Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are running VBA in Access 2007 and trying to create an Excel 2007 spreadsheet. We are using the following format:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table/Query Name Here", "FileNameHere".

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DimWebSample_Final", gsExportFilePath & gsExportFileNameBody & "_" & Month(gdDate) & Day(gdDate) & Year(gdDate) & "_" & gsDatasetExportFileSuffix(giDatasetID) & ".xls", True

I have Excel 2007 on my computer, but using acSpreadsheetTypeExcel9 creates Excel 87 or 2000 spreadsheets according to Access 2007 help. That is what we get. The help does not list another acSpreadsheetTypeExcelnn constanct to useu for 2007 results.

How would I get it to creates an Excel 2007 spreadsheet?

share|improve this question
up vote 1 down vote accepted

Excel 2007 introduces two new formats xlsb and xlsx. So you have to pick either acSpreadsheetTypeExcel12 or acSpreadsheetTypeExcel12Xml If your file extension doesn't match the type then Excel will complain when it opens it so you should also make sure your extension is correct.

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12, _
             "Table1", _


DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml, _
     "Table1", _
share|improve this answer
Thanks. I used the second one and got what I need. – Lars May 25 '11 at 16:40
@Lars I'm glad I was able to help. BTW at the risk of seeming to lobby for my answer to be accepted, there is a system for accepting answers, which will become important to you if you ask 4 or more questions. – Conrad Frix May 25 '11 at 17:57

On opening the xlsx file the following message shown
found unreadable content in 'Table1.xlsx'

After clicking the Yes button to repair the file the following message shown
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

What happened? and How could I prevent this?

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .