Prompt a user for a file in Access

[bae14]

I have been trying to prompt a user for a file name. I'm using the code below but keep getting the message "compile error: Method or data member not found". It is stopping on the Application.FileDialog line. Any help would be greatly appreciated!!

Private Sub Command13_Click()
 
Dim myDialog As FileDialog
Dim strFile As String
Dim strSearchPath As String
Dim vrtSelectedItem As Variant
Set myDialog = Application.FileDialog(msoFolderPicker)
'Use strSearchPath  if you want to start in a particular directory. You can leave it out if you wish (as I have it commented out right now) and it will start with a default directory of Windows choosing.
'strSearchPath ="C:\SomeDirectory\SomeSubDirectory"
With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
     If .Show = -1 Then
 
    For Each vrtSelectedItem In .SelectedItems
      'I pass the file information to another routine that handles the transfer
      ImportIt (vrtSelectedItem)
    Next vrtSelectedItem
        MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
        Else
        'The user pressed Cancel.
        End If
   Set myDialog = Nothing
End With
 
End Sub

[NeoPa]

Try using (msoFileDialogFolderPicker) on your line number 7.

Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

[NeoPa]

Try using (msoFileDialogFolderPicker) on your line number 7.

Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

I got that from the help, but when I went to Debug.Print it there was nothing.
Confused, I found that the Reference to Office (Microsoft Office 11.0 Object Library or version equivalent) is required to use it. Make sure you have such a reference in your Access set up, and make sure it is also available for all computers that expect to run your project.

Good luck.

[bae14]

That helped. Thanks! BUT, now I get a "run time error" '438'. It says the object doesn't support this property or method. It is stopping on the Filters.Add line. Any ideas? Thanks a lot for your help.

[bae14]

Actually my code passed through the error and got to "ImportIt" and says the sub or function not defined. Any help on this one rather than the one I just posted? Thanks!

[NeoPa]

Actually my code passed through the error and got to "ImportIt" and says the sub or function not defined. Any help on this one rather than the one I just posted? Thanks!

As far as I can see there's no procedure ImportIt defined.
Please use the line numbers in future when referring to your code. It makes life a bit easier.

[bae14]

Thanks so much for your help! I believe I can get beyond this now. Have a good day.

[NeoPa]

No problem.
Good luck with you project :)