Defaulting a folder for FileDialog in VBA

Question

Private Sub Command93_Click()      Dim f As Object     Dim strFile As String     Dim strFolder As String     Dim varItem As Variant     Dim P As String     Dim DeleteEverything As String          DoCmd.SetWarnings False         DeleteEverything = "DELETE * FROM [TABLE]"         DoCmd.RunSQL DeleteEverything      Set f = Application.FileDialog(3)     f.AllowMultiSelect = False     If f.Show Then         For Each varItem In f.SelectedItems             strFile = Dir(varItem)             strFolder = Left(varItem, Len(varItem) - Len(strFile))             P = strFolder & strFile         Next     End If     Set f = Nothing          DoCmd.TransferText acImportFixed, "[IMPORT SPECIFICATION]", "[TABLE]", P, False  End Sub 

How to make the FileDialog default to a specific folder when it initially opens?

Answer 1

Add the folder path (including the trailing \) to InitialFileName. For example, to open the dialog in the user's home directory do:

f.InitialFileName = Environ("USERPROFILE") & "\" 

If you forget the trailing \, then the dialog box will still open in the correct folder, but the folder name will also appear as the default selected file name. Then the dialog will be looking for a sub-folder with the same name, which usually doesn't exist.