Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Modules: Run Excel Macros through Automation

Author(s)
Dev Ashish

The code first.

'************* Code Start ****************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Sub sRunCARMa()
Dim objXL As Object, x
    On Error Resume Next
    Set objXL = CreateObject("Excel.Application")
    With objXL.Application
        .Visible = True
        'Open the Workbook
        .Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
        'Include CARMA in menu, run AutoOpen
        .ActiveWorkbook.RunAutoMacros xlAutoOpen
        x = .Run("AccountsViewEngine", 0)
    End With
    Set objXL = Nothing
End Sub
'************* Code End ****************

        As you can see, I'm using CreateObject to start an instance of Excel. You can however, use fIsAppRunning function to decide between CreateObject or GetObject.

        CreateObject by defaults starts a new instance of the application in hidden mode. Hence you need to set the Application object to Visible.

        After opening the workbook in which the macro is present, use the RunAutoMacros method if you have any AutoOpen macro that you wish to run.

        Note that in order for Access to recognize xlAutoOpen constant, you need to reference Excel Object Library.

        Then simply use the Application.Run method to run any macros. Note that the syntax I've used is

                Run(Macro, Arg1, Arg2, ...)

         as the function AccountsViewEngine is expecting a Boolean argument which the zero satisfies.


© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer