PDA

View Full Version : Running a Function or macro of child file from a Master file



ravinder_tig
05-31-2009, 10:23 PM
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
06-01-2009, 08:21 PM
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 str = Application.Run(wbTarget & "!" & "MB", Num)

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