By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

Importing Data from One Excel Sheet into Multiple Access Tables Simultaneously

P: 17
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
Share this Question
Share on Google+
5 Replies


BradHodge
Expert 100+
P: 166
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.

Brad.
Jun 21 '07 #2

P: 17
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.
Jun 22 '07 #3

BradHodge
Expert 100+
P: 166
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.
Jun 22 '07 #4

P: 17
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

BradHodge
Expert 100+
P: 166
Glad you found a solution!

Brad.
Jun 25 '07 #6

Post your reply

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