Search code examples
vbaexcel

How do I open an Excel .xltm file via VBA as a template?

I have some code written in VBA to automate creation of PowerPoints. (Yes, I understand there's a special place in hell for this. Sometimes, however, one must do what is required whether it makes sense or not.)

The process is as follows:

  1. Open an Excel template file MyTemplate.XLTM creating MyTemplate1.XLTM.
  2. Update data in MyTemplate1.XLTM as appropriate
  3. Save MyTemplate1.XLTM as NewFile.XLSM
  4. Open a PowerPoint template file MyPowerPoint.PPTM
  5. Loop through all the objects/shapes on all the slides in MyPowerPoint.PPTM looking for linked shapes
    1. For each linked shape, change Shape.LinkFormat.SourceFullName from MyTemplate.XLTM to NewFile.XLSM (with appropriate gymnastics to link it to the proper location within NewFile.XLSM)
  6. Save MyPowerPoint.PPTM as NewPPT.pptx

As the code executes step 5.1, it opens MyTemplate.XLTM, adjusts the link location, then closes the file. For every. single. linked. object. in the entire PowerPoint presentation. This, obviously, is rather slow. My template file is only 621KB, but loading it from the network takes enough time that I can see the thermometer bar filling in the Excel status bar as it loads. (Yes, I can run with .ScreenUpdating=False, and I do, but it still takes time.)

Once I've created the PowerPoint, I can open NewFile.xlsm, then open NewPPT.pptx and refresh all the links very quickly. If I don't open NewFile.xlsm first, the refresh all links takes significantly longer.

Therefore, I'd like to open MyTemplate.XLTM in code once prior to step 5.1 so that it doesn't need to load every time. However, when I execute

Dim TemplateXL as Excel.Workbook
Set TemplateXL = XLObj.Workbooks.Open("MyTemplate.xltm")

I get a file called MyTemplate1.xltm, and PowerPoint still has to open MyTemplate.xltm each time I have to refresh a linked object.

In Windows Explorer, I can right-click MyTemplate.xltm then select open and I am editing the actual template itself, instead of creating a new document based on that template.

How do I emulate the Right-Click | Open functionality within Excel VBA code?

Solution

  • It's been a minute, so I'll bring closure to this...

    I ended up following Tim Williams' suggestion of converting my XLTM macro-enabled Template workbook into an XLSM macro-enabled standard workbook. This allows me to open the workbook in code and update links "very" quickly (in comparison to when it was an XLTM file).