AutoBatcher 32 bit

OfficeBatcher 32 bit

OfficeBatcher is a handy tool that executes programs (batch jobs, queries, printouts) at predefined times. What and when, is
customized in a batch script. OfficeBatcher may be used to execute all kinds of programs. It may trigger
a macro in e.g MSWord/Excel/Access, or you can get it to trigger a DOS batch job (from Windows). For the time being it is most advanced
related to MSAccess because I've made it possible to pass parameter values to queries/reports.

I wrote this program when I needed to execute a batch job in MSAccess that updated some local tables from a remote ODBC attached Database. These batch jobs should be triggered at certain days and hours. In addition it was useful to printout some reports so the users could just pick them up at the printer at some mornings.

I wrote a general program that may be used to execute all kinds of programs. It can be used to trigger a macro in e.g MSWord or to trigger a DOS batch job (from Windows). But for the time being it is most advanced related to MSAccess because I've made it possible to pass parameter values to queries/reports.

Batch Scripts

It's pretty easy to set up OfficeBatcher to trigger programs or macros. You do this with OfficeBatcher Script (in OfficeBatcher.ini). You may program your scripts with a texteditor like Notepad or with the Script editor in OfficeBatcher, which is started from the Tools menu of OfficeBatcher (To see this menu you have to right-click the red ball icon in the systray and choose Restore). The batch script editor is easy to use and contains syntax debugging (see Debug under the Script File menu). The Batch script looks like this:

[Batch NR]

Each batch job is defined under separate [Batch NR] sections in OfficeBatcher.ini. The first batch job starts with 1 like this [Batch 1], next job is defined under section [Batch 2] etc. Remember that the numbers have to be increasing.

Type is telling which program that is going to execute the macro. Use DOS as type if you just want to start a program.

PathAndDB is telling which file that holds the macro to be executed. Supported macros is MSWord macros, Excel macros and MSAccess macros.

ExecuteMacro=Macro name

The name of the macro to be executed.

ExecuteDay 1=Day number

Day when the macro is to be executed. Decide together with StartTime when the batch job is to be executed. One batch section/job may only be executed automatic once per day (but you may have several sections for the same day). You can use several ExecuteDay values in one section if the batch job is to run several days. Remember that the numbers have to be increasing like this (ExecuteDay 1 means Sunday):

ExecuteDay 1=2

ExecuteDay 2=5

StartTime=7:55

Time when the macro is to be executed. If current time is greater than StartTime when OfficeBatcher is started, the macro is executed.

LastBatch= 4

When a batch job is executed LastBatch is set to ExecuteDay. It's a control to ensure that one batch/section is only executed once per day. If you need to execute the same batch job several times a day, You have to use several batch sections that is equal except the StartTime.

PropertyForm=1

Applies only to MSAccess macros. You may use this functionality when You want to trigger parameter queries/reports in your MSAccess macro, and want to pass the parameter value from the batch script. To get it work you have to have a form named frmProperty in your mdb file. And You have to use text boxes on this form named txtProperty1, txtProperty2 , txtProperty3 (incremented for each Property value you want to pass. Currently tree Property values supported in each batch section.). You use this parameters like this in your MSAccess queries: Where [Forms]![frmProperty].[txtProperty1]= [A value]

PropertyForm=1 tells that you use this method.

PropertyValue 1 = any Value e.g. 1998

PropertyValue 1 is the parameter value You pass to txtProperty1 related to your MSAccess queries (see above).

PropertyValue 2 = any Value e.g. i1998

PropertyValue 2 is the parameter value You pass to txtProperty2 related to your MSAccess queries (see above).

Remark: if you use the letter "i" in front of your numeric parameter value, like this: PropertyValue 2 = i1998, the value is incremented to i1999 in your batch section after execution. I build this functionality when I used week number as a parameter value that had to be incremented the next week the query should be executed.

EXAMPLE of a batch script:

[Batch 1]
Type=MSAccess
PathAndDB=d:\devstudio\vb50\nwind.mdb
ExecuteMacro=MakeSuppliers
StartTime=7:55
PropertyForm=1
PropertyValue 1=i 199826
PropertyValue 2=i 199827
ExecuteDay 1=4
LastBatch= 4

[Batch 2]
Type=MSAccess
PathAndDB=d:\devstudio\vb50\nwind.mdb
ExecuteMacro=ShowSuppliers
StartTime=08:30
;PropertyForm=1
;PropertyValue 1=i199824
;PropertyValue 2=199825
;ExecuteDay 1=1
ExecuteDay 2=6
LastBatch=0

[Batch 3]
Type=MSWordVBA
PathAndDB=c:\temp\test.dot
ExecuteMacro=Test
StartTime=08:30
ExecuteDay 1=4
ExecuteDay 2=5
LastBatch=0

[Batch 4]
Type=MSExcelVBA
PathAndDB=c:\temp\test.xls
ExecuteMacro=Test
StartTime=09:48
ExecuteDay 1=3
;ExecuteDay 2=5
LastBatch=0

[Batch 5]
Type=DOS
ExecuteMacro="c:\temp\Test.bat"
StartTime=08:30
ExecuteDay 1=3
ExecuteDay 2=5
LastBatch=0

[Batch 6]
Type=DOS
ExecuteMacro="c:\temp\Test.bat"
StartTime=08:30
ExecuteDay 1=3
ExecuteDay 2=5
LastBatch=0

In addition you have to start OfficeBatcher.exe (normally by adding it to the start-up group. Then it starts each time Windows starts). When OfficeBatcher is started You'll see as a red ball in the Systray. By "right clicking" on this ball you may deactivate OfficeBatcher and test the script manually by choosing Run Batch under the Tools menu. When everything seems to work, you may Activate it so it is triggered related to the time settings in the script.

Other information

When OfficeBatcher executes macros in MSAccess, MSWord or Excel it uses OLE. That means that You have to exit OfficeBatcher to exit the OLE link. When the OLE link is active it may be difficult/impossible to exit e.g.. MSAccess.

OfficeBatcher is programmed with Visual Basic 5.0 SP 3. Only the instincts controls and object are used. It requires little resources.