Importing Excel multiple workbooks with multiple tabs into a single Access 2010 db

[captdkl02]

I am using Excel and Access 2010. I have 7 Excel workbooks with multiple tabs with the same data need to be imported into a single Access 2010 database. Each tab has identical field names with different number of records. I would like to import automatically by some type of VBscript or macro within Access.

Here are the field names: Object Class, Expenditure Type, Requirement, Description, Basis of Estimate (BOE), Priority (ie Must Pay, Mission Critical, Mission Imparied), Vendor, Contract Year, Period of Performance (POP), Contract Type, Contractor, FTE, If Capital Equipment -- Provide dollar amount, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Total Spend Plan, FY12 EOY Actuals, Qtr1 Plan, Qtr1 Auth, Qtr2 Plan, Qtr2 Auth, Qtr3 Plan, Qtr3 Auth, Qtr4 Plan, Qtr4 Auth, Annual Plan Total, Total Auth, Tab, Div.



Thank you.

[alansidman]

Are you looking to import all workbooks and all tabs to the same table or to multiple tables. And if multiple tables, how are they to be parsed? All tabs from the same workbook to one table? All similar tabs from different workbooks to the same table? We need some more information.

Alan

[captdkl02]

Alan,

Looking to import all workbooks and tabs to same Access db table. A workbook is from an organization within the company (i.e. CIO, IP, HQ, etc) and each tab from the excel workbook is a project (i.e. Task1, Task2, Task3, ...TaskN). The fields for each tab in the workbook are identical. The field names are Object Class, Expenditure Type, Requirement, Description, Basis of Estimate (BOE), Priority (ie Must Pay, Mission Critical, Mission Imparied), Vendor, Contract Year, Period of Performance (POP), Contract Type, Contractor, FTE, If Capital Equipment -- Provide dollar amount, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Total Spend Plan, FY12 EOY Actuals, Qtr1 Plan, Qtr1 Auth, Qtr2 Plan, Qtr2 Auth, Qtr3 Plan, Qtr3 Auth, Qtr4 Plan, Qtr4 Auth, Annual Plan Total, Total Auth, Tab, Div.

Let me know what other info you need.

Thank you.

Dave

[alansidman]

Look at this site. Scroll down to the importing and exporting section. Denis has a nice presentation here.

http://www.datawright.com.au/access_...ces/access.htm

Alan

[ssanfu]

Also check out this page by Ken Snell: http://www.accessmvp.com/kdsnell/EXCEL_Import.htm

[captdkl02]

Alan,

Good about implementing button. The code is good for a Excel file that has one sheet in a workbook to import; however, it does not address the multiple tabs in each workbook. I have seen similiar things on web. Do you know how to import multiple tabs (worksheets)?

[alansidman]

Here is code that I use, but it brings in each tab to a separate table. Additionally, it requires you to have the workbook on your desktop. So you will need to amend the code slightly for both of those situations.

Option Compare Database


Private Sub Command1_Click()
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author    : jed
' Date      : 1/16/2009
' Purpose   : To import all worksheets in a specified workbook into
'             individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
' Make sure that the file GEMCAP.xls is located on your desktop.  You may have to change the path
' if you are using a newer version of Windows.  This was designed to run on Windows XP.
'---------------------------------------------------------------------------------------
'
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String


   On Error GoTo ImportXLSheetsAsTables_Error


Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls")
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls", True, sh.Name & "!"
Next


wb.Close
appExcel.Quit


   On Error GoTo 0
   Exit Sub


ImportXLSheetsAsTables_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

Note that this is also designed for Excel and Access 2003, so you will have to make changes if that is an issue. This should get you on the right track however.

[captdkl02]

Steve,

I used Ken's code below and it does not compile with error below as indicated in bold. I have a single excel file with multiple tabs in my test folder.
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String
' Establish an EXCEL application object
' It blows up on the "On Error Resume Next" with error msg "Invalid outside procedure"
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
strPath = "C:\Users\david.lehman\Documents\DSS\Budget Justification Project\Spend Plan Baseline\test\"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "gnome"
blnReadOnly = True ' open EXCEL file in read-only mode
strFile = Dir(strPath & "*.xlsx")
intWorkbookCounter = 0
Do While strFile <> ""
intWorkbookCounter = intWorkbookCounter + 1
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
blnReadOnly, , strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount) & intWorkbookCounter, _
strPath & strFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"
Next lngCount
' Delete the collection
Set colWorksheets = Nothing
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPath & strFile
strFile = Dir()
Loop
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

[captdkl02]

Alan,

It worked to import data in seperate tables in access and crashed access, but not big problem when I brought access back up your routine imported the information from excel workbook with various tabs into seperate tables.

Is there a way to append the data in one table vs. seperate tables? Is there another variable for TransferSpreadsheet to accomplish this or what is another way? Also, I want to read in multiple Excel workbooks with the seperates tabs that will be imported into one Access table. Any further hints would be greatly appreciated!!

Thank you.

Dave

[alansidman]

This link explains the Transfer Spreadsheet syntax. You can add the table name. Take out the coding for multiple tabs. I don't have time to give you all the codes right now as I have to run out and do some errands, but if I have time later today, I will see what I can come up with. In the meantime, look at this link and see if you can amend the code.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Alan

[captdkl02]

Alan,

I will take a look at the link in the meantime. Thank you.

Dave

[alansidman]

Dave,
I played with Ken Snell's code and attached a little db for you to see how it works. You will have to change a couple of items. The Path. The file name I set up with a text box. And you will have to assign a table name. I used tblTestTable. Oh, one more thing. I did this in access 2002 so that it had greater visibility, but set it for Win 7 and Excel 2007/10

[captdkl02]

Alan,

Can you please zip the file? My employer location is not allowing to download and I able download zip files.

Added error message.

Thank you.

Dave

[alansidman]

Dave;
No problem. Don't you love those Techies trying to save you from bad files.

Here it is.

[captdkl02]

Alan,

Made modifications you mentioned and I am receiving runtime error 3274. External table is not in expected format.

here is the line of code bombing ==> DoCmd.TransferSpreadsheet acImport, 10, _
strTable, strPAF, blnHasFieldNames, colWorksheets(lngCount) & "$"

My code for strTable is ==> strTable = "DSS Consolidated SP-5"

Also, I see the first ColWorksheets item 1 is "Data List" from the debug info. Further assistance is greatly appreciated.
Any suggestions??

Thank you
Dave