Running a function of another file

[ravinder_tig]

Hi All
I'm Working on a project which do require to run a function of another child Workbook in the same directory
and i had achived that also
But i do need your help regarding one of my problem with the coding part
i'm using Application.run(Filename!Function name, arg list) command for my use
in above command when i use the child file without spaces it works jst fine
but when file name like eg "Child File" is containing spaces with it a error
appears which states
Error No. 1004
Macro Not Found Filename!function name dosen't exists
i'm attaching both file apprunexaple.xls with this code and
child file as app run data.xls

if i use wild card characters would that work? Eg aap*run*example.xls

Code for Apprunexample is

Option Explicit
Sub RunMacro_WithArgs()
'Macro purpose: To demonstrate using the run method to execute
'a function or macro (with arguments) from another workbook
Dim wbTarget As Workbook, _
Number1 As Long, Number2 As Long, _
Mynum As Variant, _
CloseIt As Boolean

'Get values from user
Number1 = 2
Number2 = 3

'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = ThisWorkbook.Sheets(1).Name

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & ThisWorkbook.Sheets(1).Name & ".xls")
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!"
Exit Sub
End If
On Error GoTo 0

'Run the function, and give user the results
Mynum = Application.Run(wbTarget.Name & "!EasyMath", Number1, Number2)
MsgBox Number1 & "+" & Number2 & "=" & Mynum
If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
End Sub

Plz help me with this
I'll Be really gr8ful
Regards,
Ravinder S

[GTO]

Greetings,

I cannot find the nifty article I found related to this to save my life :-( Anyways, the workbook name / macro name part gets picky.

This seems to work fine:


Sub RunMacro_WithArgs()
'Macro purpose: To demonstrate using the run method to execute
'a function or macro (with arguments) from another workbook
Dim wbTarget As Workbook, _
Number1 As Long, Number2 As Long, _
Mynum As Variant, _
CloseIt As Boolean

'Get values from user
Number1 = 2
Number2 = 3

'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = ThisWorkbook.Sheets(1).Name

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & _
ThisWorkbook.Sheets(1).Name & ".xls")
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!"
Exit Sub
End If
On Error GoTo 0

'//*****************************************
'// Build the string with the single quote marks
'//*****************************************
Dim s As String
s = wbTarget.Name
s = "'" & s & "'!EasyMath"

Mynum = Application.Run(s, Number1, Number2)
MsgBox Number1 & "+" & Number2 & "=" & Mynum
If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
End Sub


Hope that helps,

Mark

[ravinder_tig]

Thanks a million mark it works wonders

Thanks a lot

[GTO]

Happy to help :thumb

If Solved, could you mark the thread? There's an option under Thread Tools right above your first post. This saves others from checking unnecessarily.

Thanks,

Mark