run excel macro

[Ketaki]

Hi,

I am creating an excel spreadsheet automatically through
MS Access macro's "OutputTo" function. I also have the VBA
code of an excel macro to be run in this spreadsheet. For
testing purposes i open this excel file and manually copy
this code in excel macro and run it. But I actually want
to run this macro/VBA code on this spreadsheet
automatically through a batchfile or something without
having to open this excel file and copying this VBA code
in a macro inside.

Thanks,
Ketaki

 

[John Green]

Ketaki,

As you are familiar with VBA I recommend using VBA in Access to control the entire job. You can use code like the following to copy
Access data to a new Excel workbook:

Dim oXL As Object
Dim oXLWBook As Object
Dim oXLWorkSheet As Object
Dim rs As New ADODB.Recordset

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oXLWBook = oXL.Workbooks.Add
Set oXLWorkSheet = oXLWBook.Worksheets(1)
rs.Open "Select * From tblSalaries", CurrentProject.Connection

oXLWorkSheet.Range("A1").CopyFromRecordset rs

You can continue to manipulate the Excel data from Access using similar code to the code you would use in Excel, except that you
will need to explicitly reference Excel as in the above code.