[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Transferring MS Access data to MS Excel

Posted on 2010-08-31
9
Medium Priority
?
1,155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33568351
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
ID: 33568367
Use
DoCmd.TransferSpreadsheet acExport, 10, etc.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33568386
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33568423
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33568432
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
ID: 33568535
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 2000 total points
ID: 33571353
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
ID: 33575480
Thank you!
0
 
LVL 1

Author Comment

by:CFMI
ID: 33576961
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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. …

656 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