Running Queries from a macro

[krispetrie]

I need to create a macro that opens a query and saves it to a worksheet in an EXCEL workbook and then opens the next query and saves it to the next page in the workbook and so on. I can do this manually, but it is time consuming and inaccurate. I have another workbook that is linked to this workbook and pulls data from the cells. Thanks a lot!

 

[The_Doc_Man]

If the number of sheets is fixed and the size of each sheet is fixed, a macro could do this. However, if either of those factors is variable, you probably need to delve into VBA code to do it. In which case, you will not do a TransferSpreadsheet in the macro, but instead would do a
DoCmd.TransferSpreadsheet in your VBA code. You can export to a spreadsheet and specify the destination "sheet!upperleftcell:lowerrightcell" as one of the argument strings for that call. Of course, another argument in the call is the acExport option and another is the name of the file to which you are doing the export function. Look in the Help topic for DoCmd action or method, then click the "See Also" to find the TransferSpreadsheet action and get a summary of its arguments.

Even if VBA is required, you could still trigger it with a macro that does a RunCode option on your VBA function. Hope this points you in a positive direction.

 

[Pat Hartman]

The help entries for TransferSpreadsheet Action/Method specifically state that you CANNOT specify a worksheet or range on export, only on import.

You will need to get into OLE if you want to place data at specific places in a spreadsheet.