When I export a table or a query from access to excel manually, I can easily have the resulting format be in the latest version of Excel, referred to as Biff8, Excel 8-10 or Excel 97-2003, depending on the method used.

When I use a macro to export the table (TransferSpreadsheeet) or query (OutputTo) , I can set it up programmatically to automatically format the resulting Excel file in the latest file format, using If I add to my macro a set warnings=false as the first command in the macro, and set warnings=true at the very end, this all happens fluidly, with no error messages, no prompts.

If I convert the macro to a function, the table export (TransferSpreadsheeet) will work the same way, as expected. However, when the part of the code/function (OutputTo) runs for the query export, it trys to save it as Excel v5.0 - I have to manually click the correct button to override that default, and have it save it in the latest format.

Steve

Hi,
you can specify the version to be used in both the OutputTo and TransferSpreadsheet method. Can you paste the code that the macro conversion produced? BTW...why do you turn the warnings off? Are you executing an action query? If so then you should rather execute it with the .Execute method without turning off the warnings.
HTH
Good luck

Olie,

Thanks for your input ! Here is the code:



Thanks !

Steve

So why do you use the TransferSpreadsheet method and the OutputTo method? Does one do something different then the other?
HTH
Good luck

Those are the respective options for each method

So if you use either of these it doesn't save in the correct file format?
HTH
Good luck

I first created a macro, and saved it as a module - the resulting code is what I posted - when I created the macro, those were the available options. TransferSpreadsheet you can only select table - with OutputTo, you can choose from a drop down of several choices, including tables AND queries. That's why the TransferSpreadsheet method is used with the table.

I'm pretty sure that you can output a query with the TransferSpreadsheet method, too. Just adjust the object name to be a query instead of a table.
HTH
Good luck

So if you're doing it my way, just "pretend" in my macro I'm exporting (transferring) a table, then when I convert my macro to a module, just replace the table name with a query object name ? Or maybe it will even let me type in the query name ?

Why create a macro in the first place? You can just do the method from scratch within your application code. If you start typing intellisense will help you with the syntax of the method and let you fill out the rest easily.
HTH
Good luck

Oli,
thanks !
Steve

No problem...good luck!

Oliver,

I've tested your theory and you are correct, but there still seems to be a bug in MS-Access when you use the OutputTo function in a VB Module to export a query, that it saves it as an Excel 95 worksheet, not a Micrososft Excel 97-2003 format. (Microsoft Excel Biff8).

I'm just wondering why it does this - you would think it would be the same for a table or a query - maybe they had two different groups in Redmond working on the two different procedures during development.

thanks again !

Steve

Hi,
Try to manually output the table or query (File--Export...). Select the appropriate excel file format you want to export to...then export it. Then use the OutputTo method in code and redo the export with the acFormatXLS constant. See what version that outputs the file to.
HTH
Good luck