Excel to Gedcom conversion using VBA

Compiling information into spreadsheets from web and other sources has always be an easy and useful research aid. Using the power of spreadsheets including custom filtering, multi column sorting etc is great to aid comparisons and finding possible matches, Unfortunately there still lies the time consuming task of entering the data into a genealogy program, that's where VBA can help.

So what is VBA, well I suppose you could call it macros on heat, but more accurately it is an implementation of Microsoft's event-driven programming language Visual Basic 6. Really anyone who has done any sort of basic programming in the past should be able to achieve some results pretty quickly. VBA comes as part of Microsoft programs such as Excel and can be accessed by pressing Alt+F11.

There are various useful web sources similar to this one, for beginners and there are many more. Also as you progress don't forget that VBA is essentially Visual Basic and address any particular problems to a forum such as VBForums which is a great resource.

So load up Microsoft Excel, press Alt+F11 and you will enter the programming environment. When you do so on the example files below all you have to do is run the code through any of the options on the left.

That's it, download the example files below, press Alt+F11 and get a feel for how you build a gedcom file from the spreadsheet information.

The files below are labeled Level 1, 2 & 3 for a reason, if the concept of programming and the gedcom file format is new to you then do start with Level 1. The other thing I must stress is to import the gedcom created after you run the code into a new Rootsmagic (or other) genealogy program and thoroughly check the results, you can then tweak the code to get the results you need. 

The code is written in long hand with a focus on achieving the results rather than creating slick and fast code. Usually projects like this are going to be used once so it's more important for me to get the result rather than trim a second of the run time. There are also many variables assigned especially in the first example to help explain what is going on, this example is also heavily commented (the green bits) to help understanding what does what.

Level 1 File Download. This is one of the most basic constructions with no family ties. It is constructed from a shortened migration list, calculates an approximated birth date, and created facts for each piece of information available. This example also assigns a basic free form source with an appropriate citation and is well commented to explain the various elements.

Level 2 File Download. This example takes things a little further and applies family linkage information to create a gedcom of couples from a shortened marriage listing. This example not only shows how Place Details can be used but also how Geocoding can be managed in a spreadsheet before compilation to gedcom. 

Level 3 File Download. OK so it's here now but I am still fiddling with it following the RM 4.1 release and some changes to shared roles. I have included the file complete with the 7000+ records of interest to me that I lifted from the 1901 Ireland census. It shows examples of family links, Place Details and Shared Roles and should give a good insight to what time saving can be achieved with a few days effort.

Have fun with the files and do post back to the forum if you develop any nifty bits of code for working and manipulating data.