1

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?

Improve this question

2 Answers 2

Reset to default
1

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"
Improve this answer
1
  • @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, 2011 at 17:57
0

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?

Improve this answer

You must log in to answer this question.

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