5

i saw this topic How to show "Open File" Dialog in Access 2007 VBA? and i like the solution there that's not using references, however, i can't figure out how to display the file path that the user selected. can someone please explain

thank you very much

this is the piece i'm talking about

Dim f As Object   
Set f = Application.FileDialog(3)   
f.AllowMultiSelect = True   
f.Show    
MsgBox "file choosen = " & f.SelectedItems.Count
Improve this question
4
  • You are talking about the file path. But you have multi select enabled. Do you want the folder path, where all the files lie? Commented Jan 27, 2011 at 7:11
  • i'm disabling it in my version, this is someone else's code, in my version it will be one file at a time because then things have to be done to the file. Commented Jan 29, 2011 at 22:43
  • possible duplicate of How to show "Open File" Dialog in Access 2007 VBA? Commented Apr 15, 2012 at 9:23
  • it's not a duplicate, my question was based on that topic (as it says in the original post). if i should've posted my question in that topic - then i apologize and i will do that next time instead of starting a new one. Commented Jul 27, 2012 at 0:47

1 Answer 1

Reset to default
8

First things first: you should always prefer to use strongly-typed variables whenever possible. In this case, you can replace Object with Office.FileDialog.

To display the paths of each file that was selected, you need to loop through the SelectedItems collection. For example, you would add the following code:

Dim f As Office.FileDialog
Set f = Application.FileDialog(3)   
f.AllowMultiSelect = True

' Show the dialog. If the method returns True, the user picked at least one file.
' If the method returns False, the user clicked Cancel.
If f.Show Then
    MsgBox f.SelectedItems.Count & " file(s) were chosen."

    ' Display the full path to each file that was selected
    Dim i As Integer
    For i = 1 To f.SelectedItems.Count
        MsgBox f.SelectedItems(i)
    Next i
End If

Note that the FileDialog also has other properties that you can set, if you require customization. For example, the .Title property allows you to specify a title that will appear as the dialog's caption in the title bar. You can also specify a filter using the .Filter property, which will limit the type of files that the user will be able to see and choose from in the dialog. For example, if you wanted to limit the choices to only Access Databases, you could add the following code:

' Clear out the current filters
f.Filters.Clear

' Add a few custom filters
f.Filters.Add "Access Databases", "*.mdb"
f.Filters.Add "All Files", "*.*"
Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

@Cody Gray FileDialog works fine with late binding. Dim f As Object doesn't require setting a reference to Microsoft Office Object Library.
I personally recommend using early binding to write and test the code but I think it's better to deploy with late binding. Just my 2 cents.
I categorically reject early binding for anything other than the default 3 Access references, Access, VBA and DAO. Anything else is asking for trouble in deployment (especially if you're distributing an MDE/ACCDE front end). Late binding is slower if you re-initialize every time you use the outside component, but that can be avoided by caching the top-level object in a STATIC variable (so it's initialized the first time you use it and remains available through the rest of the session). So, I strenuously disagree with the recommendation of early binding.
I am an Access developer and have seen all the problems that come with distributing an app with early binding for anything other than the basic 3 references. Late binding is just much more reliable for distribution. Any experienced Access developer will tell you this -- it's the way you insure that your app doesn't break when installed on a multitude of different machines (with different versions of Office, for instance). Anyone who recommends early binding is likely not an Access developer.
I'd also like to know the secret of using early binding and strong typing in VBScript.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.