Import 1 spreadsheet into 2 tables in access 1

[btvs6]

Hello,

I am building an Access Database that will be supplied with data from other departments within my organization in an Excel Format. I have normalized my tables in Access and instead of asking them to provide spreadsheets to match my structure, each department is just sending one Excel file with all the fields/data I need.

Thus, how do I get column A and column C from the Excel sheet into Table1 of the Access database and column B and column D from the Excel sheet into Table2 of the Access database?

Thank you

 

[PHV]

Import the spreadsheet into a temporary table and then use 2 append queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bubba100]

DoCmd.TransferSpreadsheet acImport, 8, "Table 1", "C:\File Directory.xls", True, "Sheet1!a1:e400"

DoCmd.TransferSpreadsheet acImport, 8, "Table 2", "c:\file directory.xls", True, "Sheet1!F1:J400"