export table to excel

[focus10]

after the last microsoft update access 2003 fail to export to excel 2010
by DoCmd.TransferSpreadsheet

is anybody has a solution?

thanks

 

[isladogs]

You are probably going to have issues trying to export from an old version of Access to a newer version of Excel

However you could try changing the spreadsheet type in the code from acSpreadsheetTypeExcel9 (or similar) to acSpreadsheetTypeExcel12 (for Excel 2007 or later)

Syntax something like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, ExcelFile, True, Range

 

[arnelgp]

I don't think the enum constant is available in a2003?

 

[isladogs]

I no longer use A2003 but this is the help file info taken from A2010:

Note that 10 years on, the help file still doesn't include the newer Excel file types:
- acSpreadsheetTypeExcel12 / acSpreadsheetTypeExcel12xml

 

[focus10]

acSpreadsheetTypeExcel12 is not recognized by access 2003

must be some where another solution

 

[isladogs]

That really doesn't surprise me as its for the later version.

You are always going to get problems like this when you mix up Office versions

However, it should still work with the older version designed for Excel 2000-2003:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, ExcelFile, True, Range

 

[Mark_]

Focus10,

Can you export to a CSV then open said CSV in excel? The DoCmd.TransferText method may offer another route to achieve the same results.

This should avoid version issues with different users having different versions of excel. Also affords the ability to import into earlier versions, should one still have one.

 

[focus10]

acSpreadsheetTypeExcel9 did the job until the last update by microsoft.
after the last update we are in a new era with a new problem.

 

[focus10]

csv can be a good solution for self using.
but not enough when customers are involved

thanks for the answer