Quick way to import multiple tabs from Excel into Access

[tommymsalazar]

Is there an easy way to import many tabs of data in a spreadsheet with multiple columns of data into MS Access? I can import them manually but there are so many tabs and importing each one will take forever.

Help appreciated!!!

 

[alansidman]

Here is code that someone gave to me several years ago that has worked for me tirelessly

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

Depending on which operating system you may have to tweak a bit. Also, depending on where your files are located, you may have to tweak that also, but you should be able to get the gist of the whole thing.

Alan

 

[HiTechCoach]

This may help: Import Data from Microsoft Excel