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
> Export To Excel Error (docmd.transferspreadsheet), Any Version    
 
   
ws1o
post Jun 17 2019, 09:35 AM
Post#1



Posts: 104
Joined: 30-October 15
From: Central CT USA


Have you had trouble opening an Excel you exported using the VBA Docmd.TransferSpreadsheet method? I have found that exporting using the built-in constant acSpreadsheetTypeExcel12Xml gives me an error (I can't open the Excel file from code, such as Application.FollowHyperlink), but if I use the Docmd.TransferSpreadsheet command with the NUMERIC value of the Excel version (10) it works fine. What the heck is going on that Access doesn't seem to recognize the value of its own Constant?
Go to the top of the page
 
DanielPineault
post Jun 17 2019, 09:48 AM
Post#2


UtterAccess VIP
Posts: 7,010
Joined: 30-June 11



What error do you get when using the constant?

What version of Access are you running? (version, build, bitness)?
I just tested on A2013 x32, A2016 x32 & A2019 x32 without any issue. I tested by using
CODE
Sub export1()
    Const sFile = "C:\Users\Daniel\Desktop\Export1.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MyTable", sFile
    FollowHyperlink sFile
End Sub

Sub export2()
    Const sFile = "C:\Users\Daniel\Desktop\Export2.xlsx"
    DoCmd.TransferSpreadsheet acExport, 10, "MyTable", sFile
    FollowHyperlink sFile
End Sub

Perhaps you should try an Office Repair to see if that helps at all?
Does your code compile without errors?
Have you compacted recently?
Perhaps post a sample db that replicates the issue for us to examine and pass along to the Dev Team if the issue confirmed.

That all said, I long ago abandoned using Transfer... as I hit different issues when trying to use them. After wasting enough time coming up with fixes and workarounds ... I finally decided to create an Excel export proc of my own which provides much more control and since you have the Excel object you can format things to your heart's content. Refer to http://www.devhut.net/2017/03/15/ms-access...rdset-to-excel/ should you wish to learn to do the same.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
gemmathehusky
post Jun 17 2019, 11:20 AM
Post#3


UtterAccess VIP
Posts: 4,750
Joined: 5-June 07
From: UK


Excel Version 10.
Do you mean Excel 2010, or an older version

ie Access Version 11 is A2003
I presume Access Version 10 is A2002.

Do you have an old version of Excel? If so, it won't be able to open an XML file.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post Jun 17 2019, 11:37 AM
Post#4


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


@Dave,

I think the OP means: acSpreadsheetTypeExcel12Xml = 10

--------------------


Regards,

David Marten
Go to the top of the page
 
ws1o
post Jun 17 2019, 12:37 PM
Post#5



Posts: 104
Joined: 30-October 15
From: Central CT USA


Excel and Access part of Office 365 Pro Plus Version 1902 Build 11328.20222
Go to the top of the page
 
ws1o
post Jun 17 2019, 12:38 PM
Post#6



Posts: 104
Joined: 30-October 15
From: Central CT USA


I've got a fancy Excel export process using automation etc (object models etc). But I just wanted to use the built-in quick-and-dirty TransferSpreadsheet code, and it's a shame that it's so buggy. Come on, Microsoft, it's all your code!
Go to the top of the page
 
ws1o
post Jun 17 2019, 12:40 PM
Post#7



Posts: 104
Joined: 30-October 15
From: Central CT USA


The 10 is the numeric value for the built-in MS Access constant called acSpreadsheetTypeExcel12xml (type it into the VBE Immediate window and you'll see)
Go to the top of the page
 
gemmathehusky
post Jun 18 2019, 10:56 AM
Post#8


UtterAccess VIP
Posts: 4,750
Joined: 5-June 07
From: UK


I remember seeing some excel xml files (produced by SAP?)

If I opened them FROM excel, I got an "irregular format" message, but they opened. But if I tried to just follow hyperlink, they wouldn't open. Something like that.
See if the files can be opened by excel.




--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
BuzyG
post Jun 18 2019, 11:18 AM
Post#9



Posts: 419
Joined: 20-September 12
From: Cornwall UK


I use TransferSpreadsheet in MS Access 2016, to export various crosstab queries, for display using Excell, 2016. No issues for me with it at all, powerful and useful command.

CODE
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySystemModsAll_Crosstab", FileStr, True

--------------------
Live to Surf
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 11:07 PM