Re: Run Excel macro from Access?

[Richard Choate]

You can run an Excel macro from Access, but you may get unexpected results,
especially if the macro contains loops. I tried this and was dissatisfied
with the performance of the macro. If I ran the macro in Excel, it worked
great, but when I ran it from Access after sending data to the Excel file,
it sucked. I ended up doing all of the Excel manipulation right there in my
Access code, and it works fine. You just have to write your code carefully
and set references to Excel in your Access VBA Editor. Here is a snippet
from my code:

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object

' Create an instance of Excel and open a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb =
xlApp.workbooks.Open("C:\Dir1\SubDir1\SubSubDir1\FileName.xls")
Set xlWs = xlWb.Worksheets("Raw Data")

' Give user control of Excel's lifetime
xlApp.UserControl = True

xlWs.Activate
xlApp.ActiveCell.Select
xlApp.ActiveCell.CurrentRegion.Select
xlApp.activeworkbook.Names.Add Name:="AllPay",
RefersTo:=xlApp.ActiveCell.CurrentRegion

On Error Resume Next

xlWs.Activate
xlApp.Range("A1").Activate
xlApp.ActiveCell.Select
xlApp.Range(xlApp.ActiveCell.End(xlDown).Offset(0, 17),
xlApp.ActiveCell.Offset(0, 17)).Select

xlApp.Selection.Replace What:="", Replacement:="Salary", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=False

' xlApp.Run "SetData()"

Set xlWs = xlWb.Worksheets("Report")
xlWs.Activate
xlApp.Range("A1").Select

xlApp.activeworkbook.Save

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
xlApp.Quit
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing




Hi,

I have been outputting an Access query to Excel in my
form's code:

DoCmd.OutputTo acOutputQuery, "vqryRecordSource",
acFormatXLS, "O:\0306 - June Data\CD\" & Me!
[lstReportClient] & ".xls"

Now I need to make some modifications to the outputed
spreadsheet, such as inserting/deleting some columns,
coloring some columns etc.

Can I make these modifications in my Access form? I could
create an Excel macro containing these modifications, but
I'd like to execute this macro immediately after the
output and before the file is finally saved.

The point is to do all this in the same button-click on my
Access form -- without opening the file in Excel and
running the macro there.

Thanks!

Regards,
Rajat