Upgrade from 97

[Fifer]

We have recently upgraded our systems from Access 97 to 2003, I am trying to amend the import module so that it recognises the excel sheet we use to import.

The old code is :

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, ImportTable, _
HostDir & ExtractFilename, True

This is the point the module stops at and I am presuming it is because of the reference to excel 97.

Can anyone assist e in the correct code so it picks up excel 2003..?

 

[Bodisathva]

Access2003 help file:

SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
...
If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed​