How to use filedialogbox to save filepath in string, using Access VBA?

Question

I have an Access file which I will be using for quality assurance of data.

I will be inputting data from three Excel files, each into its own Access table.

At present, I have three buttons and corresponding text boxes. I manually enter the file path and name into the text box, click the button and it completes the rest of my macro, importing the data.

I'd like to use the file picker dialog box to populate the textbox with the path.

It sounds like you want [Application.FileDialog](http://stackoverflow.com/a/9477733/77335) Since you want the full path, retrieve `.SelectedItems(1)` instead of `Dir(.SelectedItems(1))` — HansUp, Feb 17 '16 at 16:41
You should have really Googled this first. I don't have to look at this to know that it is a duplicate on many, many levels. — Anthony Griggs, Feb 17 '16 at 19:41

score 1 · Answer 1 · edited Jul 18 '19 at 19:13

This code and worked for me:

Private Sub Comando32_Click()
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    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))
            MsgBox "Folder: " & strFolder & vbCrLf & _
                "File: " & strFile
            Me.certidao.Value = varItem
        Next
    End If
    Set f = Nothing
End Sub

score 0 · Answer 2 · answered Feb 17 '16 at 16:50

Of course, it is possible to call the file Dialog API in VBA!

An example direct from Microsoft VBA documentation:

Private Sub cmdFileDialog_Click() 

   ' Requires reference to Microsoft Office XY.0 Object Library. 

   Dim fDialog As Office.FileDialog 
   Dim varFile As Variant 

   ' Clear listbox contents. 
   Me.FileList.RowSource = "" 

   ' Set up the File Dialog. 
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 

   With fDialog 

      ' Allow user to make multiple selections in dialog box 
      .AllowMultiSelect = True 

      ' Set the title of the dialog box. 
      .Title = "Please select one or more files" 

      ' Clear out the current filters, and add our own. 
      .Filters.Clear 
      .Filters.Add "Access Databases", "*.MDB" 
      .Filters.Add "Access Projects", "*.ADP" 
      .Filters.Add "All Files", "*.*" 

      ' Show the dialog box. If the .Show method returns True, the 
      ' user picked at least one file. If the .Show method returns 
      ' False, the user clicked Cancel. 
      If .Show = True Then 

         'Loop through each file selected and add it to our list box. 
         For Each varFile In .SelectedItems 
            Me.FileList.AddItem varFile 
         Next 

      Else 
         MsgBox "You clicked Cancel in the file dialog box." 
      End If 
   End With 
End Sub

Please note you have to include a reference to Microsoft Office 11.0 Library (in code window, select menu option Tools, Reference and select your library for the correct version of your Office Version)

Hi Cisco, just noticed what you said underneath that code. I've checked and I have 'Microsoft Office 14.0 Object Library' selected. I have the option to move it up in priority as it is currently 4th. Would that make any difference? — Adam Blunt, Feb 18 '16 at 09:57
I have enabled 'Microsoft Excel 14.0 Object library' and 'Microsoft 15 Object Library'. The error now gets past the previous error but now gives 'Method or data member not found' with `Me.txtFileSelect.RowSource = ""`. I've double checked the names of the text box and cmd button and they are as shown in the code. — Adam Blunt, Feb 18 '16 at 10:06

score 0 · Answer 3 · answered Feb 18 '16 at 09:48

Thanks for the response.

I did google it first and tried everything I came across. I also came across the very set of code pasted above. I Played around with it for a while and whatever I did returned errors. I decided to try the code in Excel instead of Access and it worked straight away. The only thing I could think was that the code wasn't applicable to access. Following all of that I asked the question here.

Private Sub cmdDialog_Click()

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   Me.txtFileSelect.RowSource = ""

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      .AllowMultiSelect = False
      .Title = "Please select one or more files"

      .Filters.Clear
      .Filters.Add "Excel Files", "*.XLSX"
      .Filters.Add "All Files", "*.*"

      If .Show = True Then

         For Each varFile In .SelectedItems
            Me.txtFileSelect.AddItem varFile
         Next

      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Sub

With this code I get: Compile error; User-defined type not identified

If the compile error highlights `Office.FileDialog`, you can switch to late binding as described in this answer: ['FileDialog' type is not defined in MS Access](http://stackoverflow.com/a/35556719/77335) — HansUp, Feb 22 '16 at 23:04

Cisco · Answer 4 · 2016-02-20T09:20:41.903

Try this code, for single file:

MyFileURL = aBrowseForFile("C:\users\") 


Public Function aBrowseForFile(aStartFolder As String) As String

' Needs a reference to Microsoft Office Object Library 15.0

On Error GoTo Err_txtBrowseForFile

Dim fDialog As Office.FileDialog
Dim varfile As Variant
Dim strPath As String
Dim strFilter As String, strFileName As String
Dim Main_Dir As String, DefFolder As String


Set fDialog = Application.FileDialog(msoFileDialogFilePicker)


With fDialog
    .InitialView = msoFileDialogViewThumbnail
    .AllowMultiSelect = False
    .Title = "Please select one or more files"
    .InitialFileName = aStartFolder
    .InitialView = msoFileDialogViewThumbnail
    .Filters.Clear
    .Filters.Add "all files", "*.*"


     ' Show the dialog box. If the .Show method returns True, the
     ' user picked at least one file. If the .Show method returns
     ' False, the user clicked Cancel.

    If .Show = True Then
         aBrowseForFile = .SelectedItems(1)
    Else
        'MsgBox "You clicked Cancel in the file dialog box."
    End If
  End With

Exit_txtBrowseForFile:
       Exit Function

Err_txtBrowseForFile:
       MsgBox Err.Description, vbCritical, "MyApp"
       Resume Exit_txtBrowseForFile

    End Function

Put this function in a module, as it is. Do not put some other code inside, so you can call it in other projects and build your own tools set.

Call it as shown above in your form.

This code runs well and it is tested.

If you want to check this code, in the debug window type

debug.print aBrowseForFile("C:\users\")

and see what happens. If you have other run-time or compile errors, please post another question.

Hope this helps

score 0 · Answer 5 · answered Feb 26 '16 at 14:02

Thanks for the response.

I solved the problem in the end, I hadn't selected the object database. I found the following code to work:

Private Sub cmdInput_Click()

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      .AllowMultiSelect = False
      .Title = "Please select a file"

      .Filters.Clear
      .Filters.Add "Excel Files", "*.XLSX"
      .Filters.Add "All Files", "*.*"

If .Show = True Then

For Each varFile In .SelectedItems

    DoCmd.TransferSpreadsheet acImport, 10, "InputData", varFile, True, ""
    Beep

    MsgBox "Import Complete!", vbExclamation, ""

Next
Else
         MsgBox "You clicked Cancel in the file dialog box."
End If

   End With

End Sub

How to use filedialogbox to save filepath in string, using Access VBA?

5 Answers5