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?

Check out the code provided here: http://stackoverflow.com/questions/9399651/picking-a-folder-using-application-filedialog — Peter Albert, Jun 04 '13 at 12:03

score 66 · Accepted Answer · edited Oct 07 '15 at 19:09

66

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.

edited Oct 07 '15 at 19:09

Leo

2,775
27
29

answered Jun 04 '13 at 12:43

Chuck

1,156
2
10
19

4

I think `Application.PathSeparator` instead of `"\"` will be better – john c. j. Oct 06 '17 at 10:05
1

@johnc.j. Why would you say that? "\" is the path seperator – Davesexcel Jan 30 '19 at 14:57
6

@Davesexcel It seems you use Windows. On macOS and Linux it will be "/". `Application.PathSeparator` will work anywhere. – john c. j. Jan 30 '19 at 17:24

score 0 · Answer 2 · answered Feb 12 '20 at 12:21

0

I tried ChDir "your\path\here" and it works

answered Feb 12 '20 at 12:21

Costas Petrides

1
1

2

please visit and check [how to answer a question](https://stackoverflow.com/help/how-to-answer). – Yunus Temurlenk Feb 12 '20 at 12:44

score -1 · Answer 3 · edited Jun 17 '16 at 15:50

-1

Or, before opening the dialog, simply change the deault file directory with:

Application.Options.DefaultFilePath(wdDocumentsPath) = "your\path\here"

edited Jun 17 '16 at 15:50

SierraOscar

17,507
6
40
68

answered Jun 17 '16 at 15:47

KathyM

1

1

This code is only good for use with MS Word, the OP is asking about MS Access – SierraOscar Jun 17 '16 at 15:49

Defaulting a folder for FileDialog in VBA

3 Answers3

Linked

Related