XLTX contains macros, but then they are not there - what gives?

Question

I have an xltx that clearly contains macros when i open it, based on the error "ThisWorkbook.RefreshAll err..etc .etc., due to invalid sql connection".

(whether i open the xltx as New or Open).

Once I go to vba project, there is ZERO code of any kind.

I consider myself a VBA expert, but have to confess, I've never worked at ALL with template files. (they seem like a completely unnecessary thing to me).

is there some magic that makes vba code invisible in xltx ?

Answer 1

You need to use XLTM (Excel Macro-Enabled Template)- the difference is the same as between XLSX and XLSM - you can write whatever code you like, but if you don't save it in the macro-enabled format, it won't still be there when you re-open the workbook/create a new spreadsheet based on the template.

You should have had this error message when you saved (although if you just clicked "Yes" it'll have saved anyway, just without the macro code)

If you haven't closed down the template file you're working on, you should still be able to save it as XLTM and retain the code - otherwise it's gone, unless it can be recovered from an autosave:

Answer 2

There will not be any macros in an .XLTX. You can try renaming the file as .XLSM or .XLTM in file explorer to get them back. The message "ThisWorkbook.RefreshAll err..etc .etc., due to invalid sql connection"." also looks like a broken link to a datasource rather than a macro.