I have been trying to figure out a solution to late binding the file dialog object for a long time. I have played with the code, googled for examples and haven’t had much luck until today when a generous visitor of ours posted the solution in the comments of our Select or Open a file in VBA with file dialog post.
The original FileDialog post referenced above shows an example of how to use the FileDialog object with the requirement of adding a reference to the Microsoft XX.X Office Object Library. When using late binding, no reference is needed. The code just works.
Our visitor’s comment of the example originated from an accepted answer on a StackOverflow thread: http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba. For the purposes of this post, I modified the StackOverflow example to better handle when users cancel out of FileDialog. This example works across all office products (Word, Excel, Access, Outlook, etc…) and Office versions 2007, 2010, 2013.
Late Binding File Dialog in VBA
'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
' NOTE: Late binding version needs no
' MS Office Object Library Reference
'--------------------------------------------------
Function selectFile()
Dim fd As Object
Set fd = Application.FileDialog(3)
With fd
If .Show Then
selectFile = .SelectedItems(1)
Else
'stop execution if nothing selected
End
End If
End With
Set fd = Nothing
End Function
For example-sake, here is the example that uses early binding:
Early Binding File Dialog Example With VBA
'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
If .Show Then
selectFile = .SelectedItems(1)
Else
'stop execution if nothing selected
End
End If
End With
Set fd = Nothing
End Function
As mentioned above, this is something that has been driving me crazy for a long time. The reason this example works is because of the enumeration of msoFileDialogFilePicker to 3. Without the MS Office XX.X Object Library reference, the language interpreter doesn’t know what msoFileDialogFilePicker means.
Please comment with any suggestions, improvements, banter, etc…
Hello, I was trying your solution, I’m interested in observing the resulting text string, so my code is:
Private Sub Command2_Click()
Dim fd As Object
Dim directorio As String
Set fd = Application.FileDialog(3)
With fd
If .Show Then
selectFile = .SelectedItems(1)
Else
‘stop execution if nothing selected
End
End If
End With
directorio = selectFile
MsgBox (selectFile)
Set fd = Nothing
End Sub
but it gives me the following error, highlighting “selectFile”: Cant Find project or library
The example used a function called selectFile where selectFile is set as the return value. I haven’t tried this out to confirm, but you could try this:
Private Sub Command2_Click() Dim fd As Object Dim directorio As String Set fd = Application.FileDialog(3) With fd If .Show Then directorio = .SelectedItems(1) Else ‘stop execution if nothing selected End End If End With MsgBox (directorio) Set fd = Nothing End SubGreat solution, i searching how to use objects without references! Thanks man! Great explanation. Works wonderful.
great. It works for me. I am using widown10 and MS Access2013. I have office object file 15.0 selected but still not working with late binding it worked. thank you so much.