Running multiple queries and exporting them

[krispetrie]

I would like to set up an event or macro that will run a query and save it as a worksheet in an excel file, then run another query and save it as the next page in the workbook and so on. Can anyone help me here? Thanks!

 

[The_Doc_Man]

I'm no expert in Excel but here's a thought:

The TransferSpreadsheet action (in the macro) allows you to specify two useful parameters for exportation: The name of the .XLS file and the range of exportation within the sheet.

I believe that if you could correctly encode the sheet number in the range argument, you could do what you wanted. But you would probably have to play with it a bit to get it exactly right.

The Help file suggests that the correct format for the range argument might be something like

Sheet1!A1:C7

to export a 3x7 table to the spreadsheet. The catch is, of course, to be able to predict the size of the output array or figure out what is required to just specify the sheet without the cell bounds. There, I can't help you because I'm not a frequent Excel user.

 

[Pat Hartman]

You can NOT specify a range when using the TransferSpreadsheet Method/Action for export. Quoted from the help entry:

Range	The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax no longer works in Microsoft Access 97). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.
Note   When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.[\code]

You will need to do the export with OLE or DDE.

 

[edb]

I am trying to do something similar. Does anyone know how to export a report to excel? My report is page broke by employer groups. I would like the export to put each employer group in its own excel worksheet and all sheets in the same workbook.

Thanks