Open Word template hyperlink as a new Word document (.docx), not as the original template (.dotx)

[SaraWitch]

Hello peeps,

I have various buttons that I have hyperlinked to Word templates. When opening the Word templates from File Explorer (or other Word hyperlinks), these open as a new document ("Document 1"), as they should. However, from the Excel hyperlinks, they open as the original template. I want to preserve the integrity of the original template (GDPR risk!).

I found a solution of creating a shortcut of the template and hyperlinking that, but this no longer works - it still opens the template. I've also found a couple of VBA codes for links within a cell (which don't work for me), but not for a button.

Any help would be greatly appreciated, thank you

 

[John_w]

When you say buttons, do you mean Shapes? If so, see if this approach works for you - try it on one of the shapes.

1. Remove the link from the shape (see note below).

2. Edit the shape and set its Alt Text to the full name of the Word template file, including the ".dotx" extension. The folder path is optional - if omitted, the code below expects the template to be in the same folder as the workbook, but the code could be changed to look for the template in any folder.

3. Paste the following code into a standard module in the Excel workbook (.xlsm file):

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If


Public Const wdNewBlankDocument = 0


Public Sub Open_Word_Doc_From_Template()

    Dim WordApp As Object 'Word.Application
    Dim WordDoc As Object 'Word.Document
    Dim templateFile As String
    
    templateFile = ActiveSheet.Shapes(Application.Caller).AlternativeText
    
    Set WordApp = Nothing
    On Error Resume Next
    Set WordApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If WordApp Is Nothing Then
        Set WordApp = CreateObject("Word.Application")
    End If
    WordApp.Visible = True
    
    If InStr(templateFile, "\") = 0 Then templateFile = ThisWorkbook.Path & "\" & templateFile
    
    Set WordDoc = WordApp.Documents.Add(template:=templateFile, NewTemplate:=False, DocumentType:=wdNewBlankDocument)
    SetForegroundWindow WordDoc.Windows(1).hwnd
    
    Set WordApp = Nothing
    
End Sub

4. Assign the Open_Word_Doc_From_Template macro to the shape.

Note - You have to remove the link (Step 1) because it takes precedence over the assigned macro (Step 4), stopping it from being run. Removing the link means you lose the screentip when you hover over the shape.

 

[SaraWitch]

I am continually amazed and grateful at the expertise and skill on this forum!

This worked perfectly (and for all my 'quick access buttons' (yes, shapes)) - thank you so very much, @John_w. This is a game-changer!