Importing Excel 3.0 spreadsheets 1

[ClifCamp]

I am automating a process in an Access 2000 db. I have some code which runs a Oracle server front end (Cognos Impromptu), runs five queries, and exports them as Excel spreadsheets. The problem is that Impromptu will only export as Excel 3.0 sheets. The code then imports them into Access. Here is a sample for one of the sheets:

DoCmd.DeleteObject acTable, "tblPromotions"
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel3, _
TableName:="tblPromotions", _
FileName:="C:\Clif\Metrics\CurrentMonthFiles\Promotions.xls", _
HasFieldNames:=True

My problem is that Access gives me the message:
"External table in unexpected format." even when I say "acSpreadsheetTypeExcel3"

When I manually open the sheet and save as Excel 9, it imports it.

Does anyone know why this import won't work?

 

[vbajock]

I believe Impromptu will also export in DBASE III format. You might try that.

 

[vbajock]

Did it work?

 

[sahu74]

Hi ClifCamp,

I had a similar problem with the error that you are receiving. I was importing from Excel 97 into Access 97.

What I did was in the DoCmd.TransferSpreadsheet line where I had ,acSpreadsheetTypeExcel97, I ignored the argument and left the space blank.

E.G.
DoCmd.TransferSpreadsheet acImport, , "tblxxx", "C:\....\Excel.xls", True

This worked for me, it may work for you.

Good Luck