Excel reagent inventory spreadsheet for managing supplies.

Excel reagent inventory spreadsheet for managing supplies.

We have a chemistry instrument at a small lab that doesn’t run a lot of tests.  Therefore it’s important to manage, even micro-manage, keeping stock of reagents and supplies.  Unaccustomed to the lower rate of consumption, on a couple of occasions we’ve over-estimated the amount of reagent that needed to be ordered.  Now we have enough of some reagents to get us through 2019 if it didn’t expire first.  Yet for some other reagents we’ve run out and had to…

Read More Read More

Folders used by Ol Monitor

Folders used by Ol Monitor

Ol Monitor does it’s best to restrict the folders it works with to just one, the “Public\Documents\OlMonitor” folder. Normally the Public folder is found on the C drive: “C:\Users\Public\Documents\Olmonitor” Of course Ol Monitor does read (but not write), to it’s folder in Program Files: “C:\Program Files (x86)\OLMonitor VIM” Normally you would never have to check file permissions on this folder. The other folder you might have to set is the Smarterm Sessions folder. Where/how to find the Smarterm Sessions folder:…

Read More Read More

Fix for Smarterm macro error: “Error in line: Script is too large to be compiled”

Fix for Smarterm macro error: “Error in line: Script is too large to be compiled”

I had long been besieged by this error, and I know I can’t be the only one.  I could find nothing regarding this error in the manual, the help file, or even endless Googling.  Splitting your code out to multiple .stm macro files is the easy answer.  But that is problematic because passing variables to functions between macro files is iffy at best.  I don’t think it’s even possible without using precompiled files. I finally stumbled across this forum post….

Read More Read More

Creating a mini calendar inside an excel spreadsheet

Creating a mini calendar inside an excel spreadsheet

Whilst creating various turn-around-time reports, I thought it would be helpful to add a little calendar to the spreadsheet.  Here is an excel vba function that adds a mini calendar to the cell address you specify: Public Function MakeCalendar(intStartRow As Integer, intStartCol As Integer, strStartDate As String, strEnddate As String) As Boolean ‘Takes start row and column and prints a calendar. By Eric Ainsworth – www.synapsonic.com On Error GoTo Errorhandler Dim WeekDays() As String ‘little array for S-Sat WeekDays =…

Read More Read More

Email archive

Email archive

If you are new to this list, you may not have gotten these previous emails: 1. Website announcement – Joke Intro, 2. Jokes 2 3. You know your instrument is a piece of junk when… 4. Excel Macro tutorial online, minor OL Monitor updates 5. May 2010 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…

Read More Read More

DragInfo Version 1.004 sync with text file

DragInfo Version 1.004 sync with text file

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…

Read More Read More

How to combine/copy multiple workbooks into one workbook.

How to combine/copy multiple workbooks into one workbook.

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.

Turn-Around-Time Excel macro

Turn-Around-Time Excel macro

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…

Read More Read More

Excel Macro Tutorial 6 – Add macro buttons to your toolbar

Excel Macro Tutorial 6 – Add macro buttons to your toolbar

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…

Read More Read More

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

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: