FileDialog Not Working in Access 2010

[dferreira]

I am trying to allow the user the capability to browse to a file to import that file into access. I found code for it, but it still does not work. I can't find the reference that is recommended. Help????

 

[shadow9449]

Are you using the 64 bit edition? Some API calls don't work in the 64 bit edition unless you use the appropriate call.

SHADOW

 

[dferreira]

hiw do I know if it is 64 bit?

 

[penguino29]

Hi dferreira,

In the past, we used Windows API to callup the common dialog box in order to obtain a filepath. This has become problematic with 32 and 64 bit versions of windows.

Its much easier nowadays to use the Microsoft Office FileDialog because its independent of the OS. (I've tried it on Win7x64 and x86)

The reference is here:

http://msdn.microsoft.com/en-us/library/aa190816(v=office.10).aspx

(you will need to copy the code from the article and stick it in a module)

And make sure you add MS Office Library reference. To do this: in the VBA project window, on the menu, click TOOLS, REFERENCES, then scroll down the list and find "Microsoft Office 14.0 Object Library" and check the tickbox.

(You may have difference version number instead of 14.0, depending on which version of office you have installed)

If you need some sample codes, just come back here and ask.

 

[dferreira]

Thank You, Thank You!!!!!! I have spent so much time tring to make this work! If you were here I would kiss you!

 

[penguino29]

You are welcome - we are all here to help!

I was tearing my hair out using comdlg32.dll before, using the MS Office File Dialog is much easier.

Come back here if you have any problems.

 

[dferreira]

Can someone tell me how to import the file using the File Dialog now that I have that code working.

 

[ulieq]

I would love detailed instructions in how to make this work. Copy and paste to module means nothing to me. Thanks.

 

[penguino29]

Hi ulieq and dferreira,

Thanks for bumping the thread. Apology for my absence.

I've done some revision recently, and there is actually an elegant way to do it. The following is known with work in in Access 2007 and 2010.

Put the following code under an onClick event of a button.

Dim f   As Object
Set f = Application.FileDialog(3)
 
With f
    ' Allow Mulitple Selections
    .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", "*.*"
End With  '--- f

' Call .Show and 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

Full credits and details are from the original articles here:-

http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba

http://stackoverflow.com/questions/4813598/using-the-browse-for-file-dialog-in-access-vba

 

[shadow9449]

I did a search for the FileDialog and I found this Microsoft page:

http://support.microsoft.com/kb/279508

According to the Summary, the FileDialog does not work with the Access Runtime. Does anyone know if this is true for later versions of Access? If so, it's a SERIOUS limitation.

Thanks

SHADOW

 

[penguino29]

Hi shadow9449,

Thanks for looking up and pointing out the possible issue. According to the links in my post, FileDialog works for 2007 runtime - although I cannot vouch for it (be it 2007 or 2010) as I haven't tried it myself.

So in most cases the code should work. Your mileage may vary.

 

[ulieq]

i still cannot get this to work. I use 32bit office 2010 and windows xp. I put the code under the onclick event of a button. Does the name of the button have to be changed to something?

 

[shadow9449]

penguino29 :

I didn't see any mention either way of whether it works in runtime versions in those articles.

I wonder if anyone has any experience either way?

SHADOW

 

[ulieq]

Actually, I got an error "Invalid Outside procedure"

 

[penguino29]

Hi ulieq,

You need to put the code inside, in between the "Private Sub" and "End Sub" of a button's code. It should work. Please try again and report back.


Shadow9449:

from http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba

My comments on Renaud Bompuis's answer messed up.

Actually, you can use late binding, and the reference to the 11.0 object library is not required.

The following code will work without any references:

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

MsgBox "file choosen = " & f.SelectedItems.Count
Note that the above works well in the runtime also.

 

[shadow9449]

Shadow9449:

from http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba

Very good! Thank you so much!

SHADOW

 

[ulieq]

Thanks. I got that to work. Now how do I get that file path to be inserted into the text box?

 

[penguino29]

Hi ulieq,

As you can see from the sample code, the variable

f.SelectedItems(i)

contains the file path. You simply assign that to a text box.

me!myTextbox1 = f.SelectedItems(i)

 

[ulieq]

Nice. Next: How to use relative file paths FROM the datbase backend? ^_^ thanks!

 

[ulieq]

This method only enters data into an unbound textbox. How do I save that textbox to the correct field?