I need to import data from an Excel 2007 spreadsheet into a temp table in Access 2007. I cannot always count on the Excel file being in the same location every time.
The following DoCmd.TransferSpreadsheet works perfect every time. But notice that the path to the file is hard coded.
DoCmd.TransferSpreadsheet acImport, 10,"Tbl_VendorPrices_TempImport", "C:\menuprobids\import35", True, "A1:I1000"
I have tried many different ways to navigate to the file. This one below works very well. However, when you pick the file it instantiates an instance of Excel. So when I try to import the file it fails saying that the file is already open by another user.
What I need is a way to navigate to the file location, and only return to VB the PATH, and store it as a string variable. Then I can have the TransferSpreadsheet use the variable to locate the file.
Dim Dlg As FileDialog
Dim txtFilePath As String
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.title = "Select the file you want to import"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
Any Thoughts?
Phred