Bluepoint Design

Page last updated: 04/16/2008

I often see users use Excel or other application with a list of values to process with ME and record results and although this works there's a better way. Instead of switching back and forth and arrowing around with all the potential timing and navigation issues it's easier to run ME from and to TSV files. Suppose we have a list of numbers and we need to calculate their square roots with Calculator. Of course this is very simple but this could represent a much more complex process with any application. Consider this list of numbers

256
98
2698
8547
1968

Manually we would navigate to A1, copy, Alt+Tab to calculator, paste, function, copy, ALT+Tab back to Excel, arrow over, paste, and repeat over and over. Lots of potential for problems and we have to figure out a way to stop it if looping. Oh, and it's pretty slow.

Instead let's sick this list in a simple text file and drive the macro with Text File Process. Obviously if we were using multiple values we would use ACSII File Process but I wanted to keep it simple. Here you can see once Calculator launches we stay in it and can fire numbers very quickly at it. On the first iteration we got our result of 16 in T2. Now what do we do with it? Well I am going to manuall construct a line of a TSV file.

I have a write up about the hexadecimal world you can read here but I'll give a quick run down here. In a delimited file each value is separated by a delimiter and the lines are separated with a CRLF. That's Carriage Return and Line Feed which are actually two non-printing characters. They are what you get when you hit enter in Notepad. We can't type these values in so early in the macro I put their values in string variables. Even though CRLF is two chars I stick them into one var for ease of use. CR is 0D in hex (13 decimal), LF is 0A (10 decimal), and tab is 09 (9). In my macro T95 is CRLF and tab is T96. So to make one line of the TSV file I glue it all together like this:

%T1%%T96%T2%%T95% in effect T1 TAB T2 CRLF

Now I append all that to the T3 variable. Alternatively one can output %T1%%T96%T2% to a file and use the Add Trailing CR/LF option. In fact this is essential in some large macro because if the macro blows up and all your results were in T3 you're hosed. But I like keeping things in memory when possible to speed things up.

Now with each repeat I add a line until we're done. In effect we have a TSV file in the T3 variable. Once here I simply it, copy it to your clipboard, and output it to a file. Since Office apps natively use the TSV format to copy and paste tabular data you can simply paste the results into Excel Should look like this:

However I also wrote it to a file and you can open that file with Excel as well. Note that Excel truncated the display above. See how easy that is?

Now one final part, generating input files. This wants to come first in a way but it's better to mention after understanding the format a bit better. If you have a list in Excel how do you create the source file to drive ME? The easiest way is to "Save As" and choose Tab Separated Values as the format.

Notes:

1. TSV files are like CSV files except they use tabs instead of commas. I find this easier to use because I don't have to determine if the field has commas and encapsulate with quotes since we generally never use tabs in any of our strings of data. Of course the same technique could be used for commas. 
2. Copy and paste in Office Apps: If you copy something in almost any tabular data like Excel or Access and read it into a var it's in TSV format. So sometimes it's easier to have the user copy what they need have the macro create the file to process. Same goes with output.
3. In this example I created a file to drive it using Text File Process because it simplifies the understanding. More often however I drive the macro right from the string var. How I do it is to look for the position of the first tab and store that in an integer var. Now I copy that portion of the source var to a different var, and trim to get rid of the tab, then delete the same part of the source var. I repeat this over an over in a pattern chewing thru the var as I go. Works slick!