Aquarium Blog

Thursday, June 30, 2005

How to recover source code from your old VBA projects

Yes, I have been on blog vacations, or hiatus for the Nth time. I have much going on in my life, but I just don't feel like writing lately. Not every day.

Anyway, I have just found by myself a solution for a long standing problem, and I wanted to share in on the web, just in case somebody else needs it. The fact is that I have googled a lot for a solution for this, and I have found the same question formulated several times, but never an useful answer.

Now, straight to the story...

Some years ago, using Office 2000 Developer (or was it Office XP Developer?) I created a little Outlook addIn that helped me remove duplicate mail items with ease.

If you are wondering why did I need that, there are several possible reasons. Think about POP3 server request that timeout too fast and download the same emails on each retry. It happens to me all the time with large attachments in my GMail account. Another reason would be odd rules in Outlook that creat create duplicate emails.

On the days of Office 2000 and XP Microsoft used to provide the option to create addIns straight from within VBA. Those projects were saved as .VBA files and you could also compile them to DLLs. That functionality used to come with the Developer Edition of Office.

Later, when Office 2003 came out, this functionality was cut. I mean, Office 2003 Developer Edition does not exist at all. What exist is Access 2003 Developer Extensions, which covers most, but not all the Access related functionality found in previous Developer Editions, and Visual Studio Tools for Office, which is a brand new creature.

Among the most important features that Microsoft left orphaned on Office 2003 I can mention:
  • Access - Source Safe Integration (which was fortunatelly addressed more recently via a free download)
  • The ability to create compilable, self contained VBA Projects and Office addIns.
I am not really interested in continuing the development of my "remove duplicates" addIn as a VBA project, as this is clearly obsolete technology. But I recently found a few bugs on it, and I wanted to get access to the original source code in order to migrate it to VSTO. But, I haven't been able to see the source code since support for VBA projects was discontinued.

Mine is a really simple addIn, but I am too lazy to write it again. So, being laziness the mother of invention (or, er... hacking), this is what I found today:

Off all Microsoft Office 2003 applications, there is at least one that allows you to create macros in VBA but don't have an application specific file format: FrontPage. Instead of defining a proprietary file format, FrontPage allows you to edit standard HTML and related files, so I guessed FrontPage had to save VBA macros somewhere else. Yes, you guessed it! It saves its macros in a file called "Microsoft FrontPage.fpm" in %USERPROFILE%\Application Data\Microsoft\FrontPage\Macros.

Update: A few minutes after posting this, I found that I could have used Outlook's own VbaProject.OTM, with the advantage that the code could possibly run.

A simple binary comparison revealed that both the .FPM file and my old .VBA shared the same header format. Hmmm.... So the .FPM file uses the same conspicuous self contained format that .VBA projects used!

The obvious solution was to temporarily rename the .FPM and to copy my VBA project to the same folder with name "Microsoft FrontPage.fpm". Next you open FrontPage, you go to the Tools menu, Macro, Visual Basic Editor, voilaá, your VBA code is back.

I copied it to a text file, closed FrontPage and restored the files to their original names and locations. Enough for today. Lazinesss rules!

Disclaimer: if you want to reproduce the steps described above, and you make a mistake, you are on your own.

PS: Isn't it amazing how many problems you can solve by temporarily renaming files? I have found quite a few otherwise unsolvable problems with SQL Server and Outlook that I could solve this way. Well, they are otherwise unsolvable because I don't have access to source code!

PS2: I plan to publish the lame code for this addIn in a future post. Maybe when I am able to port it to VSTO I will publish both sources and a detailed comparison.


Post a Comment

Links to this post:

Create a Link

<< Home