• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

How to automatically import sheets from excel into access database

Dear all,

I've been manually importing data from an excel workbook into an access database using External data -> Import.  There are over 20 sheets of data is there a way to automatically set up an import procedure that will iterate through the sheets using all the import wizard default options?

Thanks
0
AndyC1000
Asked:
AndyC1000
  • 2
1 Solution
 
AndyC1000Author Commented:
Hello,

I've tried the example from the first link.  I'm having troubles with the table name argument I would like this to be the same as the excel sheet name.  The current error "action or method requires a table name argument".

Sub ImportAllSheets()
Dim objXL As Object
Dim sTable, xlPath As String, i As Integer

xlPath = "C:\Export\TestBook1.xlsx"

Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open xlPath, , True
    With objXL
        For i = 1 To .Worksheets.Count
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sheet1, xlPath, True
        Next
    End With
    objXL.Quit
    Set objXL = Nothing

End Sub

Open in new window


Thanks
0
 
Rey Obrero (Capricorn1)Commented:
test this

Sub ImportAllSheets()
Dim objXL As Object
Dim sTable, xlPath As String, i As Integer

xlPath = "C:\Export\TestBook1.xlsx"

Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open xlPath, , True
    With objXL
        For i = 1 To .Worksheets.Count
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, .worksheets(i).name, xlPath, True, .Worksheets(i).Name & "!"
        Next
    End With
    objXL.Quit
    Set objXL = Nothing

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now