Need help importing multiple excel tabs into one Access Table

[Landaverry]

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?

[MMcCarthy]

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?

[Landaverry]

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.

[MMcCarthy]

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

[NeoPa]

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.

[ADezii]

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!
   

   Dim excelapp As New Excel.Application
   Dim excelbook As New Excel.Workbook
   Dim excelsheet As New Excel.Worksheet
   Dim intNoOfSheets As Integer, intCounter As Integer
   Dim strFilePath As String
    
   strFilePath = "C:\Test\Test.xls"
    
   Set excelbook = excelapp.Workbooks.Open(strFilePath)
    
   intNoOfSheets = excelbook.Worksheets.Count
    
   For intCounter = 1 To intNoOfSheets
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, _
                             False, excelbook.Worksheets(intCounter).Name & "!A1:G50"
   Next
    
   excelbook.Close
   excelapp.Quit
   Set excelapp = Nothing

[NeoPa]

In Excel,

Selection.SpecialCells(xlLastCell) = Bottom-right cell of used area in a worksheet.
    .Row                           = Row number of the cell
    .Column                        = Column number of the cell

This code can be used to specify a perfect address range for the import.

[ADezii]

In Excel,

Selection.SpecialCells(xlLastCell) = Bottom-right cell of used area in a worksheet.
    .Row                           = Row number of the cell
    .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.

[Denburt]

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.

[NeoPa]

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 :

Private Const conMaxRow As Long = 65536
...
lngBottomRow = Range("G" & conMaxRow).End(xlUp).Row

[ADezii]

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 :

Private Const conMaxRow As Long = 65536
...
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).

Dim excelapp As New Excel.Application
Dim excelbook As New Excel.Workbook
Dim excelsheet As New Excel.Worksheet
Dim intNoOfSheets As Integer, intCounter As Integer
Dim strFilePath As String, strLastDataColumn As String
Dim strLastDataRow As String, strLastDataCell As String
 
strFilePath = "C:\Test\Test.xls"
 
Set excelbook = excelapp.Workbooks.Open(strFilePath)
 
intNoOfSheets = excelbook.Worksheets.Count
 
For intCounter = 1 To intNoOfSheets
  excelbook.Worksheets(intCounter).Activate
    'The next 3 lines will obtain the last data cell reference for each Worksheet
    strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
    strLastDataRow = Selection.SpecialCells(xlLastCell).Row
    strLastDataCell = strLastDataColumn & strLastDataRow    'e.g. J123
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, False, _
                 excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
Next
 
excelbook.Close
excelapp.Quit
Set excelapp = Nothing

[NeoPa]

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.

[Denburt]

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.

[ADezii]

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:

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.

[NeoPa]

Tha Application Object is set up in Line #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.

[Landaverry]

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.

[ADezii]

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.

[webcat]

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.

[NeoPa]

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.