How to get document name from Word merge document after merging in VBA

Question

Here is my scenario: User merges a Word document, I have a button on the Quick Access toolbar which execute a macro which uses a Shell Execute to spawn an application passing it a parameter.

This works, however the parameter I need is the name of the Word document. When I query the active document it is called, "Form Letters."

Is there a way to get the Word document (template) name in VBA code after the document has merged? I know Word changes the name after it merges, I need the Word document name that contains the merge fields.

Const SW_SHOW = 1
Const SW_SHOWMAXIMIZED = 3

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub RunYourProgram()
    l = Len(ActiveDocument)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' The line below retrieves name, "FORM LETTER"(strips off .doc)
    ' rather than name of Word document template
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Doc_Name = Mid(ActiveDocument, 1, l - 4)
    Dim RetVal As Long
    On Error Resume Next
    RetVal = ShellExecute(0, "open", "M:\gendoc\FG_To_ECF.exe", _
        Doc_Name, "c:\Certificates", SW_SHOWNORMAL)
End Sub

Rodrigo V Rodrigo V · Accepted Answer · 2015-06-16T19:18:39

I am not sure if I understood correctly your question.

If you are trying to retrieve the file name of the current Word document that is open, you must use ActiveDocument.FullName (which includes the full path) or ActiveDocument.Name (just the file name including its extension).

In the example you gave the code would as below:

Const SW_SHOW = 5
Const SW_SHOWMAXIMIZED = 3
Const SW_SHOWNORMAL = 1

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub RunYourProgram()

    Dim RetVal As Long

    On Error Resume Next
    RetVal = ShellExecute(0, "open", "M:\gendoc\FG_To_ECF.exe", ActiveDocument.Name, "c:\Certificates", SW_SHOWNORMAL)

End Sub

UPDATE:

Ok @KentE, I think that now I got what you need. Unfortunately the final document generated by the Mail Merge do not hold the name of the original file anywhere. I see two possible solutions for that:

1) You can save the original document as a real template. In your case the CS32.doc must be saved as .dot or .dotx extension. So then, when you open the CS32.dotx and run the Mail Merge the generated “Form Letters” document will contain the link to its original template. Which in this case is the CS32.dotx. In the VBA you can retrieve the name of the template via ActiveDocument.AttachedTemplate

2) Or you may change the CS32.doc to hold a custom property. Let’s say that you create the custom property “OriginalName” with the value “CS32.dotx” (Can be any value). So then, when you open the CS32.doc and run the Mail Merge the generated “Form Letters” document will contain the same custom properties of the original document. Which in this case, the “OriginalName” property will contain the value you manually entered. In the VBA you can retrieve the name of the custom property via ActiveDocument.CustomDocumentProperties("OriginalName")

I hope it helps. Regards.

How to get document name from Word merge document after merging in VBA

1 Answers