Data Collection Macro

I mentioned in my last post that I've made a little macro for Microsoft Excel 2003 (I think it works in earlier versions, 2007 and OpenOffice Calc, but haven't tested extensively), which helps collate data from lab instruments which spit out individual tab/comma separated text files.

An example of its use: I was taking readings from six different samples at intervals of a few hours. The machine (a fluorimeter) spits out text files with all the information it's gathered, from which I only needed one line (one particular wavelength I was interested in). This macro picks up just the line you need from the file.


  • Files must be named "X-YYYY" where "X" is a single digit number representing which sample you are testing and "YYYY" is a four digit number (i.e. 0001 to 9999) representing the number of readings you have taken. The numbers need to be consecutive (1-0001, 1-0002, etc.)
  • You need to know which row you are looking for out of the text file. The easiest way to do this is to import it into Excel/Calc and scroll down until you find it, then note the number.
  • If the text file has any extension other than .txt you need to edit the macro a little. Follow the instructions below for getting the spreadsheet, then press Alt + F11, open up the macro using the bar on the left and search for ".txt" in the code. Replace ".txt" with the file extension and save.

The simple option for using it is to download this Excel spreadsheet, and open it up. (you may have to enable macros) then press Ctrl+Shift+W.

Enter the appropriate information into the pop-up box. Then save the resulting file.

WARNING: The macro will fall over if your naming of files isn't consistent.

To have it at your disposal all the time, save the file in the "XLStart" folder. If you installed Microsoft Office in the default location, you'll find "XLStart" under "C:\Program Files\Microsoft Office\Office" + whatever version of Office you have. Then press Window > Hide. Then whenever you open Excel and press Ctrl+Shift+W, the macro will run.

If you want just the macro itself, download this Visual Basic file.

I hope this ends up being useful to someone - it sure saved me a lot of faffing. If I've been unclear please ask for clarification.


Leave a Reply

Your email address will not be published. Required fields are marked *