Importing Data from One Excel Sheet into Multiple Access Tables Simultaneously

[rewalk]

Hello all,

I've set up protected Excel sheets that users can paste data into. I then need to be able to pull the data they paste into multiple access tables and then reset the Excel spreadsheet back to the template. For example, the first few columns go to the first table, the next 3 into a second table and so on. Is this possible? I can't think of a way to do it. Thanks in advance for all your help!

[BradHodge]

Here is what I came up with...

1) As could be expected, your field names and data types should be consistent between the Excel Spreadsheet and the Access Tables.
2) You will likely need to have some sort of module to be able to code a pause between trips out to the spreadsheet to get another column. I used this one by Dev Ashish .
3) Here is the code I used...

Private Sub Import_Click() 
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport1", "C:\Spreadsheet.xls", True, "A:A"
'Transfers column A from your spreadsheet to the 1st table in your database 
Call sSleep (500)
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport2", "C:\Spreadsheet.xls", True, "B:B"
 Transfers column B from your spreadsheet to the 2nd table in your database 
Call sSleep (500)
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport1", "C:\Spreadsheet.xls", True, "C:C"
 Transfers column C from your spreadsheet to the 3rd table in your database 
Call sSleep (500)
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblExcelTemplate", "C:\Spreadsheet.xls", True
 Transfers a table in your database with the spreadsheet's column names and no data, back to your spreadsheet. 
 End Sub

Hope this helps.

Brad.

[rewalk]

Brad,

Thanks for that bit of code. That definitely helped. However, it led me to another problem. The ID is set by an autonumber in the first table things are copied into. However, this ID needs to be duplicated in all the other tables. The reason for this is because users run reports that pull information together from the various tables in stages, so access needs to know what entries go with what entries in each table. I can't find a way to get access to duplicate the ID from the first table in the other tables. To follow is the specific (simplified) example:

Table 1
ID, Var 1, Var 2
Value:
1, X1, X2
2, Y1, Y2

Table 2
ID, Var 3, Var 4
Value:
1 (assigned from table 1), X3, X4
2 (assigned from table 1), Y3, Y4

Report:
X1, X2, X3, X4
Y1, Y2, Y3, Y4

Thanks again for your help and any ideas on this problem are equally appreciated.

[BradHodge]

That's a tough one. Since you want all of the tables to share the same ID number, using an AutoNumber on any of them would be illadvised because that number can reset itself, which would cause your data not match up anymore.

I was trying to think of a way to use the date in combo with something else from the record to be the unique ID. So far no luck. I'll keep thinking about, but maybe one of the experts has some thoughts on this one.

Brad.

[rewalk]

Hey Brad,

I just wanted to let you know I ended up figuring it out and wanted to thank you for your help and guidance. The way I did it was using a DoCmd to import the spreadsheet directly to a query which split the information up. Thank you again for all your help.

[BradHodge]

Glad you found a solution!

Brad.