import 2 sheets from excel into two diff tables in access

[dalerguy]

Hi,

I've created a xls file containing 2 sheets named auth & recur. My objective is to get sheet auth & sheet recur imported to their respective tables in the database.

This will be a daily process. The filename looks like this 04-05-2005.xls.

I'm thinking the best way is to have the user specify the file name (location will remain static) then pass the variable to the macro.

I have two questions:

1. how do I reference each sheet and import the proper data to access

2. How can I prompt the user to specify the file name before running the import.

Any help will be appreciated

Thanks,

Mike

 

[Norie]

Mike

Try this.

Change the columns and path as required.

Sub Test()
Dim sDate

    sDate = InputBox("Please enter filename:", "Enter filename", Format(Date, "mm-dd-yyyy"))
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "auth", "C:\" & sDate & ".xls", True, "auth!A1:B65536"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "recur", "C:\" & sDate & ".xls", True, "recur!A1:B65536"
    
End Sub

 

[dalerguy]

Norie,

Thanks for the code. Works great!