?
Solved

VBA: TransferSpreadsheet output Excel 2007

Posted on 2009-02-19
10
Medium Priority
?
780 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:ronallard
  • 5
  • 4
10 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 23681471
Use acSpreadsheetTypeExcel12Xml, or its numeric value, 10
0
 

Author Closing Comment

by:ronallard
ID: 31548752
Thank you kind sir... You're always there to help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23681615
matthewspatrick,

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

Jeff
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ronallard
ID: 23681686
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23681706
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
 

Author Comment

by:ronallard
ID: 23681723
Interesting!  I have that on, but do not get  ...12 or ...12xml in the drop-down????
0
 

Author Comment

by:ronallard
ID: 23681733
By the way is there a significant difference between those two types?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23682805
"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
 

Author Comment

by:ronallard
ID: 23683135
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23686128
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question