Export query to Excel and run excel macro

[Chris]

Hi. Can anyone provide a suggestion or code to export a
query to Excel then run macros from Excel, which I've
already created in Excel. I have my Excel macros in a
workbook called "Vendor25K" and my Access query is
called "Vendors". Is there a way I can have the query
export to this workbook and replace the existing "Vendors"
detail worksheet, then have my Excel macros fire? Or I
could have the query export to a new Excel workbook and
transfer my Excel macros into the Access module. Don't
know which way would be better or how to do it. Any help
would be greatly appreciated!!!

 

[John Nurick]

Hi Chris,

You can do all or any of this sort of thing by writing VBA code that
runs in Access and controls Excel - or vice versa. In the situation you
describe the latter might be better: in Excel, set a reference to the
DAO or ADO library, open a recordset into the query, and then use
Excel's Range.CopyFromRecordset method to paste the records into your
worksheet, then continue with your existing macros.

Doing it that way, Access isn't involved at all: Excel uses DAO to
control the Jet datbase engine which manages mdb databases. The
following links should be helpful:

HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
http://support.microsoft.com/?id=246335

INFO: Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/?id=247412

HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation
http://support.microsoft.com/?id=243394

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm
Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148
ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476) http://support.microsoft.com/?id=142476