473,287 Members | 1,564 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Need help importing multiple excel tabs into one Access Table

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
18 7548
MMcCarthy
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
32,554 Expert Mod 16PB
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
8,834 Expert 8TB
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
32,554 Expert Mod 16PB
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
8,834 Expert 8TB
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
1,356 Expert 1GB
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
32,554 Expert Mod 16PB
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
8,834 Expert 8TB
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
32,554 Expert Mod 16PB
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
1,356 Expert 1GB
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
8,834 Expert 8TB
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
32,554 Expert Mod 16PB
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
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
8,834 Expert 8TB
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
webcat
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
32,554 Expert Mod 16PB
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

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

Similar topics

9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
1
by: socasteel21 via AccessMonster.com | last post by:
I have a spreadsheet that has 3 tabs each of the worksheets is setup exactly like a cooresponding table in Access. I created a button that should import each tab to a new table and then append...
1
by: winzy | last post by:
I have a table in Access which needs updating from an Excel file. Instead of importing the whole Excel like a new table in Acess, how do I do a partial import/update?? For example, Access table...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
3
by: mukeshsrivastav | last post by:
dear sir i want to move form excel to access. i have 5 excel file having same formats and fields.now i want to import all data in one access table. importing one file is easy .but importing and...
28
by: kkadakia | last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to...
1
by: thadson | last post by:
Hi, I'm trying to import specific cells from MS Excel 2000 spreadsheets to MS Access 2000 tables then move the spreadsheets to a different directory. I'm very new to this and I'm having trouble...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.