Solved

in TransferSpreadsheet, why can't I use extension .xlsx with acSpreadsheetTypeExcel12Xml OR acSpreadsheetTypeExcel12???

Posted on 2016-10-25
20
31 Views
Last Modified: 2016-10-30
Hey experts,

I'm having a problem with some data I'm trying to export to Excel 2010. I'm allowing the user to use either the .xls or .xlsx extension. My code looks like this:

DoCmd.TransferSpreadsheet acExport, IIf(Right(txtExportPathAndFileName, 5) = ".xlsx", acSpreadsheetTypeExcel12Xml, acSpreadsheetTypeExcel12), "qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True

When I try to open the exported .xlsx, I get a message that the extension or file type is invalid.

Any ideas?

Thanks
0
Comment
Question by:eantar
  • 8
  • 7
  • 5
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859236
try using this for both file extension

DoCmd.TransferSpreadsheet acExport, 10, "qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True

post back the result
0
 
LVL 57
ID: 41859260
I'd do it this way:

If Right(txtExportPathAndFileName, 5) = ".xlsx" Then
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,"qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True
Else
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,"qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True
 End If

then put a stop on the IF and verify if the extension is on the end or not and that your falling through  the correct logic (use F8 to step through)

Jim.
0
 
LVL 57
ID: 41859265
Oh and BTW, acSpreadsheetTypeExcel12 is 2007 format, which still is normally an .xlsx extension.  You want acSpreadsheetTypeExcel8 for .XLS

Jim.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859293
or this


If Right(txtExportPathAndFileName, 5) = ".xlsx" Then

DoCmd.TransferSpreadsheet acExport, 10, "qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True

else

DoCmd.TransferSpreadsheet acExport, 9, "qryTherapistsCaseloads4Excel", txtExportPathAndFileName, True

end if
0
 
LVL 5

Author Comment

by:eantar
ID: 41859363
Arrggg! Driving me nuts!

I broke up my If statement up into an if then else and use the literal 10 for acSpreadsheetTypeExce12XML. I'm still getting the invalid message:

message I get.
Any ideas why this might be happening. This is a simple Transfer Spreadsheet call.

Any help would be greatly appreciated.

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859368
what access version are you using?
0
 
LVL 5

Author Comment

by:eantar
ID: 41859378
Access 2010.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859392
try doing a compact and repair of your db then try again.

if the error still show,  upload a copy of your db
0
 
LVL 57
ID: 41859393
0
 
LVL 5

Author Comment

by:eantar
ID: 41859425
Hi Jim,

Yes, I read your post. Here's a picture of how I rewrote the code, but I'm still having the same problem.

Debugger and Intermediate window
Any ideas??? It just shouldn't be.

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859428
can you upload a copy of the db?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859433
can you test the code, using a table instead of the query "qryTherapistsCaseloads4Excel"
0
 
LVL 5

Author Comment

by:eantar
ID: 41859436
Hi Rey,

Uploading the db is a bit of a problem because I'm working with HIPPA compliant information. I'd have to dummy up some data.

But I just read something I'm going to try before I take that step.

Thank you.

E
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859440
what is the SQL statement of query "qryTherapistsCaseloads4Excel"?
0
 
LVL 57
ID: 41859444
He needs to be using acSpreadsheetTypeExcel8 for an .xls extension.

Jim.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859447
@jim, he is already using it..
0
 
LVL 5

Accepted Solution

by:
eantar earned 0 total points
ID: 41859450
ARRGH! FOUND IT!

Or found something that I didn't know was required to do, but this worked.

When I first ran the Transfer Spreadsheet to xlsx, I ran it with the file type of acSpreadsheetTypeExcel12, not acSpreadsheetTypeExcel12Xml. Then I researched and found that to export to xlsx you need to use acSpreadsheetTypeExcel12Xml. So I changed my file type to that. BUT! I didn't change the file name that I was exporting. I assumed that Excel would just overwrite it. It doesn't. I'm not sure what it does. But when I updated the code to KILL the old file name BEFORE creating the new one, THAT WORKED!!!

Bizarre.

Anyway, thank you guys for sticking with me through this.
0
 
LVL 57
ID: 41859452
Make sure you accept your own comment as answer.

Jim.
0
 
LVL 5

Author Comment

by:eantar
ID: 41859458
Thanks Jim. I always feel bad about doing that because I appreciate the company and feedback while I'm figuring this stuff out, even when I found the answer myself. But I know that those are the rules. Will do.
0
 
LVL 5

Author Closing Comment

by:eantar
ID: 41865748
Found the answer through web search. Thanks again for sticking with me.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Access 2016 syntax 6 44
Calculation in Access 5 26
2 IIF's in Access query 25 31
Access Importing Table Query With Specific Timestamp Daily 2 15
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 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