asked on

MS access 2010. Trouble on exporting to excel 2010

I had a problem to export an Access (2010) table/query to Excel 2010 format.
I've tried with these two commands:
1. DoCmd.TransferSpreadsheet A_EXPORT, acSpreadsheetTypeExcel12, <tableName>, <ExcelFileName>, True
2. DoCmd.TransferSpreadsheet A_EXPORT, acSpreadsheetTypeExcel12Xml, <tableName>, <ExcelFileName>, True

For both commands I put 'xlsx' extension for the name of Excel file.

When I try to open the excel output file, I receive the following errors:
1. (acSpreadsheetTypeExcel12). With the first command, Excel answers with the message "Excel cannot open the file <ExcelFileName> because the file format or file extension is not valid. Verify that the file has not been corrupted and that file extension matches the format of the file.". The file is unusable, even if I double-click on the file or I open file after Excel has been opened.
2. (acSpreadsheetTypeExcel12Xml). With the second command, Excel shows this warning message "Excel found unreadable content in <ExcelFileName>. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes". After clicking 'yes' Excel shows a form I've attached as 003.jpg. After clicking 'close' button the file is usable. After saving the file, Excel converts or corrects the file and all warnings disappear.
The exporting of Excel file on previous format, works fine.

Does anyone know if there is a way to export an excel 2010 file without these problems?

best regards
Giuseppe Siboni
003.JPG

Rey Obrero (Capricorn1)

delete first the excel file
if dir(<ExcelFileName>) <>"" then kill <ExcelFileName>

try this, see if this will export correctly

DoCmd.TransferSpreadsheet acExport, 10, <tableName>, <ExcelFileName>, True

als315

Can you manually export your table?

Jim Dettman (EE MVE)

Your first error message is because you specified the old format, but with an .XLSX extension.

Not sure on the second message. As Rey said, try killing the file off first.

Jim.

Giuseppe Siboni

ASKER

Dear Rey,

before exporting the excel file I've already the command to kill, if it exists, the previous version of the file

DoCmd.TransferSpreadsheet acExport, 10, <tableName>, <ExcelFileName>, True
The constant "acSpreadsheetTypeExcel12Xml" is equal to 10, so the command produces the same result.

Thank you for your quick answer

Rey Obrero (Capricorn1)

how about exporting the file manually and saving it, do you get the same error?

Giuseppe Siboni

ASKER

yes, the result is the same, excel shows the same warning messages

Rey Obrero (Capricorn1)

do you have the latest update of Office 2010?

Jim Dettman (EE MVE)

One other thought; if you have anti-virus on, try turning it off and exporting. Anti-virus may be trying to clean the file.

Jim.

Giuseppe Siboni

ASKER

it seems to be upgrade, I've chosen to upgrade during the windows upgrade procedure
the microsoft web page (https://support.microsoft.com/it-it/help/2121559/how-to-determine-the-service-pack-level-of-your-office-2010-suite)
shows release numbers (see 005.jpg)
my release number is greater then sp2 number
004.JPG
005.JPG

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Giuseppe Siboni

ASKER

it is a quiet normal table, made of 15 columns and 172 records; field types are Text, number or boolean; Fileds name doesn't contain any space or strange character.
If I export the same table in Excel XP format, everything works fine.
I've tried the same operation on 3 computer.
- a physical computer with Windows 7 64 bit and ms office 2010 32 bit (MCAfee antivirus)
- a virtual machine with Windows 7 32 bit and ms office 2010 32 bit (without antivirus)
- a virtual machine with Windows 7 64 bit and ms office 2010 64 bit (avast free antivirus)

the result is the same

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Giuseppe Siboni

ASKER

Dear Rey,

I've discovered the problem.
In my Access db the table I've tried to export is named "#ENG_output_L1" and the excel output file name is "engineL1.xlsx".
I've renamed the table removing the "#" character ("ENG_output_L1") and now it works fine.
I'm sure to say: a table with name that begins with "#", produce a problem in the excel export file header section. Could be useful to comunicate to Microsoft, if it's possible.
many thanks to everybody for help and collaboration
Giuseppe Siboni