TransferSpreadsheet

[chippy2]

Hi all

I have an application in Access 97 that uses DoCmd.TransferSpreadsheet to
get data from an Excel spreadsheet into a table. This has worked quite
happily with Excel 2000 spreadsheets for several years but the supplier of
the spreadsheets, who is completely outside my and my customer’s control, has
upgrade their version of Excel (guess it’s gone to 2003 or 2007) and the
Access TransferSpreadsheet method no longer recognises the format. I’ve tried
using DAO instead, but Access 97 has no ISAM to handle the connection. Short
of making my immediate customer go through the nausea of saving the
spreadsheets in an older format every day, does anyone know of a fix (like an
ISAM for Access 97 to connect to the newer version of Excel)?

Any advice would be much appreciated.

Chip

 

[chippy2]

If anyone's interested I cracked this by using the Excel object model from
VBA within Access to load each spreadsheet and SaveAs an earlier version of
Excel.

 

[Arman]

Hi chippy2, could you give an example of your VBA code?

Thanks.

 

[chippy2]

Hello. What follows is not a complete solution (e.g. you have to cater for
what happens if Excel is already open and you need to provide a full path to
the workbook location), but the salient bits are:

Dim excl As Excel.Application
Set excl = CreateObject("Excel.Application")
excl.Workbooks.Open "workbook.xls"
excl.ActiveWorkbook.SaveAs "tmp.xls"
Kill "workbook.xls"
excl.ActiveWorkbook.SaveAs FileName:="workbook.xls",
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
excl.ActiveWorkbook.Close
Kill "tmp.xls"