Running a Function or macro of child file from a Master file

[ravinder_tig]

Hi Guys
I do need your help regarding one of my projects in which I ‘m trying out to run a function of a closed file from and active file In this case I do have 2 files Master and process
In both there’s a combo box control which help me revolve the data on basis of month As the case when combo box of process file is changed data is being revolved according to month
What I need to do is
When I change the combo box of MASTER file the change should reflect in the process file As function runs updates the process files value and file saved and closed
Here’s the code for master file which I’m using to run function MB of process file

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 String
   Dim rng As String
   Dim Num As Integer
   Dim CloseIt As Boolean
   Dim i As Integer
   Dim str As Variant
   Num = Sheets(2).Range("A14")
   '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
   
   wbTarget = "Process Wise Test1.xls"
   'rng   Range("b8") = ThisWorkbook.Path
 '  If Err.Number <> 0 Then
   'Open the workbook
  '    Err.Clear
    Workbooks.Open (ThisWorkbook.Path & "\" & wbTarget)
      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!" _
                    & vbNewLine & ThisWorkbook.Path & "\" & wbTarget
      Exit Sub
   End If
   On Error GoTo 0
   'Run the function, and give user the results
   str = Application.Run(wbTarget & "!" & "MB", Num)
   If CloseIt = True Then
   'If the target workbook was opened by the macro, close it
   '   wbTarget.Close savechanges:=True
   Else
      'If the target workbook was already open, reactivate this workbook
      ThisWorkbook.Activate
   End If
End Sub

Here’s da code for process file

Option Explicit
Sub MBmacro1()
Dim str As String
str = MB(0)
MsgBox (str)
End Sub
Function MB(num As Integer) As String
   Dim myCol As Integer, c As Integer
   Dim myColLett As String
   Dim myRow As Long, r As Long
   Dim sourceSheetName As String
   Dim monthNumber As Integer
   Dim startRow As Long, endRow As Long
   Dim myFormula As String
   Dim myResult As Double
   Dim myMonthName As String
   Dim colCaption As String
   Dim found As Variant
   Dim i As Long
   Dim swOk As Boolean
   Application.ScreenUpdating = 0
   With ThisWorkbook.Sheets("Summary")
   If num = 0 Then
   monthNumber = .Range("a14")
   Else
   monthNumber = num
   End If
     myMonthName = MonthName(monthNumber)
   
     For r = 6 To 8
         sourceSheetName = .Cells(r, "c")
         For c = 4 To 9
            colCaption = .Cells(5, c)
         
            swOk = True
            'find monthName in data
            With ThisWorkbook.Sheets(sourceSheetName)
               Set found = .Range("a:a").Find(myMonthName, LookIn:=xlValues)
               If Not found Is Nothing Then
                  'find start and end row for month data
                  startRow = found.Row
                  For i = startRow To startRow + 10
                     If .Cells(i + 1, "a") <> "" Or .Cells(i + 1, "b") = "" Then
                        endRow = i
                        Exit For
                     End If
                  Next i
               Else
                  swOk = False
               End If
            
               'find column caption
               Set found = .Range("2:2").Find(colCaption, LookIn:=xlValues)
               If Not found Is Nothing Then
                  myCol = found.Column
               Else
                  swOk = False
               End If
            End With
            
            If swOk Then
               'convert column number to letter:
               myColLett = Split(Columns(myCol).Address(False, False), ":")(0)
               myFormula = "average(" & sourceSheetName & "!" _
                        & myColLett & startRow & ":" _
                        & myColLett & endRow & ")" _
               'if you want to put formula in sheet you can use code:
               '.Cells(r, c).Formula = "=" & myFormula
               
               'if you prefer to use macro you can do:
               If Not IsError(Evaluate(myFormula)) Then
                  'calculate formula value
                  myResult = Evaluate(myFormula)
                  .Cells(r, c) = myResult
               Else
                  .Cells(r, c) = "  - -  "
               End If
            Else
               .Cells(r, c) = "  - -  "
            End If
         Next c
      Next r
      Application.ScreenUpdating = 1
   End With
   MB = "done"
End Function

When I run the master file code with different file and functions it runs fine but
For this process file it generates an error Error no 1004 Macro couldn’t found “filename!function name”
Plz help me with this debugging I’m really tired up trying to sort this issue
I’d be really thankful
Regards
Ravinder S

[ravinder_tig]

i' had debugged the application and find out the problem when ever in master i suppy the file name as a string and if file name do hav spaces in between then it dosen't consider this as a [VBA]str = Application.Run(wbTarget & "!" & "MB", Num)[/VBA]

if i initialize wbtarget as workbook it could be done but i don't know how to set wbtarget with that file name could u plz help me with this issue

Regards,

Ravinder S