Allow user to choose what file to import

[Quiver]

Is there a way to have the user choose what file to import, but set everything else up in VBA?

The command button that would start the import would have something like this --

DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN",???,True

-- with the ??? indicating where you would typically put the path and name of the spreadsheet. However, the spreadsheet that will be imported will have a variable name including the date (such as "IMPORT080807.xls").

Any thoughts on how to do this?

Thanks!

[Rabbit]

You could use the filedialog class to open up a Select File Dialog where they navigate to the file they want.

[Sysdupe123]

Here is a way you can have a browser window pop up and they can select the file, regardless of the name or filepath.

Function GetFile()
'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim sPath As Variant


DoCmd.SetWarnings False
DoCmd.Hourglass True
' Set up the File Dialog.

Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Allow users to not make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your File"

' Clear out the current filters, and add your own.
.Filters.Add "All Files", "*.*"

'Set the text of the button Caption
'.strButtonCaption = "Get File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each sPath In .SelectedItems

'import into primary table
DoCmd.TransferSpreadsheet acImport, "tblName", "Equipment", sPath, True
Next sPath
Else
'Show if Canceled is selected in a message box
sPath = "No File Selected to Import."
MsgBox sPath
End If

End With
DoCmd.SetWarnings True
DoCmd.Hourglass False


End Function

If you are setting up a form, you can direct a command button to this function and the user can import the file.

[Quiver]

It won't compile. It freezes on "Dim sMyPath As FileDialog". FileDialog is not an option. Do I need to reference a specific library? I'm using Access 2000 with VB 6.5.

Thanks.

[Rabbit]

Access 2000... I don't think FileDialog came with Access 2000. It started with Office XP and up.

[Quiver]

Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Dim strDate as string
strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
 
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True

This got the job done.

Thanks again!

[jschmidt]

Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Dim strDate as string
strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
 
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True

This got the job done.

Thanks again!

Sorry about this post. It was double posted so I removed this one.

[jschmidt]

Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Dim strDate as string
strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
 
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True

This got the job done.

Thanks again!

You could use the file dialog if you set a reference to Microsoft Office 11 Object library (11 or Higher). You can do this in your vbscript editor by clicking tools/references and then find the object library from the list. But it looks like you figured something else out. Good Luck.

[Quiver]

Thanks for the idea. I actually just tried setting the reference to the Microsoft Office 11.0 Object Library (as a learning experience :) and it still can't find it when I debug.

[Krunchy]

I'm having the same kind of issue I have the file on the desktop of the users computer it's the same place that they will have their mini database.
It seems to not be able to find the file it give me the error msg I have put in everytime. If I need the whole path the login of the user has to be entered by them. Thats the reason for the inputbox section.

Function Importfile()
 
Dim login As String
On Error GoTo Err_Importfile
'login = InputBox("What is your LAN ID?")
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tracker", "trackerimport.xls", True
 
Exit_Importfile:
    Exit Function
 
Err_Importfile:
    MsgBox "Tracker File is not found. Please save sales tracker export to desktop and as trackerimport.xls and try importing again"
    Resume Exit_Importfile
End Function