Calling a Macro from another workbook

[Ricky Morris]

What's the syntax to call a macro in another workbook?

Thanks,
Ricardo

 

[Johnny Thunder]

I am running into a problem where I have defined the Workbook as "Destbook" and am trying to call it but getting an error?

The code is here

 Dim Destbook As Workbook
    Dim varCellvalue As Variant
    Dim sNameFound As String
 
       Const sPath As String = "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\"

        varCellvalue = Sheets("Main").Range("B24").Value
        
    On Error Resume Next
    Set Destbook = Workbooks(varCellvalue & ".xls")
    If Err.Number <> 0 Then _
        Set Destbook = Workbooks(varCellvalue & ".xlsm")
    On Error GoTo 0
    
    If Destbook Is Nothing Then
        sNameFound = Dir(sPath & varCellvalue & ".xls*")
        If sNameFound = vbNullString Then
            MsgBox "No files found matching: " & sPath & varCellvalue & ".xls*"
            Exit Sub
        Else
            Set Destbook = Workbooks.Open(sPath & sNameFound)
       End If
   
   Else
   
   End If
   
    Destbook.Activate
      
       Sheets("ODM").Select
    
    Application.Run ("'" & Destbook & "'!macro1")

Any ideas why this isn't working? My macro name is Macro1 and Destbook returns the full file name so I am stuck.


My Error.

Error: 438 Does not support this property or method.

 

[ZVI]

     Application.Run ("'" & Destbook & "'!macro1")

Any ideas why this isn't working? My macro name is Macro1 and Destbook returns the full file name so I am stuck.
My Error. Error: 438 Does not support this property or method.

Try this instead: Application.Run "'" & Destbook.Name & "'!Macro1"

 

[Johnny Thunder]

Try this instead: Application.Run "'" & Destbook.Name & "'!Macro1"

It worked out perfectly.

Thank you for your help. May I ask what the ".Name" refers too? Is this a way to tell VBA that Destbook is a defined Name?

 

[ZVI]

Glad it helped!

In your code Destbook is workbook object.
But you’ve missed its declaration: Dim Destbook As Workbook
That object has some properties including the Name property.
Destbook.Name means the name of workbook object.

For more details try this code and follow the instruction in the comments:

Sub Test()
 
  Dim Destbook As Workbook
  Set Destbook = ActiveWorkbook
 
  ' In new code line type:    Destbook
  ' then type the dot symbol: Destbook.
  ' After typing the dot symbol the list of workbook's properties and methods will appear.
  ' Choose the Name property: Destbook.Name
  ' and press F1 to see the help of that (selected) property
   
  MsgBox "Destbook.Name = " & Destbook.Name
 
End Sub

 

[Johnny Thunder]

Perfect. I appreciate the explanation on this.

 

[Johnny Thunder]

any ideas why this is breaking now that the new code has been applied?

Getting a 'Runtime Error:1004 Application-Defined or Object-Defined Error'


Your help has made my project come to life so I appreciated it greatly. Thanks

Sub ODM_Transfer()

  
    Dim Destbook As Workbook
    Dim varCellvalue As Variant
    Dim sNameFound As String
 
       Const sPath As String = "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\"

        varCellvalue = Sheets("Main").Range("B24").Value
        
    On Error Resume Next
    Set Destbook = Workbooks(varCellvalue & ".xls")
    If Err.Number <> 0 Then _
        Set Destbook = Workbooks(varCellvalue & ".xlsm")
    On Error GoTo 0
    
    If Destbook Is Nothing Then
        sNameFound = Dir(sPath & varCellvalue & ".xls*")
        If sNameFound = vbNullString Then
            MsgBox "No files found matching: " & sPath & varCellvalue & ".xls*"
            Exit Sub
        Else
            Set Destbook = Workbooks.Open(sPath & sNameFound)
       End If
   
   Else
   
   End If
   
    Destbook.Activate
    
    Sheets("ODM").Select
      
    On Error Resume Next
    
    Application.Run "'" & Destbook.Name & "'!Macro1"
    
    If Err.Number <> 0 Then _
           
           Err.Clear
           
           Else
           Sheets("ODM").Select
             Range("H11").Select
               Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
          End If
                
        ThisWorkbook.Activate
          Sheets("ODM").Select
          Range("B14:H14").Select
            Range(Selection, Selection.End(xlDown)).Select
              Selection.Copy
     
     Destbook.Activate
     
        Sheets("ODM").Select
        Range("C14").Select
          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
     ThisWorkbook.Activate
     
         Sheets("ODM").Select
         Range("N14:O14").Select
          Range(Selection, Selection.End(xlDown)).Select
            Application.CutCopyMode = False
              Selection.Copy
     
     Destbook.Activate
          Range("J14").Select
           Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False

Application.CutCopyMode = False


    End Sub

 

[ZVI]

Getting a 'Runtime Error:1004 Application-Defined or Object-Defined Error'

     If Err.Number <> 0 Then _

At least the underscore symbol after Then is a typo - delete it and try again.

 

[Johnny Thunder]

Same Error after correcting the typo.

So close to finishing this beast.

 

[ZVI]

After On Error Resume Next you can't get the error message.

To understand the logic, please explain what range are you trying to select by these two lines of the code:
Range("B14:H14").Select
Range(Selection, Selection.End(xlDown)).Select

 

[Johnny Thunder]

After On Error Resume Next you can't get the error message.

To understand the logic, please explain what range are you trying to select by these two lines of the code:
Range("B14:H14").Select
Range(Selection, Selection.End(xlDown)).Select

On my source sheet Range("B14:H14").Select was what the Macro recoreder brought when I used that to get my Copy criteria.

I used to have the code Range("B14:H & Lastrow").Select but it seemed to not work sometimes due to copying data from a table.