Solved

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

Posted on 2016-10-25
20
56 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
[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
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

737 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