VB Script to import Excel workbooks with multiple tabs in MS Access 2010 (1 Viewer)

I am using Excel and Access 2010. I have 7 Excel workbooks with multiple tabs with the same type of 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.

Each workbook has multiple tabs (worksheets). Each workbook represents a group in my organization and each tab presents a project.

For example:
Workbooks:
1) Group 1 - Project A1, Project A2, ...Project An
2) Group 2 - Project B1, Project B2, ....Project Bn
.
.
.
3) Group N - Project Z1, Project Z2, ...Project Zn

Here is some code will import a workbook tab to a separate access table.

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:\Users\david.lehman\Documents\DSS\Budget Justification Project\Spend Plan Baseline\test\HQ FY13 Spend 111812v2.xlsx")
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DSS Consolidated SP-5" & sh.Name, "C:\Users\david.lehman\Documents\DSS\Budget Justification Project\Spend Plan Baseline\test\HQ FY13 Spend 111812v2.xlsx", 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

I need to everything in one Access table. The 1st row in each tab are identical representing the field names.

Thank you for your help.

Dave

Maybe it is a bit tedious, but how about importing one sheet at a time to one table at a time, but then append the contents of the single table to where you really wanted it, then erasing the intermediate table and doing the next import?

I have done that with the Access wizard and want to import all tabs from each workbook. I have 8 workbooks averaging 8+ tabs. That is over 64 sheets and takes too much manual time. Writing in VB script is the way to go to automate it.

I am still looking for hints and previous code to import all workbooks with several tabs each at one time into an Access table.