Exporting Multiple Queries to a single Excel worksheet

[Jinxie]

I am attempting to export the results of multiple queries to a single
pre-existing, formatted 'data' worksheet in and existing Excel workboo
using VBA. This means that I need to be able to specify the range tha
will be populated by the export process.

I have written code that happily exports the query results to ne
worksheets using the TransferSpreadsheet and accmdExport methods, bu
cannot seem to get multiple record sets onto a single new sheet le
alone a single pre-existing one.

If someone could point me towards a method that supports thi
functionality or provide a coding example of how to do this fro
Access, I would be most appreciative.

Cheers, Jinxi

 

[John Nurick]

Hi Jinxie,

Probably you'll need to do this by automating Excel.

One way would be to have your Access VBA code open the workbook and set an
Excel.Range variable to point to the top left cell of the first range of
data. Then open a recordset containing the data for that range, and use
Excel's Range.CopyFromRecordset to plonk the data into the worksheet. Repeat
as necessary.

Another possibility would be to use Excel's own data facilities to import
the data to the ranges from the relevant queries.

 

[Jinxie]

John,

Thank you for the advice. I'll give it a go, I already have 90% of th
automation code complete, just didn't think to do it that way.

Cheers,

Jinxi

 

[Jinxie]

Thanks to John I now have a working code example. I hope this helps ou
somone out there