Try Dev Ashih's web page for an example of this:
http://www.*-*-*.com/ ~dashish/

Can someone shed some light as to how I can activate an Excel Macro
while running a module in Access.  Basically this is what the module
does:
It generates a series of data.  The module then transfer the data and
place the the data and plots a graph in an Excel Worksheet (The
Worksheet is like a template).  What I would like to do is to copy the
worksheet and paste the info on a new worksheet either within the same
or a new workbook - as many of the same graph & data need to be
generate for different items.  I worte the macro in the workbook, i.e.
copy, insert, & paste, but I don't know how to activate it from the
Access Module.  I've tried DoCmd.RunMacro; DoCmd.RunApp; etc but they
do not seem to work.

Advice, syntex,  or insight will be much appreciated.  Thanks in
advance.

In these situations I have found it most useful to exploit Access
Automation Objects.
You can display methods, commands, properties etc. of the Excel OCX by
using the Object browser from any code screen. Use the "Reference" option
to link the Excel library and you are off to the races.

Without getting into it too much, use CreateObject to create the Excel
object and
then you can use Excel's OCX to your heart's content.

Casey

Hi,

The code first.

'************* Code Start ****************
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.

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

I would suggest two improvements  to the code Dev posted.

1. Declare the Excel object as early bound, by
        (Step 1) a reference to the Excel object library (use
Tools--References while in amodule)
        (Step 2) replacing objXL as Object by objExcel as Excel.Application

2. Precede the Set objExcel =Nothing line with objExcel.Quit .  Otherwise,
you will leave instances of Excel open AND might have trouble closing down
Access (at least in Office97).

Cordially,
                Sanjay

<snip>

Hi Sanjay,

Good suggestions.  However, in this case

(1)  Late binding is just my preference for automation.
(2)  Can't use objExcel.Quit because the macro I'm running calls
GetSaveFileName in the end, hence Excel might still have the Dialog box
open.  Using Nothing works fine in this case AFAIK.

Thanks
Dev

--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------