 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005Exporting Multiple Queries to a single Excel worksheet
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 -- Jinxi Visit - http://www.officehelp.in | http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph 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. > I am attempting to export the results of multiple queries to a single, > pre-existing, formatted 'data' worksheet in and existing Excel workbook [quoted text clipped - 11 lines] > > Cheers, 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 -- Jinxi Visit - http://www.officehelp.in | http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph Thanks to John I now have a working code example. I hope this helps ou somone out there :) > Private ObjXL As New Excel.Application > Private ObjBook As Excel.Workbook [quoted text clipped - 53 lines] > Hourglass = False > End Sub -- Jinxi Visit - http://www.officehelp.in | http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.