Code change to current excel import format

[Leesha]

Hi,
I'm using this code to import excel files. The files were originally in 97 format but are now xlsx. I'm not sure how the code needs to be changed.

Code: Select all

    strFolder = "C:\xxxxx\Import\TTTSMDailyFile\"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
        DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadsheetType:=acSpreadsheetTypeExcel97, _
            TableName:="tblCSVImportTemp", _
            FileName:=strFolder & strFile, _
            HasFieldNames:=True
            'Range:="Import"
        strFile = Dir
    Loop

Thanks!

[HansV]

You need to change *.xls to *.xlsx and acSpreadsheetTypeExcel97 to acSpreadsheetTypeExcel12Xml:

Code: Select all

    strFolder = "C:\xxxxx\Import\TTTSMDailyFile\"
    strFile = Dir(strFolder & "*.xlsx")
    Do While strFile <> ""
        DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblCSVImportTemp", _
            Filename:=strFolder & strFile, _
            HasFieldNames:=True
            'Range:="Import"
        strFile = Dir
    Loop

[Leesha]

Thanks Hans!
I figured I'd need to change the .xls but even when I tried that I got an error because I didn't know what to put in for the second part. I appreciate the help.
Leesha