Running a series of Sub as a batch job ?

Yikkes · #1 (**permalink**) 03-20-08, 18:08

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.

shades · #2 (**permalink**) 03-21-08, 11:22

Howdy.

Code:

Sub ComboSubs()
  Run job01
  Run job02
  Run job03
End Sub

Adjust as needed.

Yikkes · #3 (**permalink**) 03-21-08, 12:06

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.

Yikkes · #4 (**permalink**) 03-21-08, 14:18

Thanks I tried to load the code you suggest.

VBA can't find the subs to run.

H.

savbill · #5 (**permalink**) 03-21-08, 20:54

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****n 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

Yikkes · #6 (**permalink**) 03-23-08, 07:23

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****n macro:="Finder I.xlms", FinderI

?

This doesn't work.

H.

savbill · #7 (**permalink**) 03-23-08, 15:12

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****n("MYCUSTOM.XLM!My_Func_Sum")

Or for a Procedure:

Application****n "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****n 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}

jf0rce · #8 (**permalink**) 04-10-08, 05:55

Not really much to add to bill's suggestion,but I did notice this:

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech