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!

 

[Misso101]

This is amazing and works perfectly after searching many forums with various solutions that didn't quite fit. THANK YOU!

I am in the midst of setting up a spreadsheet linking to a long variety of forms at the moment, is there a way to allocate the below code to work for all hyperlinks in the document?

Or can I allocate the below macro to each cell with a hyperlink? I'm not sure how to do that as I can't change the alt text on the cell. Clearly a #noob.

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.

 

[Misso101]

This is amazing and works perfectly after searching many forums with various solutions that didn't quite fit. THANK YOU!

I am in the midst of setting up a spreadsheet linking to a long variety of forms at the moment, is there a way to allocate the below code to work for all hyperlinks in the document?

Or can I allocate the below macro to each cell with a hyperlink? I'm not sure how to do that as I can't change the alt text on the cell. Clearly a #noob.

I should add... they are all organised in sub-folders within the same folder that the excel document is in. Is it possible to keep it that way or should they all be in the same folder location? Many many thanks.

 

[radiantclassy]

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.

Geometry Dash​

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.

Works perfectly. Thanks for sharing!

 

[John_w]

This is amazing and works perfectly after searching many forums with various solutions that didn't quite fit. THANK YOU!

I am in the midst of setting up a spreadsheet linking to a long variety of forms at the moment, is there a way to allocate the below code to work for all hyperlinks in the document?

Or can I allocate the below macro to each cell with a hyperlink? I'm not sure how to do that as I can't change the alt text on the cell. Clearly a #noob.

Your request is quite different to the OP's and therefore the code would be different so it's best if you start a new thread and I'll try to help you in that.

I should add... they are all organised in sub-folders within the same folder that the excel document is in. Is it possible to keep it that way or should they all be in the same folder location? Many many thanks.

Yes, this is possible - VBA can search for a file in a folder and its subfolders.

 

[Misso101]

Your request is quite different to the OP's and therefore the code would be different so it's best if you start a new thread and I'll try to help you in that.



Yes, this is possible - VBA can search for a file in a folder and its subfolders.

Thank you! I may have to do that in the future. In the mean time I've created a work around using your golden code where I created no fill shapes and sized them within the cell, and set it to move and size with cells. I've then copied the same descriptive text from the shape into the cell but changed the colour so that it was white. This way my team could still use the sort functions and the links would move with them. They basically look like links. A bit of work! But a lot less work than working out coding haha.

Many many thanks again.