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

Need help importing multiple excel tabs into one Access Table

P: 4
I have an excel file that has over 20 tabs. I need to import all tabs into one table and am having trouble finding code to do this. I would liike the user to be able to press on button, navigate to the excel file and have access import all tabs. Can anyone help or run across a similiar problem?
May 22 '07 #1
Share this Question
Share on Google+
18 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
I have an excel file that has over 20 tabs. I need to import all tabs into one table and am having trouble finding code to do this. I would liike the user to be able to press on button, navigate to the excel file and have access import all tabs. Can anyone help or run across a similiar problem?
When you say multiple tabs do you mean multiple sheets. What are the sheet names?
May 25 '07 #2

P: 4
When you say multiple tabs do you mean multiple sheets. What are the sheet names?
The sheet names vary. The user is using dates. Sheet names are usually 05-01; 05-02; 05-03; 05-04; 05-07, etc.

Each sheet is added on a daily basis and I need to be able to automate importing all sheets or have the user pick the sheet they want to import.
May 29 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
What you are talking about here is very complicated Automation code. Unfortunately, I don't have the time to guide you through it. Have a look at this tutorial and I'll see if I can get someone to help you out.

Application Automation

Mary
May 29 '07 #4

NeoPa
Expert Mod 15k+
P: 31,770
I can't find a way to import worksheets (tabs) from Excel workbooks (spreadsheet files) by name via the standard functions.
You'll probably need to use the Application Automation mentioned then.
A For Each loop of the Forms collection will give you a list of worksheets to list and choose from. From there, any individual worksheet can be saved (as a single worksheet by copying the worksheet first to a new workbook) to a new temp file. This temp file can then be imported into Access. There may be a better way than this convoluted one, but I can't find it quickly :(

Alternatively, you could experiment with how DoCmd.TransferSpreadsheet handles multiple worksheets. There may be scope there.
May 29 '07 #5

ADezii
Expert 5K+
P: 8,750
I have an excel file that has over 20 tabs. I need to import all tabs into one table and am having trouble finding code to do this. I would like the user to be able to press on button, navigate to the excel file and have access import all tabs. Can anyone help or run across a similiar problem?
I started working on your problem, but was really pressed for time. What I came up with was a simple, and viable solution which will import all the Worksheets in a given Excel Workbook (*.xls), into an Access Table named tblExcelImport. The bad news is that the code is sometimes quirky, and I really did not have time to explore the topic more thoroughly. I strongly advise, that before you use this code, to backup both the Excel Spreadsheet and the Access Database. Get back to me on your results and in the meantime, I'll try to improve on the code. Before you start, there are a few things I should mention:
  1. The Path to the Excel File is contained in the Variable strFilePath. Replace the Test Path and Spreadsheet with your own.
  2. Replace the experimental Range (A1:G50) in the TransferSpreadsheet line with a range that will encompass on your data on all the Spreadsheets. Replace the Range only and nothing else!
  3. If you wish, replace the Table Name (tblExcelImport) that accepts the Imported Data with your own.
  4. In the TransferSpreadsheet line, replace acSpreadsheetTypeExcel9 with your specific Version of Excel if necessary. acSpreadsheetTypeExcel9 refers to Excel 2000.
  5. Hang on and good luck!
    Expand|Select|Wrap|Line Numbers
    1. Dim excelapp As New Excel.Application
    2. Dim excelbook As New Excel.Workbook
    3. Dim excelsheet As New Excel.Worksheet
    4. Dim intNoOfSheets As Integer, intCounter As Integer
    5. Dim strFilePath As String
    6.  
    7. strFilePath = "C:\Test\Test.xls"
    8.  
    9. Set excelbook = excelapp.Workbooks.Open(strFilePath)
    10.  
    11. intNoOfSheets = excelbook.Worksheets.Count
    12.  
    13. For intCounter = 1 To intNoOfSheets
    14.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, _
    15.                           False, excelbook.Worksheets(intCounter).Name & "!A1:G50"
    16. Next
    17.  
    18. excelbook.Close
    19. excelapp.Quit
    20. Set excelapp = Nothing
May 30 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
In Excel,
Expand|Select|Wrap|Line Numbers
  1. Selection.SpecialCells(xlLastCell) = Bottom-right cell of used area in a worksheet.
  2.     .Row                           = Row number of the cell
  3.     .Column                        = Column number of the cell
This code can be used to specify a perfect address range for the import.
May 30 '07 #7

ADezii
Expert 5K+
P: 8,750
In Excel,
Expand|Select|Wrap|Line Numbers
  1. Selection.SpecialCells(xlLastCell) = Bottom-right cell of used area in a worksheet.
  2.     .Row                           = Row number of the cell
  3.     .Column                        = Column number of the cell
This code can be used to specify a perfect address range for the import.
Very interesting, NeoPa! I'll implement it within my code block and see how it works. Get back to you soon.
May 30 '07 #8

Denburt
Expert 100+
P: 1,356
Subscribing, looks like this one is handled any questions let us know how it turns out for you. Oh and BTW don't forget that in the Access VBA window you will need to set a reference to Microsoft Excel.
May 30 '07 #9

NeoPa
Expert Mod 15k+
P: 31,770
Very interesting, NeoPa! I'll implement it within my code block and see how it works. Get back to you soon.
Unfortunately, this includes any deleted Rows and/or Columns until after a .Save. To get the bottom used Row, I normally pick a Column that I know always has data in it and :
Expand|Select|Wrap|Line Numbers
  1. Private Const conMaxRow As Long = 65536
  2. ...
  3. lngBottomRow = Range("G" & conMaxRow).End(xlUp).Row
May 30 '07 #10

ADezii
Expert 5K+
P: 8,750
Unfortunately, this includes any deleted Rows and/or Columns until after a .Save. To get the bottom used Row, I normally pick a Column that I know always has data in it and :
Expand|Select|Wrap|Line Numbers
  1. Private Const conMaxRow As Long = 65536
  2. ...
  3. lngBottomRow = Range("G" & conMaxRow).End(xlUp).Row
I merged your code into mine (hope you don't mind) and I almost have this thing perfected. It is still a little fidgety (technical programming term) but it seems to work really well except for a couple of items which I don't know if I'll ever get ironed out. That's the way it goes with this Automation stuff! Thanks for your insight in this matter, and if you see anything that you think can be improved upon in the below listed code, please feel free to comment. I know you will anyway (LOL).
Expand|Select|Wrap|Line Numbers
  1. Dim excelapp As New Excel.Application
  2. Dim excelbook As New Excel.Workbook
  3. Dim excelsheet As New Excel.Worksheet
  4. Dim intNoOfSheets As Integer, intCounter As Integer
  5. Dim strFilePath As String, strLastDataColumn As String
  6. Dim strLastDataRow As String, strLastDataCell As String
  7.  
  8. strFilePath = "C:\Test\Test.xls"
  9.  
  10. Set excelbook = excelapp.Workbooks.Open(strFilePath)
  11.  
  12. intNoOfSheets = excelbook.Worksheets.Count
  13.  
  14. For intCounter = 1 To intNoOfSheets
  15.   excelbook.Worksheets(intCounter).Activate
  16.     'The next 3 lines will obtain the last data cell reference for each Worksheet
  17.     strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
  18.     strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  19.     strLastDataCell = strLastDataColumn & strLastDataRow    'e.g. J123
  20.       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, False, _
  21.                  excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
  22. Next
  23.  
  24. excelbook.Close
  25. excelapp.Quit
  26. Set excelapp = Nothing
May 31 '07 #11

NeoPa
Expert Mod 15k+
P: 31,770
Of course you can use the code :)
As far as picking at yours though, there is an update in the Application Automation thread which gives example usage. Your code is missing the setting up of the application object at the top.
May 31 '07 #12

Denburt
Expert 100+
P: 1,356
I almost have this thing perfected. It is still a little fidgety (technical programming term) but it seems to work really well except for a couple of items which I don't know if I'll ever get ironed out. That's the way it goes with this Automation stuff! Thanks for your insight in this matter, and if you see anything that you think can be improved upon in the below listed code, please feel free to comment. I know you will anyway (LOL).
ROFL yep always the invariable circumstances such as when someone changed the formatting for one reason or another.
May 31 '07 #13

ADezii
Expert 5K+
P: 8,750
Of course you can use the code :)
As far as picking at yours though, there is an update in the Application Automation thread which gives example usage. Your code is missing the setting up of the application object at the top.
Tha Application Object is set up in Line #1:
Expand|Select|Wrap|Line Numbers
  1. Dim excelapp As New Excel.Application
You can both Declare and Instantiate an Object Variable on the same line although this syntax is not recommended. I did it simply for display purposes, since there are several Object Variables to contend with, and the OP didn't seem to be that familiar with coding. Thanks for the help - I'll also check the Automation link.
May 31 '07 #14

NeoPa
Expert Mod 15k+
P: 31,770
Tha Application Object is set up in Line #1:
Expand|Select|Wrap|Line Numbers
  1. Dim excelapp As New Excel.Application
You can both Declare and Instantiate an Object Variable on the same line although this syntax is not recommended. I did it simply for display purposes, since there are several Object Variables to contend with, and the OP didn't seem to be that familiar with coding. Thanks for the help - I'll also check the Automation link.
You're right ADezii.
I thought, as there is a function to set it to, that it needed more than a simple 'instantiation' (needing to be set to the object returned from the function). It probably doesn't though, from what you're saying.
May 31 '07 #15

P: 4
I appreciate all the help. I was able to apply the code and it works great in my database. This has saved me a lot of time! thanks so much for all your help.
Jun 6 '07 #16

ADezii
Expert 5K+
P: 8,750
I appreciate all the help. I was able to apply the code and it works great in my database. This has saved me a lot of time! thanks so much for all your help.
Glad it all worked out for you. It was a tougher nut to crack than I initially imagined.
Jun 6 '07 #17

P: 13
hey guys, this is exactly what I am trying to do!!

i kinda figured out what i needed to do but i had no idea how to implement it.


but - when i run this code, i get the error i was getting when i tried to import other data from the sheet -

Field F1 doesn't exist in the destination table

- this is because the property HasFieldNames is set to false.

i guess that the sheet name is stored in a particular cell?

thanks.
Jun 13 '07 #18

NeoPa
Expert Mod 15k+
P: 31,770
WebCat, your explanation is not very clear, and also you are posting an extra question in someone else's thread. This is not allowed (I'm sure you didn't know).
I suggest that you post your own new thread and you can post a link to it in here if you'd like and think that may help.
BTW The sheet name is not generally stored in a cell but in the [Name] property of the sheet.
Jun 13 '07 #19

Post your reply

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