TransferSpreadsheet Error, Access 2003 to Excel 2007.

[BJ]

Hello

I've had Excel 2007 installed and am trying to run an Access 2003
application that transfers data to Excel. All the procedures worked
fine when using Excel 2003.
The section of code that is generating the error is:

'Transfer the recordset data to the workbook.
DoCmd.TransferSpreadsheet acExport, , QueryName, strNewWorkbookPath
_
, , NamedFormula

The error details are:
err.Number
3274

err.Description
External table is not in the expected format.

Any ideas as to how I can fix this error?

 

[Paul Shapiro]

Just a guess, but maybe set Excel's default to saving files in the older
Excel 2003 format. If the newly created Excel file is in 2007 format, Access
2003 may be unable to write to it. That's what the error description sounds
like to me.

 

[BJ]

Just a guess, but maybe set Excel's default to saving files in the older
Excel 2003 format. If the newly created Excel file is in 2007 format, Access
2003 may be unable to write to it. That's what the error description sounds
like to me.











- Show quoted text -

Thanks for your reply Paul.

I've tried looking through my latest book on Excel and Access
integration, but am unable to find any distinctly different code for
creating / referencing Excel to that in my 2003 version books.

Is there a way to set the version of Excel (97-2003) when opening it
in Access 03 via vba?
Do I have to add a new library to Access 03 in order to reference
Excel 07?

 

[SusanJane sjl]

I had this problem & it appears to be a glitch. I have macros that execute
TransferSpreadsheet and/or SendObject. You just have to go into the Action
part and re-set the Excel version. Here's the problem, though. I have some
users on 2003 and some on 2007 and even though the Excel format 97-2003
appears identical in Access 2003 and Access 2007 they are NOT compatible.
Fortunately, I kept Access 2003 on an older machine & backed up all my
databases to it.