VBA: TransferSpreadsheet output Excel 2007

What can I use in the transferspreadsheet command to get it to write the spreadsheet out as .XLSX type?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ....

Open in new window

ronallardAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Use acSpreadsheetTypeExcel12Xml, or its numeric value, 10
0
 
ronallardAuthor Commented:
Thank you kind sir... You're always there to help!
0
 
Jeffrey CoachmanMIS LiasonCommented:
matthewspatrick,

Did you mean:
Docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, ...
You posted the XML format.

Jeff
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ronallardAuthor Commented:
I don't know xml? All I posted is a snipit of the code currently in use and since we've upgraded to 2007 I thought I would change the code to create spreadsheet in that version..???? The instruction you gave me works fine.
0
 
Jeffrey CoachmanMIS LiasonCommented:
ronallard,

Just a note.

If you export to the 2007 format, people who do not have Office 2007 will not be able to open the file.

When you type in VBA intellisense should list the "Constraints".
If not, you can right click and select: List Constraints, then they will be listed.

;-)

Jeff
untitled.JPG
0
 
ronallardAuthor Commented:
Interesting!  I have that on, but do not get  ...12 or ...12xml in the drop-down????
0
 
ronallardAuthor Commented:
By the way is there a significant difference between those two types?
0
 
Jeffrey CoachmanMIS LiasonCommented:
"I have that on, but do not get  ...12 or ...12xml in the drop-down"
I presume it worked, because you accepted a post.
Is this the case?


" is there a significant difference between those two types"
Yes, there is a difference.
The xlsx format is Excel 2007 only, meaning it is an Excel file.

XML is a format is a bit harder to define.
To keep it simple, it is basically a format that allows the file to be read by any program that can accept XML Data, or any of the newer web technologies.

Perhaps matthewspatrick can provide a more detailed definition...
;-)

Jeff
0
 
ronallardAuthor Commented:
I just changed "acSpreadsheetTypeExcel9"  to "acSpreadsheetTypeExcel12xml" and it created the spreadsheet as .xlsx. I sounds like I should change it to "acSpreadsheetTypeExcel12" if I'm just looking for the 2007 version.
0
 
Jeffrey CoachmanMIS LiasonCommented:
The xlsx file extension is the desinated extensin for Access 2007 formatted Excel files.

Don't take a chance, if you do not explicitly need the XML format, don't use it.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.