SynapSonic Software

If you are new to this list, you may not have gotten these previous emails:
1. Website announcement
2. Jokes 2
3. You know your instrument is a piece of junk when…
4. Macro tutorial online, minor OL Monitor updates
5. Ol Monitor/DragInfo program updates & new feature poll
6. On OL Monitor ‘not working’ with 6.4.
7. OL Monitor Budget n Bugfixes
8. Testers needed for pending log module
9. Pending log module ready

Synchronizing with text file:

DragInfo now supports synchronizing with a plaintext file.  Changes to the xml file will update the plaintext file and vice versa.
Any snippets that exist in the xml file already or that you create will also be created within the text file.
On the other hand, DragInfo ignores any info in the text file that does not match a tab title.
Lets say you have you have a tab named “Dog”.  If a line is detected in the plaintext file with the word “Dog” (no other words on same line), the info below it will be treated as the snippet info.  The program knows the snippet for “Dog” ends when it sees more than 2 blank lines, the end of the file, or the name of another tab.
Read more…

A lab billing macro example

Here is another useful macro for copying data from multiple workbooks into one workbook. At the end of the month billing needs a report with all the months charges listed. We have been creating a workbook daily with the day’s charges listed. Now we have to collate all those workbooks into one workbook. Again it’s a tedious process in need of automation.

Read more…

Once again we have reports that the system has chucked out that need a lot of work.  In this example we are reporting the turn-around-times for a monthly report.  In this example, the Cerner Powervision app gives us a report on each tests’ TAT.  This report is exported to Excel and the user counts how many tests exceeded TAT, and calculates the percent meeting target TAT. TEDIOUS!  Excel macros to the rescue:

Instead of making this into a full-blown tutorial I will just list the sticking points and post the macro.  If you have any questions just add a comment.

What to do when you can’t use Personal.xlsb to store your macros

The Powervision app exports the reports to Excel, but it’s not loading my Personal macros file.  This is because it’s running a Citrix Excel (I’m guessing) instead of using my pc’s excel.  For this you just have to put your macro code into a workbook, and then load that workbook with the Citrix excel along with the exported report.  Your macro code can still do everything it needs to, having Personal macros is just a convenience.  I have named my workbook “ProcessTATexportsMacroXl2003.xls”. It doesn’t actually have or import any data, it just contains my macro code.

The app exports each report to a separate workbook, this leads to another problem:

Read more…

The finishing touch will be to put your macros into the toolbar, so they will be available to you every time you open up excel.
I have been working with Excel 2007 in these tutorials but up to this point everything I have shown you will work with 2003 and most other versions.  With 2007 Microsoft removed the ability to (without tricky tweaking) make a custom drop-down menu in Excel.  But they did make it easy to add a custom button to the quick access bar for a single macro.  For a great tutorial on adding custom menus to 2003 see this page: Assign a Macro to a Toolbar or Menu.  For 2007 see my tutorial below.

Adding a custom macro button to the Excel 2007 quick access toolbar:

The first thing to do is make sure you’ve been putting your macro code in the right place.  If you put the code in the workbook it will be available to anyone who opens that file.  But in this example we open and process a new workbook every day.  So we should put the code in our personal macro file.  This makes it available to you every time you open Excel.  Open the visual basic editor to look at your files: Read more…

When a test is cancelled in the system it appears as a charge and then a credit with the same date and time.

The billing team does not want to see these, as they have to charge for a test and them immediately credit a test, and it’s a hassle.  So we clean them up before submitting.
Often a test gets ordered, cancelled, and then re-ordered, so it is important to make sure the code doesn’t delete the re-ordered test: Read more…

The billing report has a bunch of tests with at cpt code of ‘0’ or a blank.  These are non-chargeable tests.  We used to have to delete them manually.

For this we can loop through the rows and delete non-chargeables just like we did with the junk removal.  For efficiency we could just add this code to the junk-removal macro, but I have kept them separate here for the sake of simplicity. I called the macro ‘DeleteZero’ and copied and modified from the last macro: Read more…

Ok now we’re getting into a bit more advanced material.  We’re going to have the macro do some real grunt-work for us.  Here’s what we want it to do:
1. Find the last row of the report.
2. Go through the entire report, row by row, and delete anything that we don’t want.

The report has a bunch of junk we need to delete:

Simple enough, the only problem is you can’t just record yourself going through the report deleting stuff and then go back and change the code.  Read more…

We need to autosize the columns to fit the data and then change the background color of the column headers.

How to autosize columns with and Excel macro:

Autosizing the columns with a macro is very easy.  This is what the recorder recorded: Read more…

In this 6-part series of tutorials, I show you how I was able to automate a process that took 5-10 minutes per report.  Now the process takes about 30 seconds.  The techniques and code examples from these tutorials can be adapted and used by anyone who has the misfortune of being delegated to do tedious gruntwork in Excel.  Many concepts like ‘how to record a macro in Excel’ are NOT covered in this tutorial.  There are plenty of tutorials on the web covering the basics of Excel macros.  Instead this tutorial fills in the gaps by explaining a real-world example from start to finish.

How to open a text file with an Excel Macro: Read more…