• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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

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
eantar
Asked:
eantar
  • 8
  • 7
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try using this for both file extension

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

post back the result
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Oh and BTW, acSpreadsheetTypeExcel12 is 2007 format, which still is normally an .xlsx extension.  You want acSpreadsheetTypeExcel8 for .XLS

Jim.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Rey Obrero (Capricorn1)Commented:
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
 
eantarAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
what access version are you using?
0
 
eantarAuthor Commented:
Access 2010.
0
 
Rey Obrero (Capricorn1)Commented:
try doing a compact and repair of your db then try again.

if the error still show,  upload a copy of your db
0
 
eantarAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db?
0
 
Rey Obrero (Capricorn1)Commented:
can you test the code, using a table instead of the query "qryTherapistsCaseloads4Excel"
0
 
eantarAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
what is the SQL statement of query "qryTherapistsCaseloads4Excel"?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
He needs to be using acSpreadsheetTypeExcel8 for an .xls extension.

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
@jim, he is already using it..
0
 
eantarAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Make sure you accept your own comment as answer.

Jim.
0
 
eantarAuthor Commented:
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
 
eantarAuthor Commented:
Found the answer through web search. Thanks again for sticking with me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now