Unanswered: Importing access query into Excel with VBA
I have a process that Importing access query into Excel, but I have to change path everytime I run that process.
Is there are a way to have a Directory window from which I may choose directory/folder to output that query to?
Thank you for your help....
''----Set name of the Excel file with Director name
strName = lstAccExec.Column(0)
''----Remove any extra chars
Label18.Caption = "Now producing report for " & strName
strName = Replace(Replace(Replace(strName, ".", "_"), ",", ""), " ", "_")
strName = "Shipments'09_" & lstAccExec.Column(0)
Check out the FileDialog property. Here's a sample of some code I have that does exactly what you're trying to do (I think).
'Use Common Dialog to get destination file location
Dim dlgBrowse As FileDialog, _
strFilePath As String
Set dlgBrowse = Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
If Nz(strFilePath, "") <> "" Then .InitialFileName = strFilePath
If .SelectedItems.Count <> 0 Then strFilePath = .SelectedItems(1)
Set dlgBrowse = Nothing
If Nz(strFilePath, "") = "" Then GoTo ExitSub 'If hits cancel
Note that when the user hits the Cancel button on the CD, it still returns a zero length string,or a null string, can't remember which of the top of my head. Either way, that's what the last line is for.