Am I the only one who is frustrated by QuickBooks’ lack of support for XLSM (macro-enabled workbook) files?

Macros are such a good way to save time and standardize a process.  I often export data from QuickBooks to Excel, and then manipulate the data in Excel.  For recurring tasks, macros can save a substantial amount of time and reduce the risk of errors being made.  BUT — you cannot export to a macro-enabled workbook from QuickBooks (if you try, you get an error message indicating that the file is not a valid Excel file).

There are some workarounds though.

Separate Text File

Macros are just Visual Basic source code, so behind the scene they are text, meaning that you can save them as text files.

Once you have created your macro, edit the macro, copy all the code, open your favorite text editor, paste it in, and save the file (I keep all these files in one subdirectory called “My Macros” in “My Documents” for easy retrieval).

Then, after you export your data from QuickBooks to Excel, click on “Record Macro…”, give the macro a name and description, then immediately click on “Stop Recording”.  Edit the macro, paste the code that you saved in the step above, then save the file with an “xlsm” extension.

This method may be useful when you wish to distribute your macro, without distributing your entire personal library of macros (see below).

Personal Library of Macros

If you don’t need other users to utilize the macro, this is the best solution.

Instead of saving macros in each workbook, you can save your macros in what is known as a Personal Macro Workbook.  This is a hidden workbook named “personal.xlsb”, and all macros saved to it are available to any workbook you have open.

In order to save to the Personal Macro Workbook, on the “Record Macro” screen, select “Personal Macro Workbook” in the “Store macro in:” dropdown box.

Copy/Paste Data in to Macro-Enabled Workbook

This is another solution if you need to share your macros with other users.

You can save your macro-enabled workbook as a template, then when you export your data from QuickBooks, save them in another workbook.  Once saved, you can copy and paste from that workbook to an appropriate worksheet in your macro-enabled workbook.

Conclusion

While it would be nice if QuickBooks supported exporting to macro-enabled workbooks — after all, they are Excel workbooks — at least there are a few workarounds.  As workarounds, none of them may be perfect, but at the end of the day they get the job done.