UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Transferspreadsheet Vs Outputto, Access 2013    
 
   
rsindle
post May 22 2020, 02:46 PM
Post#1


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


This PROBABLY applies to all versions of Access after 2007, however, I'm using 2013 for this question.

What is the deal with TransferSpreadsheet and outputting to the new .xlsx format?

I have been using this format for a while and it works fine:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMyQuery", "C:\temp\Excel9_format.xls"

However, I thought I'd like to export to a .xlsx format.
In the Object Browser, I see ... acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel8, acSpreadsheetTypeExcel9, acSpreadsheetTypeExcel12, acSpreadsheetTypeExcel12xml

So I assumed I could do:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryMyQuery", "C:\temp\Excel9_format.xlsx"

using acSpreadsheetTypeExcel12 and an .xlsx extension.
Unfortunately, I can't get TransferSpreadsheet to dump to .xlsx format, period.
None of the "12" constants will generate a proper .xlsx file.

I AM able to get the OutputTo command to do what I want using:

DoCmd.OutputTo acOutputQuery, "qryMyQuery", "ExcelWorkbook(*.xlsx)","C:\Temp\NewExcelFormat.xlsx"

but was wondering what's happened to TransferSpreadsheet?
Thanks in advance,
Rob

Go to the top of the page
 
theDBguy
post May 22 2020, 02:53 PM
Post#2


UA Moderator
Posts: 78,476
Joined: 19-June 07
From: SunnySandyEggo


Hi Rob. Did you try the 12Xml one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rsindle
post May 22 2020, 03:11 PM
Post#3


UtterAccess VIP
Posts: 1,711
Joined: 25-June 04
From: Northern Virginia


Wow! I must be having some senior moments. iconfused.gif I thought I had tried a bunch with that as well... Oh well....

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryMyQuery", "C:\temp\Excel9_format.xlsx"

Does NOT work, but

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryMyQuery", "C:\temp\Excel9_format.xlsx"

seems to work....
Thanks DBguy!
Go to the top of the page
 
theDBguy
post May 22 2020, 03:34 PM
Post#4


UA Moderator
Posts: 78,476
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it to work. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 03:18 AM