Calling Procedures in Another Workbook and Passing Variables

[ExcelKid_10]

Hello-

I have a VBA Routine where I open workbook and run some code in the newly opened workbook that requires two input parameters...I've been trying to pass them ByVal however the debugger now does not recognize the Call to the procedure if I include the variables...anybody have any ideas on how I remedy this?

Here is my code:

Function OpenExcelPerfAttributionDataFile()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWS As Worksheet
Dim strSheet, strFileName As String
Dim strBegDate, strEndDate, xDate As String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

strEndDate = DMax("[tblDates]![Report_Date]", "tblDates")
xDate = DateSerial(Year(strEndDate), Month(strEndDate) - 1, Day(strEndDate))
strBegDate = Format(xDate, "MM/DD/YYYY")


strFileName = "PerformTotalReturnLoader.xls"
sFile = "\\faffsprd\fs31_fite$\Quant\Reporting Database And Directions\" & strFileName

Set xlWb = xlApp.Workbooks.Open(sFile, True)

'*****Code crashes on this line....
sMacro = "UpdatePerformanceLoader(" & strBegDate & "," & strEndDate & ")"
'
xlApp.Run sMacro

'xlWb.Close (True) '<saves it

xlApp.Quit

Set xlWS = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

End Function

Thanks!

EK

 

[xenou]

I guess something like this would work:

xlApp.Run PerformTotalReturnLoader.xls!UpdatePerformanceLoader(strBeginDate,strEndDate)

Or keeping the workbook name in a variable, maybe like (not tested though):

xlApp.Run Workbooks(strFileName)!UpdatePerformanceLoader(strBeginDate,strEndDate)

 

[xenou]

Scratch that. All baloney.
I think you just need to add the workbook name:

sMacro = [B][COLOR="RoyalBlue"]strFileName & "!" & [/COLOR][/B]"UpdatePerformanceLoader(" & strBegDate & "," & strEndDate & ")"

Does that work?

 

[RoryA]

xlApp.Run "'" & wb.Name & "'!UpdatePerformanceLoader", strBegDate, strEndDate

 

[ExcelKid_10]

sorry for the late reply! thank you very much!