Stuck on import multiple excel file loop

[Hosacans]

Hi All,

I've been stuck on this for the whole morning.

Public Function XLfilelookup()

    strFolder = "C:\EXCHANGE\XLfiles\"
    
    With Application.FileSearch
    .NewSearch
    .LookIn = strFolder
    .FileName = "*.xls"
    .Execute
    
    For i = 1 To .FoundFiles.Count
            strFile = .FoundFiles(i)
            table = "Table" & i
            DoCmd.TransferSpreadsheet acImport, 8, table, strFile, True, "a11:u20000"
                
        Debug.Print strFile
        Debug.Print "imported " & table
    Next i
    
    End With
    
End Function

Somehow this generate run-time error '3274'
"External table is not in the expected format"

can this error have something to do with the range that i've selected to import...

i've checked all the excel files that i want to import. and it seem fine for those range. some files does not have 20000 rows, but it certain have the headers from A to U

and judging from no outputs from the debug prints, the error occurred on the first loop of importing, and nothing is imported.

Any advise

Thanks so much
-Hosacans

 

[Remou]

Have you tried omitting the range? It should not be necessary:

Range: 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.

 

[Hosacans]

Hi Remou - thanks for the quick response...

i need to input a range because that's where the headers are located for all the excel files.

anything above row 11 is just report titles and info, which i dont need to import.

i can do a macro or manually delete those rows, but i have alot of files and each file is huge and take awhile to open.

thanks
hosacans

 

[Remou]

Ok. I tried your code and I think the problem is that Application.Filesearch can return shortcuts as files. I think if you run Dir on the file name, it will show up this error.

 

[Hosacans]

you're right Remou, it does have something to do with the raw data files. somehow Access is not recognizing it as an excel format file. i copy and paste the data onto a new sheet and it imported fine with this code.

so i guess the next step is to find a way to "renew" all the excel sheets.

Thanks Remou for sending me to the right direction.

-hosacans