Call Excel subroutine with a parameter from Access

[Sck]

I am trying to run an Excel VBA procedure from Access and can't figure out how to send the required parameter. This works for running one that does not require a parameter:

xlApp.Run sFileName & "!" & sMacroName

this is the subroutine Sub FormatDollars(MonthEnd As String)

this is what I tried:
xlApp.Run sFileName & "!" & sMacroName & ", '" & CStr(MEnd) & "'"
and
xlApp.Run sFileName & "!" & sMacroName & ", " CStr(MEnd)



Access breaks on this line. Error 1004, Cannot run the macro 'FormatFile.xlsm!FormatDollars, 201412' this macro may not be available.....
if I am right, it is reading the parameter as part of the macro name and not a parameter.

any help is appreciated.

[June7]

Should work https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

This works for me:

Sub test()
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("C:\Temp\Book1.xlsm")
oExcel.Run "Test", "This is a test"
oExcel.Visible = True
Set oBook = Nothing
Set oExcel = Nothing
End Sub

[Sck]

Thanks, I got it to work by hardcoding the macro name. It is not the best option but it works for this application.

[June7]

My code did not include the file name in the Run method.

I just tested using a variable for the procedure name argument. That also works.