How do I create a file dialog box to select a file to import?

S

ScorpioRC

I'm trying to write the code to create a file dialog box to pick an Excel
file which will then be imported to a table. I've written the code for the
dialog box, but can't get it to take the selected file and import it. I keep
getting an error saying that the command needs a filename argument. Can
anyone help me? Thanks
 
S

scorpiorc

Option Compare Database
Option Explicit

Private Sub cmdFileDialog_Click()

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Clear the list box contents.
Me.FileList.RowSource = ""

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

' Set the title of the dialog box.

.Title = "Select One or More Files"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Excel Files", "*.XLS"
.Filters.Add "All Files", "*.*"

' 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
Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

DoCmd.TransferSpreadsheet acImport, 8, "Begin", varFile, True, ""

End Sub
 
J

James Hahn

varFile will not contain a valid filename when the For loop terminates.
Instead of using varFile, use one item (such as the first) from FileList.
If you really want your users to be able to select multiple files, use each
entry in FileList (ie, put the command in a loop that processes each entry
in FileList).
--
 
Top