Solved

Transferring MS Access data to MS Excel

Posted on 2010-08-31
9
1,076 Views
Last Modified: 2012-06-21
What is the difference between the three below options when transferring a Microsoft Access 2007 data source to MS Excel 2007?  Also, which do you prefer and why?
1.       acSpreadsheetTypeExcel12Xml
2.      acSpreadsheetTypeExcel9
3.      acFormatXLSX
0
Comment
Question by:CFMI
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
1 and 2 for use with docmd.transferspreadsheet

1. create in current version. have not found any main difference between this and acSpreadsheetTypeExcel12
2. create in older version of office 2000
3. not to be used with docmd.transferspreadsheet, probably more to do with docmd.outputto and is current version
0
 
LVL 9

Expert Comment

by:hitsdoshi1
Comment Utility
Use
DoCmd.TransferSpreadsheet acExport, 10, etc.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I initially thought acSpreadsheetTypeExcel12Xml would create it in xml format, perhaps it can be used as part of importing but exporting does not seem to make a difference. Probably better to use acSpreadsheetTypeExcel12 though or not bother specifying (it is optional).
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
back to the question which do I prefer? I don't have a preference. I never specify it unless I know I will be working with a particular version of office (like handling older versions).

with regards to what command to use? well I use both and use the tool which I feel is right for the job.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
see this link

http://msdn.microsoft.com/en-us/library/bb225982%28office.12%29.aspx
scroll down to the  bottom of page
0
 
LVL 1

Author Comment

by:CFMI
Comment Utility
Gotcha on the versioning issue (eg., acSpreadsheetTypeExcel7,8,9, etc.).  Thank you.  So now I'm seeing three comparable methods:
•      DoCmd.TransferSpreadsheet acExport, 10, "MyQueryName", "C:\MYxlsxFileName. xlsx ", True
•      DoCmd.TransferSpreadsheet acExport, [acSpreadsheetTypeExcel12Xml] or [acSpreadsheetTypeExcel12], "MyQueryName", "C:\MYxlsxFileName. xlsx ", True
•      DoCmd.OutputTo acOutputQuery, "MyQueryName", acFormatXLSX, "C:\MYxlsxFileName.xlsx", True

Any notable differences here?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
I don't recommend using hardcoded numbers, use the predefined constants instead.

Main difference between the two, you can specify ranges in transferspreadsheet so you can append worksheets
outputto is one straight write so would overwrite anything previously created

This command

DoCmd.TransferSpreadsheet acExport, , "MyQueryName", "C:\MYxlsxFileName. xlsx ", True

would work just as well because then I believe it would use whatever version you have.

that 2nd parameter is a enumeration type called AcSpreadSheetType. Type that in followed by . should bring up the list of constants available.

0
 
LVL 1

Author Closing Comment

by:CFMI
Comment Utility
Thank you!
0
 
LVL 1

Author Comment

by:CFMI
Comment Utility
It's interesting that 2nd parameter - enumeration type AcSpreadSheetType - typed in and followed by . brings up "a list of constants available" (including acSpreadsheetTypeExcel7,8,9) but NOT "10, 11, or 12."  I don't know if that's dependent on the active reference library but glad to get the good info from you!  Thx again.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now