Running a Excel sheet macro from Access form button

[haplc]

Hello!

I have an excel sheet under d: \temp\test.xls with a macro named "makro1". This is a makro available in "all sheets". Now i have an Access form where I have a button with action I want to run MAKRO1 in excel sheet. I have used following scripttaken and modified from Mr. Excel--thanks)

Private Sub Befehl13_Click()

Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.Workbooks.Open("D:\temp\test.xls")
Rem xls.Run ("Makro1.Module3")(i have tried("Module3.Makro1"(
wrk.Close False
xls.Quit
Set xls = Nothing
End Sub

But from Access when I clicl the button, I have message: makro1 not found.

Also is it possible that after running the macro, the excel sheet may remain open??
thanks

 

[mdmilner]

This is what you're looking for:

http://www.mvps.org/access/modules/mdl0007.htm

Here's the code segment. There's more description over there that's important. Yell if you need further explanation.

'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