Clean pastes with Excel VBA

Published on 14 December 2007 in VBA. Closed

I’ve talked about some of the Excel VBA I’ve done before, and here’s a new thing I learned today.

Problem

One part of my application selects a range from one worksheet in the spreadsheet and pastes it onto another. Originally, the code I used looked like this:

destinationWorksheet.Range("A2").Select
ActiveSheet.Paste

There is one problem I was unaware of. Every time the .Paste operation executed, it added a new “Named Range” to the ActiveSheet. I only realized this when snooping around in the Properties for the Excel file, where I found (to much dismay) 840 different “Named Ranges” referring to data long since thrown away! An older version of my application, which used this unspecified Paste operation much more frequently, had even more of these attached to numbers of other worksheets. Continue reading ‘Clean pastes with Excel VBA’

Lazy list management

Published on 12 November 2007 in Linux. 2 Comments

I use a couple awk scripts to manage a list of students who come to church with me every week. When a student tells me he will be coming on Sunday, I put an asterisk (*) in front of his name in a static text file with everyone’s names. If they tell me they won’t be coming, I put a minus sign (-) in front of their name so I remember that they said so. If for some reason I’m not sure, I put a question mark (?).

My main script spits out a list of all the students who are coming (i.e., have an asterisk). There’s no genius to it, I’m just logging it here for reference.

I also have another script that wipes all the attendance marks out of the main list file. Continue reading ‘Lazy list management’

Producing valid RSS from my XSL Transform

Published on 11 October 2007 in XML & XSL. Closed

In a previous post, I showed the XSL transform I designed to transform a simple XML file I use into “(almost) valid RSS.” I called it (almost) valid because of one problem, which I address here.

The problem reviewed

My original XSL assigned an arbitrary ID to each entry in the news file based on its order in the list. What this meant was that the first item received the integer 1 for an ID, the second 2, the third 3, etc. Here it is:

<!--old method, produces invalid link & guid-->
<link><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
  <xsl:value-of select="position()" /></link>
<guid><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
  <xsl:value-of select="position()" /></guid>

This was fine for my original purposes, but since this hardly represented a unique & permanent identifier for each post, it caused some problems when echoed into the RSS <guid> and <link> elements. The significance of the <guid> element in particular is eliminated with such an approach. Every time I added an item to the list (see the previous post for an explanation of my schema), it would be assigned the ID of 1, and the older posts would be assigned the ID numbers 2, 3, 4, 5 etc.

This meant that most RSS aggregators would not detect that a new post had appeared and would not update accordingly.
Continue reading ‘Producing valid RSS from my XSL Transform’

Fixing memory leaks in Excel VBA… (we hope)

Published on 13 September 2007 in VBA. Closed

One project I constantly return to is an application I built in Excel VBA that does a lot of different things. One specific problem remains unsolved with this application, so I’ve decided to throw it out here and see if any gurus have an answer.

Basically, as a result of some Excel memory leaks beyond my control (I can’t install recommended hotfixes on the computers where this application is used, and need to be able to handle the issue with/without hotfixes anyway), when I shut down Excel it typically remains stuck in memory (and visible under “Processes” in “Task Manager”) even though it looks like it is gone to the user.

Paranoia

I realize that many would argue that since this bug is Microsoft’s problem and not mine, that I shouldn’t trouble with addressing it. However, since this application I’ve built is something that gets used frequently (but not constantly) throughout the day, I can imagine this becoming a problem (or at least wasting resources) at some point.

Another rejoinder to this charge is quite simple: I’ve actually ignored this issue for about a year now already, and am only revisiting it because I’ve learned some things about VBA that enable me to possibly defeat it. If I didn’t have any hope of fixing this, I wouldn’t try. Continue reading ‘Fixing memory leaks in Excel VBA… (we hope)’

Processing barcode scanner data with awk and sed

Published on 17 August 2007 in Linux. 2 Comments

We had an interesting project pop up here at the Library where I work yesterday. Apparently, part of our inventory process here involves downloading text files with raw barcode data from our barcode scanners, extracting the barcode from amidst the other junk data that pads it in the file, and then loading a freshly formatted list into Millennium, our library’s catalogue software.

I’m not typically involved with inventory or the particulars of maintaining the Millennium catalogue, but I was called in to help with writing some bash scripts to facilitate the process. Continue reading ‘Processing barcode scanner data with awk and sed’