Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Run macro from an active workbook on another workbook

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run macro from an active workbook on another workbook

    Hi I have the following code. I'm trying to open a workbook (e.g. WorkWithExFile2) from current active workbook (e.g. WorkWithExFile) and run a procedure/macro that I have in active workbook (WorkWithExFile) on the workbook that I brought up (WorkWithExFile2) which was then saved as (test.xlsm)

    I'm not sure this is the way to do it. I just search online and trying to mess with the code but I got an error message saying that: "Compile error: Expected Function or variable"

    Code:
    Sub Open_External_Workbook()
    
    
    Dim open_Workbook As Workbook
    Dim wb As Workbook
    
    
    Workbooks.Open Filename:="C:\Users\Desktop\WorkWithExFile2.xlsm"
    
    
    Set open_Workbook = Application.Workbooks.Open _
    ("C:\Users\Desktop\WorkWithExFile2.xlsm")
    
    
    open_Workbook.SaveAs ("C:\Users\Desktop\test.xlsm")
    
    
    Set wb = Workbooks.Open("C:\Users\Desktop\test.xlsm")
    
    
    Application.Run wb.Name & "!" & proc_test
    
    
    End Sub
    
    
    Sub proc_test()
    
    
    Worksheets("Sheet1").Range("A1").Value = "Ran proc_test"
    
    
    
    
    End Sub
    Wonder if someone could point out the error or the correct way to do it?

    Thank you!

  2. #2
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    652
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run macro from an active workbook on another workbook

    Try to place breakpoints at each line please and watch which statement throws the error exactly.
    Last edited by Flashbond; Nov 19th, 2017 at 11:55 PM.

  3. #3
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,189
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run macro from an active workbook on another workbook

    Your path (C:\Users\Desktop) should be something like "C:\Users\Keith\Desktop '<---- Change Keith to your name.

    If you want to find out the path to your desktop, try this code from snb.

    Code:
    Sub snb_Get_Desktop_Path()
        MsgBox CreateObject("wscript.shell").specialfolders(4)
    End Sub
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,121
    Post Thanks / Like
    Mentioned
    211 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Run macro from an active workbook on another workbook

    If proc_test is in WorkWithExFile try
    Code:
    Sub Open_External_Workbook()
    
    
    Dim open_Workbook As Workbook
    Dim wb As Workbook
    
    Set open_Workbook = Workbooks.Open("C:\Users\Desktop\WorkWithExFile2.xlsm")
    
    
    open_Workbook.SaveAs ("C:\Users\Desktop\test.xlsm")
    
    
    Set wb = Workbooks.Open("C:\Users\Desktop\test.xlsm")
    
    
    Call proc_test
    
    
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •