I am having difficulty sending arguments while calling a
procedure in another application. I can run the
reflections_macro in the Reflections application by using
this command:

R2WINObject.RunMacro "reflections_macro"

When Excel calls this macro, it asks the user to input a
starting and ending date. Instead, I want to streamline
this macro by eliminating the need for user input and
have Excel send the start and end dates, but this
modified command does not work:

R2WINObject.RunMacro "reflections_macro", start_date,
end_date

I have tried numerous variations of the line above to get
it to work but to no avail. Could someone suggest the
proper code? I know it must be something easy, but I am
a novice. Thanks in advance!

More complete code:

Sub Run_Reflections_from_Excel()
Dim R2WINObject As Object
Dim xlApp As Object
Dim start_date As Date
Dim end_date As Date

startdate = Range("start_date").Value
enddate = Range("end_date").Value
Set xlApp = ActiveWorkbook

Set R2WINObject = GetObject("R2WIN")
R2WINObject.Visible = True
R2WINObject.RunMacro "reflections_macro", startdate,
enddate

xlApp.Application.Visible = True
End Sub


In Reflections:
Sub reflections_macro(ByVal startdate, ByVal enddate)
Dim startdate As Date
Dim enddate As Date

num_of_months = DateDiff("m", startdate, enddate)

....etc...
End Sub

For :R2WINObject.RunMacro "reflections_macro", start_date,
end_date
You might try :
R2WINObject.Run Macro:="reflections_macro", Arg1:=start_date, Arg2:=end_date

Terrance Rebello

"msk19" wrote:

I am having difficulty sending arguments while calling a
procedure in another application. I can run the
reflections_macro in the Reflections application by using
this command:

R2WINObject.RunMacro "reflections_macro"

When Excel calls this macro, it asks the user to input a
starting and ending date. Instead, I want to streamline
this macro by eliminating the need for user input and
have Excel send the start and end dates, but this
modified command does not work:

R2WINObject.RunMacro "reflections_macro", start_date,
end_date

I have tried numerous variations of the line above to get
it to work but to no avail. Could someone suggest the
proper code? I know it must be something easy, but I am
a novice. Thanks in advance!

More complete code:

Sub Run_Reflections_from_Excel()
Dim R2WINObject As Object
Dim xlApp As Object
Dim start_date As Date
Dim end_date As Date

startdate = Range("start_date").Value
enddate = Range("end_date").Value
Set xlApp = ActiveWorkbook

Set R2WINObject = GetObject("R2WIN")
R2WINObject.Visible = True
R2WINObject.RunMacro "reflections_macro", startdate,
enddate

xlApp.Application.Visible = True
End Sub


In Reflections:
Sub reflections_macro(ByVal startdate, ByVal enddate)
Dim startdate As Date
Dim enddate As Date

num_of_months = DateDiff("m", startdate, enddate)

....etc...
End Sub