Import Code Error

[Leesha]

Hi,
I'm using the code below to import info into an Access table from an excel spreadsheet. There are two tabs in the table. The one that contains the info is called "Import" and the other tab is called "Downloaded info". I've used this code before without any issue and am not sure what is wrong. I've even tried providing the table name but I still get an error that says that the Object Import can't be found. I've tried deleting the "Downloaded Info" tab but that didn't help. I've copied and pasted the path etc. to be sure there are no typos. What am I missing?

Thanks,
Leesha

Code: Select all

    Dim strFolder As String
    Dim strFile As String
    ' Fixed folder path
    strFolder = "C:\Walmart\Import-CDS\AuntieAnnes\"
    ' Find filename
    'strFile = Dir(strFolder & "*.xlsx")
    strFile = "Auntie Annes Import.xlsx"
    ' Get out if there is no .xlsx workbook
    Do While strFile <> ""
        DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblAuntieAnnesImportTemp", _
            FileName:=strFolder & strFile, _
            HasFieldNames:=True, _
            Range:="Import"
        strFile = Dir
    Loop

[HansV]

Place a ! after the sheet name:

Code: Select all

        DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblAuntieAnnesImportTemp", _
            FileName:=strFolder & strFile, _
            HasFieldNames:=True, _
            Range:="Import!"

[Leesha]

Thanks!!!