Using Macros in a SharePoint Library Template

SharePoint Logo

SharePoint libraries are a great way to manage template-driven documents. However there are some wrinkles if you’d like to manage macro-enabled documents. This article will provide an overview of those issues and a solution that you may find useful.

The Basics

In SharePoint, a library can hold, with a few restrictions, any type of document. By default the “New Document” button on a library will allow you to upload a file or create a new blank document based on default templates (you can set the default type type – Word, Excel, etc. – when creating the library). You can change this to a custom template in the Document Library Settings, “Advanced Settings” page (below).

SharePoint, Document Libraries, Document Templates

Any standard Microsoft Office document can be used as a template. As of Office 2007, this will most likely be “template” extension (*.dotx for Word, *.xltx for Excel and *.potx for Powerpoint) or a normal document (*.docx, *.xlsx or *.pptx). You can also use macro-enabled Word and Excel documents (*.docm or *.xlsm).

In any case, once you configure a custom template (or, if Allow management of content types? is set to “Yes”, multiple custom templates), pressing the “New Document” button will automatically open a copy of that template for use. You can then return to the “Advanced Settings” page at any time to edit or replace the document template.

Macro Enabled Documents

For security and visibility reasons, any Word or Excel document that contains macros will be saved in the *.docm or *.xlsm formats. While these can be used as document library templates, there are issues.

Most of the issues are relatively minor and stem from the fact that these macro extensions are prone to security warnings and are often flagged by email and malware protection systems. Forgiving those, there is one absolutely no-go issue for me: collaboration features are completely unavailable in these documents. You’ll be unable to collaboratively review, edit or manage documents using these extensions.

On the desktop, you could solve such issues by storing macros in the global template (normal.dotm, by default) and this will work in SharePoint as well. Sort of. When a document is opened from SharePoint it will attempt to use the global template of the local machine. This means that any macros would need to be added to the local templates of every single user of the library; something that normally isn’t a workable solution.

What’s needed is a solution that eliminates the problems of using the macro templates, but still allows for central management without custom local client configuration.

An Example and a Solution

Recently, I had the requirement to create a library of log documents in Word format (*.docx). The documents contain chronological notations and other information about recurring problems. I created the document template and then configured it as the default template for a new SharePoint library. This worked well, as far as it went.

I later decided to provide a simple method for authors to add a standardized date/time stamp via a keyboard shortcut. The following simple macro fit the bill nicely:

Sub TimeStamp_DateTime()
     Selection.InsertDateTime _
     DateTimeFormat:="DD/MM/YYYY HH:mm", _
     InsertAsField:=False, _
     InsertAsFullWidth:=False, _
     DateLanguage:=wdEnglishUS, _
     CalendarType:=wdCalendarWestern
End Sub

After much experimentation and pain, this is the process I used to make that macro available. All of the following were done in Word 2010 and SharePoint 2010 Standard:

I created a new macro-enabled document, Global_CustomMacros.docm (the name doesn’t matter), to host the macro. I uploaded this document to the root of the SharePoint Style Library. (For more about why I use the Style Library, see my article, “SharePoint Scripting Basics: Master Pages, Caching and Loading Scripts“.)

Next, I configured the shortcut. Warning: this process is significantly more complex than seems to be necessary:

  1. Open your *.docm for editing.
  2. Choose “FILE” on the ribbon menu, then “Options” and finally click “Customize Ribbon” from the left-hand menu.
  3. Within the dialog, at the bottom of the left column, find Keyboard shortcuts and press “Customize…” to open the Customize Keyboard dialog (pictured).SharePoint, Document Libraries, Customize Keyboard
  4. In the Categories area, scroll to near the bottom and select “Macros”.
  5. Near the bottom of the dialog, change Save changes in to the name of the current file.
  6. Select the name of your macro from the list.
  7. Mouse click into the Press new shortcut key field to give the field focus.
  8. Press the shortcut key sequence that you’d like to use. I used Alt+Shift+S, which happily doesn’t conflict with any existing shortcuts (the dialog will tell you if your selection is currently assigned).
  9. Press “Assign”, then “Close”, then “OK” to save the shortcut.
  10. Save the template.

Authors can now use the keyboard shortcut to add a timestamp to the current cursor location.

Finally, we need to link the list’s document template to the macro template. You could have also done this prior to uploading the template to the list. To do this in Word 2010:

  1. Go to the document library settings and click “Advanced Settings”.
  2. Click the link to “Edit Template” in the Document Template area and the template will open in Word.SharePoint, Document Libraries, Templates and Add-ins
  3. Choose “FILE” on the ribbon menu, then “Options” and finally click “Add-ins” from the left-hand menu.
  4. At the bottom of the resulting dialog, choose “Templates” from the Manage drop-down, then press “Go…” The Templates and Add-ins dialog (pictured right) will open.
  5. In the Document template enter the full Internet path to the macro template and press “Attach…”. Your path may appear similar to this: https://www.myCompany.com/
    mySite/Style%20Library/
    Global_CustomMacros.docm
  6. Press “OK” to complete the action.

This will, finally, make the macro available for use when a new document is created from the template in that library. The documents fully support all collaboration features and can be trusted by the users to eliminate annoying security messages.

One caveat: the macros will only be available when the documents using them are able to find the macro template. In other words, when they’re opened on a system that has access to the SharePoint site on which they’re created/hosted. If transferred to a system or person that lacks access, the macros will not be available.

In Conclusion

To recap, creating a library of macro-enabled, template-driven documents entails:

  1. Create a *.docm file to hold your macros.
  2. Upload this file to your site in a location accessible to all users of the library (I recommend the Style Library).
  3. Optionally, add keyboard shortcut customizations to the *.docm file to make accessing your macros easy.
  4. Create your document template and save it as a *.docx file.
  5. Link your macro file (*.docm) to your document template (*.docx) via the Templates and Add-ins dialog.
  6. Create a new document library and upload your template as the default template for the library.

While this example is simple, the sky is the limit when it comes to macros. Anything from simple task automation to complex business processing is possible. You can even create macros that interact directly with SharePoint, something I discuss in another article, “Accessing SharePoint Lists with Visual Basic for Applications“.

I hope you’ve found this useful and will consider sharing any new information you may find!

9 Comments

Add a Comment
  1. Hi, I’m following your process to have a permanently active macro template, using SP2013 and Word 2013, so some of the options are a little different. So, I’ve gone to File > Options > Addins > Manage Addins > Templates (brings me to step 4 of your process.) Then when I paste in the URL, and click attach I get the error “Word cannot open this document template (C:\Users\…docm)”. Where have I gone wrong?

    1. What URL are you entering? The error message (“C:Users…docm”) isn’t a local path (that would be “C:\”) or a SharePoint path (that would be a full URL, such as “http://…”).

      The URL that’s needed is the SharePoint URL where you’ve placed the template file. This location needs to be accessible by the user/machine where the new document will be opened.

  2. Hello,

    Great Post!!!!
    I’ve tried your method but I get a strange outcome my “Word” freezes and crashes when I’m attaching the template. I’m using SharePoint 2013 and Word 2010.

  3. Hi Kiwidust,
    I was able to get this working. Looks like it was an issue in our environment.
    We are now looking to do something a bit more unique. The requirement is to assign unique numbering within in a document. The number would be compiled of two parts, the first is that our SharePoint Site has Document ID enabled so each document has a unique number. We would like that number and append it with a .10 (or something of this sort) and increase the number every time the macro is run. We are able to accomplish this out side of SharePoint. We are having a bit of a hip cup getting the Document ID (Selection.TypeText ActiveDocument.ContentTypeProperties(“Document ID Value”)) in the template that we are using.

    Do you have any suggestions?

  4. Hi. Worked great but now it doesn’t. It won’t trigger a new document based on the original template. I created the docx via a save as of the docm. It won’t generate a new file off the template although it appears to run macro’s from the original template. I want new files generated off the template?

  5. Combo answer, You need to set the site content to read only and critical element is to set the library to open locally such that is generates a new file on create using windows explorer settings when triggered.

  6. This Post is a lifesaver! Thank you!

    I had to change the steps a bit because they were not working in my environment. I’m not sure why they didn’t work, if any one has an idea please reach out.
    In in Document Library I could not save the Template as a “dotx” I had to save it as a “docm”. When I saved it as a dotx I could not attach the document from the style library. I spend a few hours trying to figure it why it didn’t. When I saved the dotx to docm I was then able to add the macro and it worked as you described. This workaround is working correctly. I did however lose the ability to add a Quick Parts to the Template. Since I not longer have a dotx i’m not able to provide my users with a quick parts.

    1. Happy it helped, but a little confused: the document template (the document to use) should be a “docx” file – with a “c”. A “dotx” file is a “template file” and, yes – there are problems uploading this. The macros would be in a “docm” file – but the users wouldn’t ever access this directly.

      You can use “docm” files in any library, but there are limitations. For me, the breaking point was the lack of collaboration features. I’m not sure, but there’s a good chance that quick parts are also limited when using them. Maybe try changing the template file to a “docx” (a normal word 2007+ document) and see what happens?.

      1. Thanks for the Reply Kiwi!

        In my Document Library the Template is a .docx document and the Document containing the Macro is stored in the Style library as a .docm as in your write up.

        You definitely lose the quick parts feature.

        Any item why i can’t attach the .docm to a dotx document in my Document Library?

Leave a Reply

DepressedPress.com © 2014 Frontier Theme