Using VB to Export an Access Query to Excel 1

[matzos]

Well, the title pretty much says it all...

I have to write up a program that will automatically run and export an Access query on a weekly basis. I have all of the programming necessary to initiate the timing automation but I can't seem to figure out how to control the Access mdb and export the query.

Any help you be GREATLY appreciated!

Kevin

 

[gonzoaz]

You will have to have a vb program that will connect to access via DAO/ADO. The run the query to pull into a recordset in vb then you can export to Excel. MSDN has code on how to export to excel at that point. Or I can post it...

 

[bjd4jc]

Set a reference in your code to the Microsoft Access x.x Object Library

Dim accApp As Access.Application

Set accApp = New Access.Application

accApp.OpenCurrentDatabase "database path"
accApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableName", "excel file name"

accApp.CloseCurrentDatabase
Set accApp = Nothing