DoCmd.OutputTo Exporting in Excel 5.0/95 Format

[purceld2]

I have some VBA code which exports the Query in Excel 5.0/95 format which I feel is causing problems when I later call a Excel macro to format the exported spreadsheet. The Excel macro has been recorded using Excel 2003 format and I feel this is the problem.

How can I get Access to export in Excel 2003

Export code below

DoCmd.OutputTo acOutputQuery, "supplier gate call", acFormatXLS, _
         "c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call.xls", False

 

[Vonnie]

You don't say what Version of Access you are using, but you might try the transferspreadsheet command also to get more control over version.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, "qrytest", "d:\data\export.xls"

 

[purceld2]

Thanks for your reply

The version I am using is Excel 2003

I was using Transferspreadsheet but the outputted format was all over the place OutputTo maintained the formatting.

 

[Vonnie]

The VBA code is in what version of Access?

 

[purceld2]

Access 2003

 

[Vonnie]

Ah, I see now. I don't know if it is a bug or not, but I have read before that Access 2003 when using the Outputto will output in 95 format. And the only argument available is the acFormatxls. You might do a search and see if there is another solution, otherwise you might outputing a form or rpt and see if you get the same problem, or use another macro or VB or VBA code to first save the XLS as the correct format, then run the 2nd macro.

 

[tranchemontaigne]

I know this adds extra complexity, but I have had success using the ADODB recordset method to export MS Access data to MS Excel, and then passed MS Excel VBA code directly from MS Access to MS Excel to draw heirarchal headers, draw lines, merge cells etc within a created MS Excel spreadsheet.

My source articles for learning how to do this come from Rumania

http://zmey.1977.ru/Access_To_Excel.htm
http://zmey.1977.ru/Excel_And_Autofilter.htm

As I'm sure you are aware, you might want to use the MS Excel macro wizard to write your MS Excel VBA code (should you too have better fluency with MS Access VBA than MS Excel VBA) so you can copy/paste/edit within the MS Access VBA module that tells MS Excel how to format the spreadsheet.

One of the nice things about this method is that you can tell MS Excel exactly which cell you want to use to anchor the MS Access exported data so that you can leave enough rows above your data to allow for heirarchical headers. You can also paste whatever string values into any cell you want and in effect emulate company letterhead etc if you desire.
________
Toyota Hybrid X History

 

[purceld2]

Is it not possible to then open the spreadsheet again and save it with

Workbook.Saveas method before I run the called Excel macro.

It seems that becase the files has been save as 95 format and the Excel macro benn recorded using Excel 2003 some of the actions do not work like condistional formatting.

any ideas

thanks in advance

 

[tranchemontaigne]

This might work for you because the Workbook.SaveAs Method supports the FileFormat argument
________
Acura el specifications

 

[purceld2]

What value should I specify for it to be saved in Excel 2003 form

regards

 

[Vonnie]

If you are doing it in your macro, you can just resave the active document using VBA
ActiveWorkbook.saveas and save it the file format you wish in the arguments FileFormat:=xlExcel8, etc.
I would just create a temp macro using record macro wizard in EXCEL to get your coding the way you want it. They you could add it to the beginning of your original macro.
You could manipulate it from an Excel object in Access but sometimes simple workarounds are best and easy to support

Then hopefully your macro will perform correctly for you.