If you have macros in Microsoft Word, Excel or Access they will be written in VBA - Visual Basic for Applications. If you wish to use this code inside Macro Scheduler you can convert this code to VBScript. However you cannot just copy the code and paste it into Macro Scheduler - or even into a VBScript file - and expect it to work, because there are some key differences:

1. Objects belonging to the application are automatically exposed to VBA in that application, but don’t exist outside of it.
2. VBA supports “Named Argument Syntax”. VBScript does not.
3. Named Constants are automatically defined in the container application but meaningless outside of it.

So, let’s deal with each of these in turn and look at what we need to do about them.

Application Objects, Methods and Properties

Objects belonging to the container application - e.g. Excel - are automatically exposed to VBA within that application. All objects belonging to Excel can be used directly by VBA and your VBA can refer to them directly without having to create them. But outside of Excel these objects do not exist, so the code will fail - Macro Scheduler/VBScript doesn’t know what they are. You need to create them. This is done with the CreateObject command. At the very least you will need to create the “root” object which is usually the .Application object:

Set ExcelApp = CreateObject("Excel.Application")

Now you can refer to objects and properties belonging to Excel by proceeding them with the object variable:

ExcelApp.Visible = true
ExcelApp.Workbooks.Open("d:\example.xls")
ExcelApp.Workbooks.Worksheets("Sheet2").Activate

If you record a simple macro in Excel which enters some values into some cells you might see code like the following created:

Range("E4").Select
ActiveCell.FormulaR1C1 = "fred"

This code is clearly relative to the active sheet. It doesn’t specify which sheet should be used. And the Sheet object is clearly automatically exposed. To do the above outside of Excel we’d need to reference the correct Sheet object, which belongs to a Workbook object, which of course belongs to the Excel application object. So you could do:

ExcelApp.ActiveWorkbook.Sheets("Sheet1").Range("E4").FormulaR1C1 = "fred"

But you might split things up a bit with:

Set ExcelApp = CreateObject("Excel.Application")
Set MyBook = ExcelApp.Workbooks.Open("d:\example.xls")
Set MySheet = MyBook.Worksheets("Sheet1")

MySheet.Range("E4").Value = "Harry"

Note that the first three lines create references to the Application, Workbook and Worksheet objects. It’s then easier to refer directly to the objects, properties and methods belonging to each of those objects.

So where in a VBA macro inside Excel you may just see Range(”E4″).Value=”1234″ consider what the Range object belongs to and remember you need to create a reference to that object, and then prefix it with the name you give to that object reference.

Each Office application includes a Visual Basic reference in the help system. In there you can see how all the objects refer to each other. E.g. if you find the help topic for the Range object you will see that it belongs to the Worksheets object.

Named Argument Syntax

VBA supports something called “Named Argument Syntax” in function calls (ArgName:=ArgValue). E.g. record a macro to sort a column in Excel and you will see something like this in the generated code:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F4:F1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal

Note Key:=Range(… and SortOn:=xlSortOnValues etc. The benefit of this system is that if a function accepts lots of optional parameters but you only need to set a few of them and leave the rest to their default values, you specify just the arguments you want to include.

However, VBScript does not support this system and instead requires all the function parameter values only in the order in which they are declared. So the above would look like this in VBScript:

MySheet.Sort.SortFields.Add MySheet.Range(F4:F1048576"), xlSortOnValues, xlAscending, xlSortNormal

As mentioned, Named Argument Syntax means that not all the parameters need to be passed to the method and because they are named can be passed in any order. So when converting to VBScript be sure to view the help for the function and find out what parameters it expects and in what order.

Named Constants

Named constants that belong to the application will also mean nothing to Macro Scheduler/VBScript. Inside of the application they belong to they are exposed to VBA. They mean something to VBA inside of the application. But take the code outside of the application and the names are meaningless.

For example the code above uses three named constants: xlSortOnValues, xlAscending and xlSortNormal. As these are declared automatically within Excel they mean something to VBA. Outside of VBA, in VBScript/Macro Scheduler they will cause errors because they are undeclared.

We need to declare these:

xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0

I know what you’re thinking - how do I know that xlSortOnValues equals 0, xlAscending is 1 and xlSortNormal is 0? Well, you could look them up. But I don’t bother doing that. I use the VBA debugger. Open up the Visual Basic Editor and hit CTRL+G to open up the “Immediate” pane - it may already be visible. Inside the “Immediate” pane type:

?xlSortOnValues

And press Enter. You’ll see the value of xlSortOnValues appear on the next line. Handy eh?

Conclusion

The key thing to remember is that your Office VBA is referring to objects that exist only within the application in question. Your Excel code can say just Range(”E5″) because it knows what a Range object is. Macro Scheduler has no idea what “Range” is unless you tell it. Once you understand that you need to create references to these objects, and look at the VBA help to understand the hierarchy of objects, the process of porting your code to VBScript should begin to make more sense.

You’ll find an example script that controls Excel with VBScript installed with Macro Scheduler. This post contains similar code. You’ll also find lots of examples in the forums. Hint: try searching for “CreateObject”.

We made the front page of Modern Applications News print edition, with the headline “Software Saves $320 Per Day”. Here’s the article in their online edition: Software Makes the Front Office as Efficient as the Shop Floor

By Ed Authier.

I used to consider Macro Scheduler just another tool to manage the technology that surrounded me everyday in my career. But at a certain point I realized that it was the best tool I had.

Many years ago a colleague introduced Macro Scheduler to me and in those days we used to supplement the Business System job streams that could not be automated inherently; nightly reports and routines, etc.

From there it evolved into complete elimination of printed reports; then through that automation we eliminated a nightly staff position from the IT team.

I then took Macro Scheduler into network engineering with me. Macro Scheduler would monitor router interfaces and call the on-staff person in the event of an outage. I also utilized it for VPN tunnel monitoring. In recent years, I’ve tied it into the clinical systems I have been working with.

There are so many possibilities with Macro Scheduler. I have read (in the forum) recently that Macro Scheduler is for the “layman” developer, but even with VBScripting you just cannot manage all your code and compile tight executables as you can with Macro Scheduler.

The other items that many people may overlook are the updates, enhancements, and fixes. Marcus and company are very commendable in this area. MJT Net also has an excellent response to support issues.

I am now a partner in my own business and I honestly believe that Macro Scheduler has had a hand in my success thus far.

Edward Authier, Chief Information Officer
Convergent Solutions, Inc.
http://www.convergentsolutions.com

Received this nice email today, and just had to share it. Emphasis mine:

Dear Marcus

Yours Sincerely,
Gerry Pentleton
ERA UK

By Ashley Henry.

My name is Ashley Henry; I’m a Network Admin at Advent Capital Management, which is a hedge fund managing billions of dollars. We deal nationally as well as internationally. There are several reports and processes that need to run several times per day; most of which require several redundant manual inputs. There’s never enough time in the day to complete most of the tasks the various departments need to get done. The challenge was to quickly and accurately transfer and print the data from various reports and processes to our trade system and with little or no manual intervention.

I’ve been using Macro Scheduler for about four months, Prior to using Macro Scheduler we attempted to print and enter these orders manually. This was very time consuming and error prone. It also cost more man hours than necessary. Since the Traders and Operations group are often interrupted by other phone calls and tasks, they would sometimes forget which report to run or when to run them.

Before coming to Macro Scheduler my programming / macro skills were minimal. I’ve primarily worked on the networking side of things. Since I had minimal experience in programming I thought that Macro Scheduler would be difficult to use, but I was pleasantly surprised. Marcus has been extremely helpful with some of the scripts that I couldn’t figure out. I’ve made hundreds of support calls to other companies and NONE has come close to the support that I’ve received from MJT. Thus far, I’ve saved the company at least 10 man hours per week and there’s so much more automating that needs to be done… Good job to Marcus for the wonderful product that he has produced.

This was posted to the forum by Shane this morning:

I have been developing scripts for our Banking and Insurance company for approx 3 years.

We currently have 77 scripts with approx 20 000 lines of code, running on 104 machines and used 11502 times last week by our 100+ consultants.

The scripts are used in our Insurance Call Centre for lodgement of new Home and Motor Insurance claims and for tracking call backs by customers once a claim is lodged. In fact what we have created is like a mini front end for our consultants.

Previously our consultants would lodge claims directly into our Mainframe system. Old style front end, Cobol based, no error checking and no way to ensure accuracy of details.

Now Macro scheduler sits between our consultants and the mainframe and automates many/all of the processes. (completes web pages where required such as booking repairers, emails tow in requests for vehicles, prompts for details such as weather conditions in a road accident, Text messages a customer their claim details at the end of the claim process.)

Reports generated allow us to track why customers call back and how often, how many claims we lodge. I have also combined the reports with information from the Australian Bureau of Meteorology to give us real time tracking of New claim lodgement during storms so we can make predictions on call volumes.

I should also mention that the support we have received from MJTNet is second to none. A great program and a great company to deal with.

It’s always fascinating and useful to find out how other people use Macro Scheduler. As you probably know I frequently post Success Stories here on this blog. There’s also a really interesting topic on the forums, here, where people can post information about how Macro Scheduler helps them.

I’ve just scanned through the forum post and made a short list summarising some of the ways people are using Macro Scheduler:

• Creating an Automated Ticketing System
• Caption Competition Tool
• Automated backups and file management
• Anti-phishing Tool
• Automated admin tasks and unlocking files
• Backing up a MySQL database
• Transferring data from Oracle to UPS Worldship
• Automating an ERP Shipping System
• Order email retrieval, parse and input to ERP system
• Automation of e-commerce tasks
• Automated report writing
• EDI Scheduling
• Integrating Word and Excel macros
• Harvesting data from websites
• Stress and load testing ERP software
• Automated provisioning system for a telecommunications company
• To automatically broadcast and archive daily conference calls
• Retrieval from corporate databases and report production where no ad-hoc reporting tool exists.
• “entering data where there are multiple steps over a number of pages & especially where the application does not have keyboard command options. I load the data into a xls, then MS will enter the data quickly & accurately.”

Read more, or post your own experiences here: Tell us how Macro Scheduler helps you, what you use it for.

If you have a Macro Scheduler Success Story and would like to see it included on this blog please contact me. At the end of the year we will choose our favourite story and the winner will receive $100 worth of Amazon Vouchers.

By Keith Passaur.

Three years ago I started eDocFile, a company that specializes in image enabling applications. Before then I was a salesman working for a document imaging company and the products I sold were too complex and expensive for most users. During my training I learned about how years ago people wrote programs that pulled information from a user’s screen (screen scraping) and passed it onto other applications. I realized that if I could find an easy way to do this I could pull a unique piece of information from a users screen and use it to relate an image to the screen. This would allow the user to have an image enabled application.

Image Enabling an application is allowing images of documents to be retrieved from an application that contains data that relates to the documents. For instance, a user is in their accounting software application, they click on an icon or press hot keys and an image of the document appears on their screen that relates to the record in their accounting application. This is accomplished by screen scraping the application (capturing the data on the screen) and passing it on to a method of retrieving the document related to the screen data.

With Macro Scheduler I have been able to capture the screen and do all this. I cannot think of another product that would allow me to do this as easily. One product I developed for this determines what application the user is in, what screen they are on and what information needs to be gathered to scan an image or retrieve an image. Another program I developed works specifically with Loan Origination Software. It is called Scrape LOS.

After spending years testing other products, the National Association of Mortgage Brokers Strategic Alliance Committee approved Scrape LOS. The reasons were because of ease of use and price. Without Macro Scheduler this product could not have been developed and sold in the price range they were looking for. One client spent over eight thousand dollars on an application to scan and file loan documents. They dropped that product within six months and went with Scrape LOS. In a nutshell it is the old 80/20 rule. This application has eighty percent of the features at twenty percent of the price and twenty percent of the complexity.

Other products have also been developed with Macro Scheduler. The latest being one that productively routes faxes to email accounts, printers and folders based upon a set of rules. In its first week of release three copies of it were sold. I cannot say enough good things about Macro Scheduler as I am not a programmer, yet I can write these scripts that get the job done, quickly and economically.

Keith Passaur
President eDocFile, Inc.
www.edocfile.com

If you have a Macro Scheduler Success Story I’d love to hear from you. Drop me a line.

By Bruce Davis.

My name is Bruce Davis. I am a Customer Relationship Manager at Eastman Kodak supporting our Electronics and Warehouse Club channels. My organization is responsible for managing a wide variety of styles of customer orders. Some of the orders come in through electronic methods and require little manual intervention. However, our high value equipment business orders cannot be sent electronically because they require so many manual overrides. The Kodak sales teams have much of the basic order data captured in spreadsheets. Other override information is a constant value across all orders for a sales deal. The lead time between our receiving the spreadsheets and when orders must be entered is usually short. The challenge was to quickly and accurately transfer the data from spreadsheet to our sales order system and to add the constant data to the orders.

We receive Excel spreadsheets from our sales team with hundreds of rows representing sales orders. The orders have similar patterns of the same catalog number, quantities, expected delivery dates, etc. for many different ship to addresses. We use Macro Scheduler to copy and paste the data from Excel into our order management system. There are many other overrides and text messages that have to be done on each order. We built a control macro that loops through several modular macros with a counter set to stop after the last row is entered.

Prior to using Macro Scheduler, we attempted to enter these orders manually. This was very time consuming and error prone. Since our order managers are often interrupted by other phone calls and tasks, they would sometimes forget which row of data they were working on or which overrides were done on a given order.

We have entered thousands of orders using Macro Scheduler. The tool can enter orders faster than a person, particularly when we have two computers running the macros. This frees up our order managers’ time so they can attend meetings, take calls and work on other tasks. More importantly, the orders are entered much more accurately than when done by a person. In many cases, we could not have met our shipping deadlines if we didn’t have the tool entering orders.

Over time we have learned to use the tool more wisely. Common steps done across different roll outs are built into re-usable modules. We only need to create or modify a few macros for each roll out. The rest of the steps are performed by having the control program run the common macros.

By Ron Lee.

Pacejet makes a Transportation Management (TMS) software suite that helps manufactures/distributors ship product via LTL, Truckload, Parcel, etc, and executes their warehouse pick-pack-ship as part of the process. Our software integrates with ERP systems so the customer has one end-to-end process flow in their business from customer to invoice.

One of the challenges we had with one customer was to benchmark our shipment planning performance for large blanket orders with their specific ERP. They would enter orders with 600-800 lines into the ERP system all at one particular time during the day - where most of the lines went to different ship-tos on different dates, etc. Our software would look at the orders and schedule all the shipments and then handle all the execution when the orders were ready to ship (wireless/RF, etc).

The technical issue was how to be able to “pound” multiple 800 line orders into the ERP system repeatedly to push a continuous wave of transactions at our software. That’s where Macro Scheduler came into play. I put together a relatively small set of macros to simulate the user’s data entry into the ERP’s order entry screen so we could validate our planning performance.

The input parameters on the orders could be varied enough to stress test different planning scenarios. With Macro Scheduler we also were able to drive the ERP application screens directly to make this as real-world a test as possible. And lastly, we didn’t have to spend a lot of time coding this as a more complex application.

The result was great for our planning performance, since we can plan orders almost faster than they can be entered into the ERP. But the result was also great for Macro Scheduler since we were able to use it to test out the scenario(s), make our customer comfortable with the result, and ensure we’re in a good spot with our product for the future.

Macro Scheduler is a great product. I appreciate all the flexibility in it. And the option to compile macros is awesome!