export table to excel (1 Viewer)
- Thread starter focus10
- Start date
isladogs
MVP / VIP
- Local time
- Today, 02:52
- Joined
- Jan 14, 2017
- Messages
- 18,216
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:
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:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, ExcelFile, True, Range
isladogs
MVP / VIP
- Local time
- Today, 02:52
- Joined
- Jan 14, 2017
- Messages
- 18,216
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
Note that 10 years on, the help file still doesn't include the newer Excel file types:
- acSpreadsheetTypeExcel12 / acSpreadsheetTypeExcel12xml
Attachments
-
Capture.PNG
isladogs
MVP / VIP
- Local time
- Today, 02:52
- Joined
- Jan 14, 2017
- Messages
- 18,216
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:
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:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, ExcelFile, True, RangeFocus10,
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.
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.
