Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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".

as
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
add comment

2 Answers

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", _
              "Table1.xlsb" 

or

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml, _
     "Table1", _
     "Table1.xlsx"
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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.