Docmd.Transferspreadsheet from Access 2007 to Excel 2007 .xlsm help needed...

Are you trying to export to a new Excel file or an existing Excel file?

If you are trying to an export to a new Excel file, I would think you would want the xlsx format. xlsm is a Macro-Enabled Excel file. If it is a new file, it wouldn't have any macros in it, so I am not sure why you would want to export to that option.

No, I am exporting to an existing .xlsm file.

Please don't tell me this is some Mickey Mouse Microsoft trying to protect me from myself crap.

Please don't tell me this is some Mickey Mouse Microsoft trying to protect me from myself crap.

Click to expand...

I have no idea, but it wouldn't surprise me.

Here is a potential workaround that I think should work (in theory).

Save the xlsm file to a lower version so that it has an xls extension (make sure all macros are still operational). Then you can export data to the xls version of the file (it is one of the options).

Another option might be to save your Macros to a different workbook (maybe even Personal Workbook), or save them as an Add In instead.

Re: Export Tables to Multi Tab Excel Workbook

This code...


Returns this error: External Table is not in the expected format.

YES, I used the actual path, file name, and query name

NOTE: I am exporting to an Excel 2007 Macro-enabled file (.xlsm)

HOW COME Excel 2007 is NOT listed here:http://msdn.microsoft.com/en-us/library/bb225982.aspx ??

All I want to do is automatically export a query to Excel so I can then open the Excel file and run macros like I have done hundreds of times over the past TEN, count 'em 1-2-3-4-5-6-7-8-9-TEN YEARS!!

Moderator Note:
I moved your reply back to your own thread here, instead of someone else's thread. Please try to keep them altogether! It makes it hard for people like me who are trying to help you if you are posting things all over the place!

NOTE: I am exporting to an Excel 2007 Macro-enabled file (.xlsm)

Click to expand...

All I want to do is automatically export a query to Excel so I can then open the Excel file and run macros like I have done hundreds of times over the past TEN, count 'em 1-2-3-4-5-6-7-8-9-TEN YEARS!!

Click to expand...

That would be a truly fantastic feat, seeing as how xlsm extensions have only been around for 3 years!

But seriously, have you tried any of the suggestions I gave you in post #4? You mentioned that you want to keep trying to do what you did for the past 10 years, so why not try that? Save your files as .xls files instead of .xlsm files and then see if that allows you to continue to do what you want.

If your macros where working fine when they were "xls" files and you really haven't added any features or VBA code that is exclusive to Excel 2007, there probably isn't a need to convert your "xls" file to a "xlsm" file.

Found this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=362916

Looks like you cannot currently export to an XLSM file, at least until they decide to include it in an upgrade.

I still think you may be able to get around it using the methodology I proposed.

Joe4 said:

Found this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=362916

Looks like you cannot currently export to an XLSM file, at least until they decide to include it in an upgrade.

I still think you may be able to get around it using the methodology I proposed.

Click to expand...

Well, at least that answers it: MSFT strikes again! Android and Open Office are looking better and better every day.

As for the methodology you suggest, If I use .xls and a lower version of Excel, then all the functionaloty of 2007, better pivot tables, better conditional formatting, etc. etc. goes with it. MSFT in their quest to protect us from ourselves has decided to create all these new file extensions because "Macros are bad!" because some idiot somewhere once deleted his hard drive because he executed a bad macro and MSFT got bad press about it has gotten out of hand.

So now I get to spend god knows how many hours devising yet ANOTHER "duct tape and spit" work arounf for all these changes in Office 2007.

Before you ask, NO I did not CHOOSE to go with 2007. Some big-shot at my company lost a golf bet to some big shot and MSFT and it got rammed down our throats.

As for the things I've done for the past ten years, the one I miss the most is [File]>[Print]

Oops, I forgot to say "Thank You" for looking it up.... Thank You.

Thanks Waxov, if you're still out there. That was one of the most entertaining threads I've read in a while.

And I've written a few rants myself.