Using the browse for file dialog in Access VBA

Question

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

You are talking about the file path. But you have multi select enabled. Do you want the folder path, where all the files lie? — froeschli, Jan 27 '11 at 07: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. — lalachka, Jan 29 '11 at 22:43
possible duplicate of [How to show "Open File" Dialog in Access 2007 VBA?](http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba) — Fionnuala, Apr 15 '12 at 09: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. — lalachka, Jul 27 '12 at 00:47

score 8 · Accepted Answer · answered Jan 27 '11 at 07:11

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", "*.*"

answered Jan 27 '11 at 07:11

Cody Gray - on strike

239,200
50
490
574

2

@Cody Gray FileDialog works fine with late binding. `Dim f As Object` doesn't require setting a reference to Microsoft Office Object Library. – HansUp Jan 27 '11 at 14:00
1

@HansUp: I'm perfectly aware that it *works* with late binding; most things do. That doesn't mean it's the recommended practice. Why do you recommend accepting the many disadvantages of late binding over early binding when you don't need any of its advantages? – Cody Gray - on strike Jan 27 '11 at 14:31
1

The advantage is that late binding doesn't require setting a reference. Are you claiming "recommended practice" is to avoid late binding? Recommended by whom? – HansUp Jan 27 '11 at 14:49
4

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. – HK1 Jan 28 '11 at 06:06
4

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. – David-W-Fenton Jan 29 '11 at 01:53
There are whole lot of bad practices recommended here. I'm not an Access dev, but I can't imagine it's much different than every other VBA environment. Caching objects in static variables for the *duration* of your application is something I never thought I'd see recommended. Altering your code to use early binding at one point, and late binding later, is the best way to *ensure* you ship bugs. Categorically recommending late binding over early binding shows no appreciation for the benefits of type safety. Unless you need truly generic objects, use early binding and save yourself the headache. – Cody Gray - on strike Jan 29 '11 at 02:10
this is why i was interested in this piece of code, because it doesn't require a reference. i got it to work in another way by using Office 12.0 reference but wanted to avoid it. having said that i don't understand what you guys are arguing about))))))))))) if someone has a minute, can you please explain what late binding (and early one at that) is and how to avoid it? thank you very much. and nice to see you again, HansUp))))))))))) – lalachka Jan 29 '11 at 22:41
David, i'd love to stay with the original 3 references but i keep finding myself in situations where i have to keep adding references. the most common one i need is Outlook and then Internet COntrols and a few others. what do you do? let's say you have to autoemail attachments or autodownload things from the net, what do you do? – lalachka Jan 29 '11 at 22:48
never mind, i know what binding means now. so then which is preferred? you all say different things)))))))))))) – lalachka Jan 29 '11 at 22:54
David, ignore my comment above and please answer this then. why are you so against early binding? i'm not distributing accde version, it's a locked regular access frontend, what's the disadvantage of using early binding in this case? – lalachka Jan 29 '11 at 22:57
1

@lalachka: Apparently it's a divisive issue. I had no idea. As I mentioned before, I'm not an Access developer, but I use VBA and VBScript all the time in other applications. I'd make a recommendation for you, but I suppose I already have. You'll have to decide on your own if you want to do late binding or early binding. The code I posted will work either way. For early binding, continue to declare `f` as `Office.FileDialog` as I've shown. For late binding, just change the declaration of `f` to `Object`, as you had originally. Everything else stays the same. No religious war required. – Cody Gray - on strike Jan 30 '11 at 07:22
got it, thank you so much. i had it working with early binding and since i thought that avoiding a reference is a good thing i posted this topic. now i'm not sure that i should try avoiding references at all cost from what i see here. will read up on it more and thank you for your time – lalachka Feb 03 '11 at 16:08
3

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. – David-W-Fenton Feb 06 '11 at 02:21
3

I'd also like to know the secret of using early binding and strong typing in VBScript. – David-W-Fenton Feb 07 '11 at 00:05
@David: Not sure what you mean by "secret". I already mentioned compile-time type checking is a major benefit to early binding. It's also faster; late binding imposes a performance penalty because the server has to be queried *each time* you access a property or method of a late-bound object. You also get Intellisense support, which many devs find useful. The only benefit of late binding is that your code still compiles, even when the components can't be found. Not sure how hidden run-time bugs are an advantage. If you need late binding very often, you're doing something wrong in deployment. – Cody Gray - on strike Feb 07 '11 at 08:13
1

But how do you use early binding and strong data types in VBScript? I'm not talking about VBA, but VBScript, which is not the same thing at all. – David-W-Fenton Feb 08 '11 at 05:02
1

The point of late binding is that you can recover from a missing component, while with early binding, your code won't compile and your whole app may fail. With late binding, a missing component will only break the part of the app dependant on that missing component. With early binding, it breaks the whole application. The former is so clearly superior I can't see why anyone would insist on the latter. – David-W-Fenton Feb 08 '11 at 05:03
@Cody Gray hey, hopefully you're still around to answer this. " If you need late binding very often, you're doing something wrong in deployment". so how are you saying deployment should be done? i run into this problem all the time when users have different versions of office on their computers. are you saying there should be code that will check the versions on load and set the appropriate references? can you describe how you deploy your apps? – lalachka Apr 18 '14 at 23:55

Using the browse for file dialog in Access VBA

1 Answers1

Linked