Automating Importing Data from Excel (2002/SP2)

[ThreeCats]

Following up on my <post#=532639>post 532639</post#>. I'm exporting information from Simply into an Excel file, and now want to automate importing it into Access.
Posts like Han's <post#=228,920>post 228,920</post#>, explain how to use the Transfer Spreadsheet method and I'm fine with that.
But now, I want to find a way to allow the user to browse to the file (as there is no guarantee where it is or where it will be stored).

Any suggestions?

[HansV]

If all users will be on Access 2002 (XP) or higher, you can use the new Application.FileDialog object. Here is a simple example:

Sub GetFilename()
Dim strFilename As String
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel files", "*.xls"
If .Show = True Then
strFilename = .SelectedItems(1)
MsgBox "You selected " & strFilename
End If
End With
End Sub

You must set a reference to the Microsoft Office 10.0 Object Library (10.0 for Office XP, 11.0 for Office 2003).

If you also have Access 2000 users, you can't use the FileDialog object. The database attached to <post:=304,810>post 304,810</post:> contains a class module and a standard module that provide a wrapper about the Windows API calls for the File Open dialog; this will work in any version of Access (in fact, the database is in Access 97 format). The form in the database demonstrates how to use the code.

[ThreeCats]

Works like a charm!