Automated import multiple XLSM (macro-enabled Excels) into Stata

[Daniel Jensen]

I'm using Stata 13 to import and append about 100 Excel files into Stata. However, about 90% of the files are in macro-enabled format (.xlsm), which has generated a problem for me.

Since the code itself is a bit complicated, let's say the basic import command looks like this:

import excel using "$directory\Macro database file.xlsm", cellra(A6) sheet(database) clear

In this code:

• A6 is the cell in which the data begins. All spreadheets will have data in columns A-AE, but the number of rows will vary
• There may be multiple sheets within the file. For the sake of argument, we always want the sheet "database"

This results in a database like this:

EventObjectives	B	C	D
First_name
Last_name
Date_of_birth
Occupation
Maximum_studies

In other words, variable names are imported in the "EventObjectives" variable, and B-C are byte variables with all values missing (the table is rotated). Looking in help for import excel, xlsm format is not mentioned, and this conversation confirms that it is not supported in Stata.

The only workaround I have found is to set up your macro-enabled worksheet in ODBC and use odbc load to get the data into Stata. Unfortunately, in my case this isn't an option because:

1. The number of databases in the folder is very large, and odbc load is very slow in my experience
2. The number of databases in the folder is changing constantly, which means I would constantly have to be setting up new databases within ODBC

For these same reasons, just entering into Excel and manually stripping the macros from each workbook isn't an option.

Another alternative I considered was using something like shell to strip the macro features from the workbook and re-save as an xlsx file. Unfortunately, I haven't found any threads that are relevant to this topic so far.

Any ideas on how to could get these databases into Stata without a massive amount of manual work would be much appreciated.