Importing Excel into Access (2000)

Topic

New Reply

I’m importing an Excel file into Access using a macro and it works fine. However, Legare over in the Excel forum showed me code for Excel that would open the file browse window allowing the user to select a file. This seems – to me – to be a much better method than have the path hard-coded in a macro. I’m assuming it’s possible to open that same window in Access and allow the user to select the file to import from.

Here’s what I am doing and how can I make it better?

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 8, “tblRecruitImport”, “c:DivisionRosterImportRosterTool.xls”, True, “”
DoCmd.OpenQuery “qryAppendRecruitDataFromImportTable”, acNormal, acEdit
DoCmd.OpenQuery “qryDeleteRecruitImport”, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.OpenForm “frmDone”, acNormal, “”, “”, , acNormal

I thank you for your time and assistance.

Reply | Quote

Replies

Sadly, Access 2000 doesn’t have a built-in File Open dialog you can use in your VBA code. This was only introduced in Access 2002. But, you can use some Windows API calls to show a File Open dialog. See post 304810; you can download the zip file attached to that post, unzip the database in the zip file, and import the modules into your database.

Reply | Quote

Absolutely outstanding! This works great! Thanks, Hans.

Reply | Quote

Absolutely outstanding! This works great! Thanks, Hans.

Reply | Quote

Sadly, Access 2000 doesn’t have a built-in File Open dialog you can use in your VBA code. This was only introduced in Access 2002. But, you can use some Windows API calls to show a File Open dialog. See post 304810; you can download the zip file attached to that post, unzip the database in the zip file, and import the modules into your database.

Reply | Quote