Import All Sheets from Excel to Access

[kestefon]

Hi Guys,



I'm have an Excel workbook (2013) with two dozen sheets that I'd like to import to Access. The code below is failing when it reaches the "Do.Cmd ..." and it's showing a type mismatch error. Any idea how to correct it?

Sub ImportAllSheets()




Dim wkb As Excel.Workbook
 Dim sht As Excel.WorkSheet
 Dim xl As Excel.Application


    Set xl = New Excel.Application
    xl.Visible = True
    Set wkb = xl.Workbooks.Open("C:\Users\docs\file.xlsm")
    With wkb
        For Each sht In .Worksheets
            DoCmd.TransferSpreadsheet sht.Name, HasFieldNames:=True
        Next
    End With


End Sub

[June7]

AFAIK, can't use TransferSpreadsheet on a VBA object.

[kestefon]

So, I modified the code a bit and was able to make it work...sort of. It's taking the first sheet and importing the same data over and over again with the name of the workbook's worksheet.

Sub ImportAllSheets()




Dim wkb As Excel.Workbook
 Dim sht As Excel.WorkSheet
 Dim xl As Excel.Application
 
 Dim strPathFile As String
 strPathFile = "C:\Users\docs\file.xlsm"


    Set xl = New Excel.Application
    xl.Visible = True
    Set wkb = xl.Workbooks.Open("C:\Users\docs\file.xlsm")
    With wkb
        For Each sht In .Worksheets
            'DoCmd.TransferSpreadsheet sht.Name, HasFieldNames:=True
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sht.Name, strPathFile, True
        Next
    End With


End Sub

[June7]

You are using sht.Name in the TableName argument. That has nothing to do with data source. Need to specify Range argument.

Per MS: A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

sht.Name & "!A1:some cell reference"

[ssanfu]

You might check out Ken Snell's site for Import and Export examples (VBA code)


Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm