Marcus' Macro Scheduler Blog

This was posted to the forum recently and I thought I would share:

I’m currently using [Macro Scheduler] to pull aviation electronic manufacturing information from a SAP database, compile into a MS Access database, then generate a report such that the data becomes useful information needed to identify and prioritize manufacturing tasks and processes across a diverse profile of complex systems being manufactured.

Had to spend hour+ (heavy on the +) every day spread out throughout an entire shift getting this information manually. Often, due to the length of time to acquire the info, the information would be derived too late in the shift to make good use of it. Lost opportunities and manufacturing delays galore.

The information can now be gleaned by anyone within minutes. The entire department is empowered to work smarter through making use of accurate real-time visual status information. Status is no longer hidden or obscure to visualize.

I was able to understand and adapt to the very easy to use Macro Scheduler structure with little frustration. I found that things I tried actually worked as anticipated and worked far more reliably than I dared hope for. When stuck, I found EXACTLY what information I needed in your forums. In fact, I ecstatically found differing superior methods in other areas of interest while searching for these solutions.

I haven’t had this much fun in decades. I want to thank the crew @ mjtnet and the users of Macro Scheduler for the efforts that brought enlightenment to me of a better way to do things.

Share YOUR story – what do you use Macro Scheduler for and how does it benefit you?

By Keith Passaur, president and owner, eDocfile, Inc., Valrico, Florida

eDocfile is a consulting firm that specializes in creating image-capture programs to increase our clients’ productivity. Over the past seven years, we’ve used Macro Scheduler in virtually every project we take on, so we don’t have to “recreate the wheel” each time we need to provide the same automation steps.

In early 2009, we were contracted by a consulting firm in The Netherlands for a small programming project. A few weeks later, they contacted us again—this time with something much larger and much more challenging in mind. Their client, a Dutch hospital, needed automation assistance to support a very large, multi-site medical vaccine trial—it would, in fact, be the second-largest project of its kind ever, involving 85,000 patients. In all, 340,000 documents—or 4,000 documents each day—would have to be scanned with Optical Character Recognition (OCR) technology for automated filing. File by OCR, one of the products we offer, had already been purchased. File by OCR works with searchable pdf or tiff files to automatically perform OCR and extract text. The extracted text is parsed and used to rename and relocate the file to build a file folder hierarchy.

The consultants asked us to modify File by OCR so the hospital’s vaccine-trial files could be named based on index information (patient number, center number, and document type).

Scanning Forms for Faxing, OCR Filing

To make sure the customized program would work as intended for the clinical staff, it was important that we completely understood how the data would be collected, transmitted, and entered into the program. Since OCR would read data from a pre-printed form that could not be altered (such as by adding a barcode), we requested original copies.

We received a six-page tri-fold form that each patient would complete. On the third page, a distinct vertical number, readable by OCR, contained the patient number and center number where the form was generated. We were told the completed forms would be scanned at each remote center in a duplex manner, creating a two-page tiff file for sending to the hospital. There, the scanned image would be separated into six individual pages and the vertical number extracted for filing. The file would then be re-assembled into a five-page pdf (page 6 of the form was blank) for automatic filing. All processing of documents would be done in a batch process after scanning, freeing the users to move on to other tasks while the OCR process was underway.

Seeing the forms and understanding the procedure, we were able to modify File by OCR so the incoming documents could be stored in a logical file folder hierarchy, separated by center. Because they would be kept in a completely non-proprietary format, the documents could be imported into any database and a hyperlink assigned for retrieval.

Macro Scheduler Speeds Filing, Data Verification

Because each document would be read with OCR and filed using the same steps, we used Macro Scheduler to automate initiating the file command lines. Macro Scheduler allows us to use regular expressions to parse text and automate the moving and renaming of files. Once the steps are put together in the script, they can easily be modified for use in other programs to automate similar actions.

Each center had been assigned a certain range of patient numbers, so to check for missing or misfiled documents we created a macro that compared the list of assigned numbers for that center to patient numbers to validate the OCR. Also, since all files must be accounted for, a macro was created to extract the patient number, center number, and the trial stage from an Excel spreadsheet for validation.

Catching OCR Errors

Because OCR is not 100% accurate, we wrote scripts that would test and retest the data captured for any errors. The net result is 1 error per 1,000 scanned images. Those with errors must be processed manually.

The Dutch consultants installed our Macro Scheduler-enhanced program in fall 2009, and the complete process was validated by an external auditing company.

The hospital is processing 1,300 documents and more than 2,600 faxes daily. Users manually process the three or four fails each day with the manual processing tools built into the software.

We think this is a great example of how Macro Scheduler can be used to automate several repetitive steps in an environment where everything must be executed flawlessly, with redundant cross-checking and seamless integration.

The Dutch consultants were pleased, and, in fact, they’ve said they’ll be calling us again very soon, perhaps with something even bigger in mind….

Keith Passaur can be reached at [email protected], 813-413-5599

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

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.

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!