Cannot pass arguments when calling macro in excel

[kevinhh]

When I try to pass arguments to a macro which can accept arguments , it will not accept them under any circumstances , it runs fine when I don't try to pass any arguments . Is there anyway that I can pass arguments to a macro in an Excel OLE object .

Set objXL = CreateObject("Excel.Application&quot
With objXL.Application
.Visible = True
.ScreenUpdating = False
.Workbooks.Open prepfile
.Run (macroname)
'This will not work
'.Run (macroname,upl_filename)
.Quit
End With
Set objXL = Nothing

 

[chell]

Hello Kevin;

I assume that you are running Excel 95 or Excel 97 and that the macro is not an Excel 4 macro but a VBA function. If so, this ought to work for you:

Set objXL = CreateObject("Excel.Application&quot
With objXL.Application
.Visible = True
.ScreenUpdating = False
.Workbooks.Open prepfile
End With

'This might work
call objXL.Application.macro(arg1)

'However the macro name might have to be qualified...
call objXL.Application.sheets(SheetName).macro(arg1)

'Or if the function is on "ThisWorkbook"
call objXL.Application.workbooks("Filename&quot.macro(arg1)

'Or if it is in a module
call objXL.Application.modules("ModuleName&quot.macro(arg1)


objXL.Application.quit
Set objXL = Nothing

Good Luck;
Chell