Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: Running a series of Sub as a batch job ?

    Hi,

    I've got 3 VBA Subs in Excel.

    c:\file\file01.xls sub job01

    c:\file\file02.xls sub job02

    c:\file\file03.xls sub job03

    How do I combine them and where so that they run one after the other ?

    H.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy.
    Code:
    Sub ComboSubs()
      Run job01
      Run job02
      Run job03
    End Sub
    Adjust as needed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2008
    Posts
    7

    Thanks

    Hi,

    You've answere several of my questions recently and I appreciate that.


    1/ How will VBA know where to find job01, 02, 03. ?

    2/ And do I write a seperate marco for the code you supplied and again where would it live?

    H.

  4. #4
    Join Date
    Mar 2008
    Posts
    7

    Ok

    Thanks I tried to load the code you suggest.

    VBA can't find the subs to run.

    H.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    It's best to specify the File name and Procedure in the Run command. This is an example of how to run successive procedures located in multiple files. In the example I use a function to test if the file is already open prior to calling the procedure. If the file is not open you open it prior to calling the Run command.

    Code:
    Sub OpenRunFiles()
        Dim sFolder As String
        Dim sFileName As String
        Dim sProcName As String
        
        ' Set path of File Location
        sFolder = ThisWorkbook.Path & "\Files\"
        
        ' Run a Loop
        For i = 1 To 3
          ' Set the file and Procedure Name to Variables
          sFileName = "File0" & i
          sProcName = "Job0" & i
          
          ' Check if Files are open
          If WorkbookIsOpen(sFileName) Then
          Else
            ' Open file if not already open
             Workbooks.Open Filename:=sFolder & sFileName, _
              ReadOnly:=True, Notify:=False
          End If
          ' Call the Procedure in the specified file
         Application.Run Macro:=sFileName & "!" & sProcName
        Next
    End Sub
    
    
    '
    Function WorkbookIsOpen(wbname) As Boolean
    '   Returns TRUE if the workbook is open    
    Dim x As Workbook
        On Error Resume Next
        Set x = Workbooks(wbname)
        If Err = 0 Then WorkbookIsOpen = True _
            Else WorkbookIsOpen = False
    End Function
    ~

    Bill

  6. #6
    Join Date
    Mar 2008
    Posts
    7

    Hi Bill

    Bill thanks for the code.

    Could you help me with this bit of hard code.

    There are only three programmes/ marcos so can hard code it.

    Macro is FinderI
    File is FinderI.xlsm
    Folder is C:\Total Programme\

    How do I combine them ?

    Workbooks.Open Filename:="C:\Total Programme\Finder I.xlsm"
    Windows("Finder I.xlsm").Activate
    Application.Run macro:="Finder I.xlms", FinderI

    ?

    This doesn't work.

    H.

  7. #7
    Join Date
    Feb 2004
    Posts
    533
    You are having an error on the 'Run' Method? There must be a exclamation character between the workbook name and the Proc name. The correct syntax for a Function Call is:

    mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum")

    Or for a Procedure:

    Application.Run "MYCUSTOM.XLM!My_Func_Sum"

    In your case you know the Workbook name and the Proc name so you do not need to use a variable if you don't want to. The workbook containing the Proc to run does not need to be active so there is no need to use Activate Workbook.

    Workbooks.Open Filename:="C:\Total Programme\Finder I.xlsm"
    Application.Run macro:="Finder I.xlms!FinderI"


    This code will open the workbook and run 'FinderI' procedure providing you have the correct path, workbook name, procedure. A problem can occur if the workbook is already open and you re-open it. I usually check if the Workbook is Open prior to attempting to open the workbook to avoid the error.

    Excel VBA Help is really good to check things like this, Syntax and Parameter Qs. In the Excel VBE Just place your cursor on the command, the word "Run" in this case, and press the function key {F1}
    ~

    Bill

  8. #8
    Join Date
    Jan 2008
    Posts
    19
    Not really much to add to bill's suggestion,but I did notice this:

    Windows("Finder I.xlsm").Activate
    This is bad practice: try to avoid using anything that selects or gives/requires focus to a specific object. In this case you are activating a excel window specifically, why would you need to?

    Ofcourse, it could be that the code inside the excelsheet demands focus because the creator also used the "bad practice" of using selections etc. then you have no choice. But otherwise it should be avoided as much as possible.

    What I mean to say is that if the code does not run properly, look in this direction and you are likely to find a cause of your issue. Imagine what would happen when your user would suddenly change focus?

    In case you are curious of how to work with objects outside the excel file you are starting in (or anyone who searched for an answer and wound up here), there is a better practice (in my opinion). Rewriting bill's code:

    Code:
    Sub OpenRunFiles()
       Dim sFileName as string
       ' This is the Workbook object which we will link to the file you want to use
       Dim TempWorkbook as Workbook
    
       ' sFileName contains the full path to the excel workbook 
       sFileName = C:\Total Programme\FinderI.xlsm
    
          ' Check if Files are open
          If WorkbookIsOpen(sFileName) Then
          Else
             ' Open file if not already open, you could hide it from view if you like, 
             ' just look inside the help file
             Set TempWorkbook = Workbooks.Open (Filename:=sFileName, ReadOnly:=True, Notify:=False)
          End If
          
          ' Now you can use the workbook directly, without using any referrals to 
          ' the file itself. So: calling your routine would be:
     
          TempWorkbook.FinderI
          
          ' if FinderI is a function that returns a value, you can use the following 
          ' to extract it: 
    
          Dim Number as Double
          Number = TempWorkbook.FinderI

Posting Permissions

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