Trawling through memory banks for Visual Basic

Posted on December 14th, 2010 in not yet categorised by Kirsty

It’s been a while since I had any use for Visual Basic, but recently it came in very handy to help solve a crazy workflow issue for a colleague.

The problem:

Basically some form letters need to be prepared on a regular basis but the database spits them out as flat text files. No fields, no delimiters, not even regular spacing between different records in the file. So to tidy up the files it has been a manual process to copy and paste from a .txt file into a .doc file, format and tweak so they fit in the plastic windows of the envelopes. All thanks to a new computer system that hasn’t got it quite right yet from that perspective. Paul, our new team member, spotted this crazy workflow and decided there must be a better way. Perhaps mail merge or similar? But what to do when the data is not presented in a logical framework?

Enter Macros and Visual Basic to the rescue!

We worked out the steps that needed to be completed, used a fair bit of Find/Replace and a bit of basic formatting. The only stumbling block depended on being able to input the date. In our macro recording of course the date information in the Find/Replace was hardcoded into the macro. Useful for today but won’t work tomorrow. Input boxes were needed. I couldn’t for the life of me remember what the code or syntax was but I knew it was possible. So a quick googling later, we have a working solution. I think the first time I experimented with macros and visual basic was back in 1994 or 1995.

We need to test the new process for a week, and get some others to test also to make sure we can transfer the solution.

Update: Process has been tested and today (14/12) is being rolled out to other staff members.

I’m a happy geek. Nice to know some skills stick around a lot longer than you think they will. Yes, I am feeling pretty pleased with myself.