471,049 Members | 1,262 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Importing Data from One Excel Sheet into Multiple Access Tables Simultaneously

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!
Jun 21 '07 #1
5 6133
166 Expert 100+
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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Import_Click() 
  2. DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport1", "C:\Spreadsheet.xls", True, "A:A"
  3. 'Transfers column A from your spreadsheet to the 1st table in your database 
  4. Call sSleep (500)
  5. DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport2", "C:\Spreadsheet.xls", True, "B:B"
  6. Transfers column B from your spreadsheet to the 2nd table in your database 
  7. Call sSleep (500)
  8. DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblImport1", "C:\Spreadsheet.xls", True, "C:C"
  9. Transfers column C from your spreadsheet to the 3rd table in your database 
  10. Call sSleep (500)
  11. DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "tblExcelTemplate", "C:\Spreadsheet.xls", True
  12. Transfers a table in your database with the spreadsheet's column names and no data, back to your spreadsheet. 
  13. End Sub
Hope this helps.

Jun 21 '07 #2

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
1, X1, X2
2, Y1, Y2

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

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

Thanks again for your help and any ideas on this problem are equally appreciated.
Jun 22 '07 #3
166 Expert 100+
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.

Jun 22 '07 #4
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.
Jun 25 '07 #5
166 Expert 100+
Glad you found a solution!

Jun 25 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Darren | last post: by
9 posts views Thread by jillandgordon | last post: by
3 posts views Thread by Conrad F | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.