Why Doesn't Stuff Work Anymore?

[HowardOfOcal]

This worked great in Access 2003 to use the file open dialog box. Now I get an error message in Access 2010

"Compile Error: User-defined type not defined"

Is there another library I have to reference?

-----------------------------------------------------------------------------------

Private Sub GetfileName(SelectedFile, usercanceled As Boolean, Section As String, tFileDescription)

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog


' Don't Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = tFileDescription
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Excell Spreadsheets", "*.*"

' 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 that is selected and then add it to the list box.
For Each varFile In .SelectedItems
SelectedFile = varFile
usercanceled = False
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
usercanceled = True
End If
End With
End Sub

[June7]

Yes,

' This requires a reference to the Microsoft Office 11.0 Object Library.

should be 14.0

Open the VBA Editor Tools > References and make sure Office 14 is the selected library.

[boblarson]

The other way is to not use references at all and use what is known as LATE BINDING. The code modified to use Late Binding would be:

Private Sub GetfileName(SelectedFile, usercanceled As Boolean, Section As String, tFileDescription)

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Object
Dim varFile As Variant
Dim Const msoFileDialogFilePicker As Integer = 3

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Don't Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = tFileDescription
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Excell Spreadsheets", "*.*"

' 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 that is selected and then add it to the list box.
For Each varFile In .SelectedItems
SelectedFile = varFile
usercanceled = False
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
usercanceled = True
End If
End With
End Sub

The only drawback is that intellisense doesn't work with late binding. So I usually use early binding (by setting the reference) until I have it the way I want and then change over to late binding for production and then when versions change there is no issue.

[HowardOfOcal]

The other way is to not use references at all and use what is known as LATE BINDING. The code modified to use Late Binding would be:
[code]
Private Sub GetfileName(SelectedFile, usercanceled As Boolean, Section As String, tFileDescription)

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Object
Dim varFile As Variant
Dim Const msoFileDialogFilePicker As Integer = 3

Well, I rummaged around in the references list under tools and found a checkbox for "Microsoft Office 14 Object Library" I checked it and my problem went away :-)

I understand the idea of early or late binding, but I'm not sure what the
"Dim Const msoFileDialogFilePicker As Integer = 3" statement does. That's for another day of research.

I hope I didn't screw anything else up by selecting the #14 library.

I guess this case is closed. Thanks for the help.

[boblarson]

No, you wouldn't have screwed anything up by selecting that reference. And the Dim Const is messed up. I errored - it should have just said

Const msoFileDialogFilePicker As Integer = 3

no DIM in front of it. So that was causing you to get an error.

And the value of msoFileDialogFilePicer is 3 so that is what we were doing. When using late binding, constants need to be defined if you are using them as Access knows nothing about them if the reference isn't set.