Automated Word Master Documents – Cool Code

Word Master Documents are a useful feature with a problem. The master document along with its subdocuments can easily get corrupted, sometimes losing the whole collection of Word files. Word professionals will normally advise you to steer clear of them, for this reason. Here’s how to create automated Word master documents that make this feature safe to use.

When you have a collection of Word files, perhaps from disparate sources, and you need to print them or save to PDF with a common table of contents, Master Documents are a good solution.

In my opinion, the real problem is that Master Documents are work to assemble. When you’ve put all the effort in to create one, it’s so tempting to keep it as a permanent collection. But over time, some of the subdocuments need to be revised. That’s when the heartbreak begins, because editing the subdocuments while they are part of a master document can easily corrupt part or all of the collection of files.


Automated Word Master Documents

I’ve created a Word template with a VBA macro that automates the creation of a master document from a folder full of Word files. If the master document is easy to create, then it’s easy to delete when you’re done printing. And that’s exactly what you should do: use the to create temporary master documents, print or save to PDF, then put the master file in the Trash. The folder of Word files that were the source of the subdocuments will be untouched and uncorrupted.

The next time you need to print, simply run the macro again. Create a new master, print it, delete it. This is the safe way to use this powerful Word feature.

The template works with both Windows and macOS versions of Word. You can use the file as a standalone template for occasional use. Double-click on it to create a fresh Word file. Click on the Insert tab, then on the Insert Subdocuments icon at the right end of the ribbon. Choose the folder that contains your Word documents. After the subdocuments are added, add a table of contents, if you like. Print or save to PDF, then delete the automated Word master document.

You can also use the template as an Add-in. This is handy if you often create master documents. See the instructions that accompany the template to learn how to install it. Then, whenever you open Word, the Insert Subdocuments icon will appear at the right end of the Insert tab.

Add Subdocuments command

Installing as an Add-in has the advantage that you can easily use a preformatted Word file that already has a table of contents set up ahead of time. You can create, print and delete an automated Word master document in a matter of seconds.

Here’s the link to the download: Automated Word Master Documents Assembler with Instructions.

Please note: You might ignore the instructions to delete the master document after printing. You might corrupt your files. Because of this potential problem, use this add-in at your own risk. We are not responsible for document corruption or data loss in your documents.

Most Recent Office You Can Run – Best Practices

As Apple and Microsoft release new operating systems, it’s no longer possible to install the latest version of Office on your old computer. Here’s is a list of the most recent Office you can run for the operating system you have, plus where to find it.


Most Recent Office You Can Run on macOS (Updated for 2024)

Rant alert! Microsoft supports the last three operating systems in both Windows and Mac. Unfortunately, Apple has a policy of releasing a new operating system every year, whether we need it or not. The result of these two business decisions is that there are rafts of perfectly serviceable Macs out there that can no longer install the current version of Office. It looks like Apple intends us to toss them and buy new ones. Another case of lip service to environmental sustainability. Rant over

If you buy or subscribe to Office for Mac today, you’ll only find the current versions of Microsoft 365 (the subscription version) and Office 2021 (the retail edition) to be easily available. But Microsoft maintains a page of older installers at Update history for Office for Mac. All of them can be activated under a current Microsoft 365 subscription or Office 2021 license.

Please note that all of these are final releases: there will be no security updates to follow. Be careful when downloading Office files from the interweb.

At the time of writing, the current crop of obsolete Macs are those that can only run Big Sur (macOS 11). These are machines that are around 10 years old, but are completely useable for applications like Office. The last Office version that Big Sur can run is 16.77. At the History page, look for the Office Suite Installer, with or without Teams, dated September 12, 2023. Do not download the updaters for individual programs! Download the Office Suite installer!

Then run the downloaded installer. After installing, normally Microsoft AutoUpdate fires up and installs the final 16.77 version. Wait until it finishes, then open Applications and run one of the Office apps.

Prompt to sign in.

You’ll see a dialog prompting you to sign into your account. Enter your name and password.

Enter your Microsoft Account email and password.

If you’re a Microsoft 365 subscriber, that should be the end of the process. Your software will be activated and you’re good to go.

If you have purchased multiple permanent license editions, you’ll see a dialog asking to to choose which license to apply. Office 2019 and 2021 no longer have separate licenses for Mac and Windows, so at this point it possible to switch Office 2019 (for example) from Windows to macOS or vice versa.

Choose an Office license to use with your installation.


Older Editions of macOS

Catalina (macOS 10.15) can run Office 16.66. At the History page, look for the Office Suite Installer, with or without Teams, dated October 11, 2022. Reminder: Do not download the updaters for individual programs! Download the Office Suite installer!

Office for Mac Update History

Catalina was the first OS to require 64-bit software. This requirement made Office 2011 obsolete, since it’s 32-bit. But Office 2011 is still a useful edition in macOS. It can still do things that were permanently removed from later versions of Office. So you might want to stick with Mojave and install the newer Office that goes with it. (There’s no problem in having Office 2011 and a later version installed on the same computer.) In this scenario, download and install the 16.54 version dated October 12, 2021.

One of my favorite Macs is my 17″ MacBook Pro. It’s the machine that travels with me, but it’s 12 years old and can run only High Sierra. The most recent Office you can run on this computer is 16.43, dated November 10, 2020.

There’s a pattern here: Apple releases new operating systems in the fall, usually in September or October. The Office version that Microsoft releases the same month is the last one for the fourth-oldest macOS. I don’t have any machines running Sierra, but if I did, I would try the October 15, 2019 edition of Office 16.30.

As I mentioned earlier, you can run an older version of Office on an older computer. Since Office 2008, the file format has remained pretty constant, so the software can still be useful. You can install Office 2011 under Mojave or earlier, while Office 2008 can be installed under El Capitan and earlier. These versions can co-exist on a Mac with a newer version (or with each other), though Microsoft’s History page only goes back to 16.27 from July, 2019.


Most Recent Office You Can Run on Windows

Under Windows, the situation is easier, as Microsoft waits about 3 years between operating systems. Plus Windows isn’t as finicky about the hardware it runs on. I have an old Mac Pro from 2006 that can only run OS X Lion and Office 2011, but under Boot Camp it does either Windows Vista and Office 2010, or Windows 7 and Office 2016. Windows 8 is likewise limited to Office 2016, while Windows 10 (now 9 years old!) can run the current version of Office 2021 and Microsoft 365. You have to have a really old Windows computer to be unable to run a useable copy of Office.

Chart Templates – Best Practices

Chart Templates allow you to capture and reproduce the appearance of sample charts. This avoids having to include sample charts for users to copy and paste. Here’s how to use them:


Installing

Chart templates must installed to a particular folder so Office can find them.

Windows Installation

  1. Open a File Explorer window.
  2. In the address field, paste in: %appdata%\Microsoft\Templates.
  3. If there isn’t a Charts folder there, create one.
  4. Copy the chart template(s) into it.

macOS Installation

  1. While holding down the Option key, click on the macOS menu bar. Choose Go, then Library. The hidden user Library folder opens.
  2. Open ~/Library/Group Containers/UBF8T346G9.Office/User Content/Chart Templates.
  3. Copy the chart template(s) into it.

Using Chart Templates

Chart templates can be used in Word, Excel and PowerPoint. Here’s how to access them:

Windows Usage

  1. In the Office program, choose Insert>Chart.
  2. In the Insert Chart dialog, click on the Templates icon.
  3. Select the chart template in the right-hand pane, then click on OK. A chart is inserted in your document.

macOS Usage

  1. On the Insert tab, choose Charts>Templates and select the template from the pop-up list. A chart is inserted in your document.

For more information about creating chart templates that work better then the Microsoft default, please see OOXML Hacking – Chart Template Colors

OOXML Hacking: Buy the ebook

The ebook version of OOXML Hacking has been released. The last three years have gone into adding the equivalent of 40 pages of new information. This is in addition to the trove of unique techniques that already appeared in the print version. Here are screen shots of the table of contents, to give you an idea of the topics covered in this publication:

Table of Contents 1
Table of Contents 2
Table of Contents 3

Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.

As always, all techniques are covered in both Windows and Mac, where possible. The book contains a link to a downloadable text file of all listings in the book, so you don’t have to re-key text from the screen. The book is currently available on Kobo.com and Barnes & Noble. Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.

Some have asked if they can use a Kobo book in a different reader. The answer is Yes! Here is Kobo’s page on how to download books from your Kobo account to export to another device or app.

Please note, this e-book has digital rights management applied. All code listings are available in a downloadable text file, so you don’t have to re-key anything. If you have any problems, please contact us at this address

Your Office Questions Answered – Best Practices

Brandwares provides top-quality, bulletproof templates to a world-wide clientele. We have the Client List to prove it. But you can get your Office questions answered for free (or cheap)!

I answer questions online about PowerPoint and Word for both Windows and macOS. For general questions about formatting and using both programs, visit the Answers.Microsoft.com Word or Answers.Microsoft.com PowerPoint forums. If you’re a VBA programmer working with Word or PowerPoint, you can get my help at the Stack Overflow Word or Stack Overflow PowerPoint pages.

I also answer questions at Experts Exchange, where I’ve been awarded a Distinguished Expert award for 2021.

Office Questions Answered - Experts Exchange Distinguished Expert 2021

This site isn’t free, but it is jam-packed with expertise. There’s a 7-day free trial, if you want to check it out. Tag your post with Microsoft PowerPoint, Microsoft Word, VBA and/or Fonts Typography to ensure your Office questions are answered.

OOXML Hacking – Chart Template Colors

Chart templates solve the old problem of having to send out copy-and-paste samples of graphs. But they only display the first 6 of the chart template colors you designed.

Adding More Colors

The most common application of chart templates is to overcome the design limitation of having only a 6-color palette to work with. I wrote about this problem years ago: Office Charts: 6 Colors Maximum! – Best Practices. After a chart uses Accents 1 to 6, it starts recycling those same 6 colors with automatically generated darker and lighter variations. Banks and wealth management clients often need many more than 6 data series in their charts, but still want to have colors that are on-brand and designed.

To create a wider range of data series colors, create a chart with the maximum number of data series that the client requires. Our record is 25 colors! Then right-click on the chart edge and choose Save as Template. This captures the formatting of the sample that you’ve created.

Your template will work just fine on charts that have already been created. Select the chart, choose Change Chart Type (yes, even if it’s the right type already), click on the Templates icon, select the chart template and click on Apply. Easy peasy, job done!

Insert Chart Templates folder


The New Chart Problem

But this process breaks down if you try to create a new chart from that template. Choose Insert>Chart, click on Templates, and select the chart type. Then start inputting data. As soon as you get past the 6th color, Office starts using those damn Microsoft default darker and lighter variations instead of the colors you set! This is Authoritarian Helpfulness at its worst!

What You Designed
Chart template colors as designed
What Office Gives You
Chart template colors as created by Office

Yes, you can fix this. Reapply the template by choosing Change Chart Type>Templates and reselecting the template. The colors are corrected to the design specs. But you shouldn’t have to do this! And now that you’ve found this article, you don’t have to.


Understanding Chart Template Colors

Let me back up a bit, and explain how chart templates are structured. The top level of the XML consists of [Content_Types].xml, a _rels folder for documenting the relationships of XML parts and a chart folder holding the good bits.

Inside the chart folder, we find chart.xml, which holds most of the formatting that we created when we made the custom chart. Chart.xml contains definitions that contain the intended series color. The colors in chart.xml get set when you save the file as a chart template, and they get used when you apply the template to an existing table. But they are not used when you create a new chart.

Each series is numbered starting with 0, so this is the series for the 7th color. For the first six, the color is set to an accent color. Starting with the 7th, the color fills are in RGB/hexadecimal. Here’s a sample:

<c:ser>
  <c:idx val="6"/>
  <c:order val="6"/>
  <c:spPr xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
    <a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
      <a:srgbClr val="9AA4AF"/>
    </a:solidFill>
    <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" w="19050">
      <a:noFill/>
    </a:ln>
    <a:effectLst xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"/>
  </c:spPr>
  <c:cat>
    <c:numRef/>
  </c:cat>
  <c:val>
    <c:numRef/>
  </c:val>
  <c:extLst>
    <c:ext uri="{C3380CC4-5D6E-409C-BE32-E72D297353CC}" xmlns:c16="http://schemas.microsoft.com/office/drawing/2014/chart">
      <c16:uniqueId val="{00000006-910C-4CE6-8943-18D1A951583A}"/>
    </c:ext>
  </c:extLst>
</c:ser>
 

In addition, there are subfolders for charts, media, theme, and _rels. The media folder only holds a BMP file that is used for thumbnail display in Office 2007 and 2010, but not in later versions. The _rels folder is for relationships of XML parts. The theme folder contains themeOverride1.xml. As the name suggests, this is a theme that overrides the theme of an Office file that hosts it. This allows you to use a chart template in any Office document while retaining the original design appearance. Finally, we have the charts folder, which has the XML part we’re looking for.

This folder contains 2 files: style1.xml, containing the formatting for all the chart parts and colors1.xml, with a list of default fill colors and transforms fo apply to those colors. Colors1.xml is the file that supplies the colors when a new chart is created from the template. Here’s what colors1.xml looks like:

<cs:colorStyle xmlns:cs="http://schemas.microsoft.com/office/drawing/2012/chartStyle" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" meth="cycle" id="10">
  <a:schemeClr val="accent1"/>
  <a:schemeClr val="accent2"/>
  <a:schemeClr val="accent3"/>
  <a:schemeClr val="accent4"/>
  <a:schemeClr val="accent5"/>
  <a:schemeClr val="accent6"/>
  <cs:variation/>
  <cs:variation>
    <a:lumMod val="60000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="80000"/>
    <a:lumOff val="20000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="80000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="60000"/>
    <a:lumOff val="40000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="50000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="70000"/>
    <a:lumOff val="30000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="70000"/>
  </cs:variation>
  <cs:variation>
    <a:lumMod val="50000"/>
    <a:lumOff val="50000"/>
  </cs:variation>
</cs:colorStyle>

On a newly inserted chart, an Office program will use the 6 colors in turn. Then it will cycle through them again, applying the a:lumMod val=”60000″ transform. LumMod modifies the luminance, turning each accent color to a dark version. The chart will follow this pattern instead of using the colors you designed for the extra data series. If you reapply the chart type to the chart, then it will update with your designed colors from chart.xml


Fixing Chart Template Colors

My first step was to read the Microsoft specs for cs:colorStyle. Under section 2.8.3.2 CT_ColorStyle, it reads: “The total set of colors is all contained colors repeated each time with each variation applied. A color style can contain 6 colors and 7 variations. This yields a total of 42 colors with the first 6 having the first variation applied, the second 6 having the second variation applied and so on.” Not promising, we really need more than 6 colors here.

After many fruitless experiments, I decided to see if I could add extra colors anyway:

<cs:colorStyle xmlns:cs="http://schemas.microsoft.com/office/drawing/2012/chartStyle" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" meth="cycle" id="10">
  <a:schemeClr val="accent1"/>
  <a:schemeClr val="accent2"/>
  <a:schemeClr val="accent3"/>
  <a:schemeClr val="accent4"/>
  <a:schemeClr val="accent5"/>
  <a:schemeClr val="accent6"/>
  <a:srgbClr val="9AA4AF"/>
  <a:srgbClr val="C2C6C9"/>
  <a:srgbClr val="B76646"/>
  <a:srgbClr val="E2C2B5"/>

To my astonishment, it worked! I could create a new chart and insert 10 dataseries with each displaying the designed color. No need to reapply the template! Who would ever have dreamt that Microsoft documentation has a mistake in it! ;-D

The takeaway is that to create a many-colored chart template that works as expected under all circumstances, you need to set the extra color values manually in colors1.xml.

Edit OOXML with VBA – Cool Code

For Office users, the closest thing to a “programming language of the people” is VBA. It’s not too hard to get started, there are gobs of help information from a good search, and the results are immediate. But VBA’s abilities haven’t expanded as its environment has changed. This has become abundantly clear with current versions of Office, where task panes and the Windows-version Backstage haven’t been included in the VBA object model. Many want to edit OOXML with VBA, but Microsoft prefers to shuffle you off to the Open XML SDK programmed with C# to do that job.

Fortunately, we’re on the case at Brandwares. We collaborated with programmer Jan Karel Pieterse to develop a PowerPoint version of his macro set that edits Excel OOXML. We’re making this freely available as a download so you can get the benefit of this.

Let me apologize to my macOS readers. I really try to provide solutions that work cross-platform, but this macro set relies on Windows system calls.

I’ll be honest, this isn’t the most elegant OOXML editing solution. The macro set unzips the OOXML to its component files, gives you the opportunity to edit the XML using VBA string manipulation, then rezips the OOXML to a usable PowerPoint file. The unzip/rezip operations are fairly slow, especially with large files. It’s not something you can use in a real-time editing situation.

One of Brandwares’s specialties is converting legacy presentations to new themes/templates. Often, there are OOXML mismatches that make reused slides retain artifacts or formatting from the old decks. We solve these issues with macro convertors that take a folder full of old decks and transform them into new presentations with new branding. This macro set is great for that.


Edit OOXML with VBA: a Peek Under the Hood

THe file contains 3 VBA modules and 1 class module. Module modConvert is the only one you need to modify. In it, Sub MainVBAOperations does the actual work of opening files, saving as a work file, calling the XML process, saving the modified file and deleting the work file. This is also where you would do any additional VBA processing. As one example, after you modify the XML of a slide master or layout, you have to reset the slide based on it to display the changes. MainVBAOperations is where you would do this.

The other Sub is ProcessXML. Here’s where you unzip the file being modified, open different XML parts for find and replace processes, followed by a rezip of all files back to a working file. The sample code in this module shows a typical revision to the idx numbers of placeholders, a common requirement of legacy presentation conversions and one that can’t be done with the PowerPoint interface.

The module modDisplay, by Shyam Pillai, provides the PowerPoint equivalent of the Application.Screenupdating command that exists in Word VBA. Useful to prevent the screen flashing and jumping as files are processed, it also helps speed code execution. modUNC by Randy Birch, assists with file management.

Jan Karel Pieterse wrote the class module clsEditOpenXML that does the heavy lifting of unzipping and rezipping the document to be modified and reading and writing the XML.

As noted in the code, You are free to use this code within your own applications, add-ins, documents etc but you are expressly forbidden from selling or otherwise distributing this source code without prior consent. This includes both posting free demo projects made from this code as well as reproducing the code in text or html format.


Converting Legacy Presentations

We use often use this macro set to update old (legacy) presentations with a new design. Successful updating requires meeting 5 criteria, please read this article for more details: Legacy Slides – Best Practices. As noted on that page, the 5th requirement is that placeholder idx numbers in the OOXML must match on the old and new layouts. There’s nothing in PowerPoint’s interface that allows you to set idx number, but this macro set allows you to do just that. The pre- and post-processing sections of the macros allow you to set the the other 4 parameters for each slide layout. Click here to download it.

The following advice is particular to presentation conversion. It’s routine that slide masters and layouts will be changed in that process. Then, to apply those changes to the actual slides in a presentation, the slides must be reset, as if you pressed the Home>Reset button in a presentation. Resetting slides wipes out character-based formatting. If a user has applied bold or italic or an underline to particular text, that all will disappear. It’s important to notify your client of this. To make an exact update would require a painstaking construction of a multi-dimensional array for each placeholder on each slide that would record all character-based formatting, then restore it after the update, for which you would have to charge many times as much as for the basic conversion work.

Brandwares is a world leader in presentation updating and conversion. We’re available for presentation assessments, to identify potential problems. We have multiple techniques for seamless re-use of legacy presentations. Contact us when you’re redesigning to ensure your new template will reuse your old slides without a hiccup.

Office Automation with AppleScript Reference – Cool Code

For Office for Mac users, AppleScript remains a useful tool in scripting Office documents. VBA for Mac has poor links to the operating suystem. In Windows, a programmer can easily make system calls to the OS. In macOS, the analogous system calls are not documented by either Apple or Microsoft. A few intrepid programmers have posted about macOS equivalents, but the information is sporadic and very hard to find. macOS’s built-in AppleScript can solve many of these issues, so I’m posting the best available Office AppleScript Reference.

This is old Microsoft documentation from 2008 that they’ve removed from their site. I don’t guarantee this, or offer support, I’m just posting it to make it available to interested programmers.

Office AppleScript Reference: MacScript vs AppleScriptTask

Ron de Bruin is a European programmer and MVP (Microsoft Valued Professional) who has written lots of good information about programming Office on a Mac. While he writes mostly about Excel, VBA is so similar across Office that most of the information also applies to Word and PowerPoint. Here’s his page on using the AppleScriptTask command in Office 2016 and later to call AppleScripts from a VBA macro: AppleScriptTask in Mac Office 2016 or higher.

Office AppleScript Reference: Conditional Compilation

To create a macro that can run on different versions of Office for Mac, or on both Windows and Mac, you’ll need to use conditional compilation. This uses #If – #Else – #End if statements to run a set of commands only in certain conditions. Here is Microsoft’s page on conditional statements for different Mac versions: Differentiate between Office for Mac versions at compile time. This MS reference page shows all available conditional compiler constants: Compiler constants.

Click here to download: Office AppleScript Reference.

Another useful reference for AppleScript automation of Office is the online article Moving from Microsoft Office VBA to AppleScript by Paul Berkowitz. This is a full-length book with comparisons between VBA and AppleScript, plus many real-world example scripts. It was originally published in 2007, in anticipation of Office 2008. Unfortunately, the downloadable PDF they promise is no longer available.