VBA to save from template dotm to VBA enabled file docm

[hbelal]

I have VBA enabled template that will be used by the team as report template. i.e. teammates will use this file to generate their files without overwriting original file. Now the vba that I am looking for should in naming the file and saving it a docm (because there is another code which user should run just before printing) to a specific folder. I created following code but I keep getting following error:

" Rune-Time error '4198': Command failed"

Looking for your help please in fixing this issue.

Sub SaveReport()
Dim Customer As String, Contract As String, ReportDate As String, strFilename As String, Temp_Folder As String
Temp_Folder = "\\FSP-FS\Customer Files\ZZZ\"
Customer = ThisDocument.SelectContentControlsByTitle("Customer_Name")(1).Range.Text
Contract = ThisDocument.SelectContentControlsByTitle("ContractNo")(1).Range.Text
ReportDate = ThisDocument.SelectContentControlsByTitle("Report_Date")(1).Range.Text
strFilename = Temp_Folder & Customer & " - " & Contract & " - " & ReportDate & ".docm"
ThisDocument.SaveAs2 FileName:=strFilename, FileFormat:=Word.WdSaveFormat.wdFormatFlatXMLMacroEnabled
End Sub

[macropod]

Provided the document is saved to a folder from which it can access its template, there is no need to use the docm format; a docx file can just as easily access the template's macros. Moreover, it's not apparent from your code whether it is the template or a document created from it whose content controls have been updated; the error suggests it's the latter whereas you're querying the former.

[hbelal]

Provided the document is saved to a folder from which it can access its template, there is no need to use the docm format; a docx file can just as easily access the template's macros.

I am not sure how this is possible since my understanding that once I save the file as docx, I will lose all macros stored before with the template. The idea behind me saving the file as docm is the following, user will use 2 macros as following:
First: When fill report header (customer name, date and contract number), he should run first macro (the one I am requesting here) in order for MS Word to save the report and name it as combination of previous 3 fields.

Second: Later and when user finalizes his work on the report, he should run another macro which will hide all hidden text.

Now, when user opens template and run first macro, file should be saved as docm so he can later run the second code within same file generated from my original template.

Moreover, it's not apparent from your code whether it is the template or a document created from it whose content controls have been updated; the error suggests it's the latter whereas you're querying the former.

I didn't get your point. I have attached sample file which the fields used in my code. File name is reading fields with no error (or this is what I see in the Locals window when testing the code manually through F8).

[macropod]

Provided the document is saved to a folder from which it can access its template, there is no need to use the docm format; a docx file can just as easily access the template's macros.

I am not sure how this is possible since my understanding that once I save the file as docx, I will lose all macros stored before with the template.

Although a docx file cannot contain any macros, it can still employ any that are in its attached template. Try it for yourself.

Moreover, it's not apparent from your code whether it is the template or a document created from it whose content controls have been updated; the error suggests it's the latter whereas you're querying the former.

I didn't get your point. I have attached sample file which the fields used in my code. File name is reading fields with no error (or this is what I see in the Locals window when testing the code manually through F8).

Your attachment sheds no light on the matter as it doesn't include any of the code that is populating the content controls; if this is being done manually, the user is doubtless creating a new document and is not editing the template you're trying to save.

[gmayor]

There are other potential issues involved here also. If the template is being used as Microsoft intended, then you should be creating new documents from the template. That document is 'ActiveDocument' and not 'ThisDocument' (which is the template). Then there are the issues of whether the content controls have been completed and whether the resulting filename contains illegal filename characters. Names can certainly do so and dates are a minefield in some languages, so you need to address both of those e.g. as shown below.

As Paul asserts, the macros remain in the template and as long as the document has access to the template, the macros will be available to the document. The person creating the report will thus have those macros and the recipient of the report should not require them.

Sub SaveReport()
'Graham Mayor - http://www.gmayor.com - Last updated - 05 Nov 2018 
Dim Customer As String, Contract As String, ReportDate As String, strFilename As String, Temp_Folder As String
    Temp_Folder = "\\FSP-FS\Customer Files\ZZZ\"
    With ActiveDocument
        If .SelectContentControlsByTitle("Customer_Name")(1).ShowingPlaceholderText = True Then
            Beep
            MsgBox "Complete Customer Name"
            GoTo lbl_Exit
        End If
        Customer = .SelectContentControlsByTitle("Customer_Name")(1).Range.Text
        If .SelectContentControlsByTitle("ContractNo")(1).ShowingPlaceholderText = True Then
            Beep
            MsgBox "Complete Contract no"
            GoTo lbl_Exit
        End If
        Contract = .SelectContentControlsByTitle("ContractNo")(1).Range.Text
        If .SelectContentControlsByTitle("Report_Date")(1).ShowingPlaceholderText = True Then
            Beep
            MsgBox "Complete Date"
            GoTo lbl_Exit
        End If
        ReportDate = .SelectContentControlsByTitle("Report_Date")(1).Range.Text
        strFilename = Customer & " - " & Contract & " - " & ReportDate
        strFilename = CleanFilename(strFilename)
        strFilename = Temp_Folder & strFilename & ".docx"
        .SaveAs2 FileName:=strFilename, FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument
    End With
lbl_Exit:
    Exit Sub
End Sub

Private Function CleanFilename(strFilename As String) As String
Dim arrInvalid() As String
Dim lng_Index As Long
    'Define illegal characters (by ASCII CharNum)
    arrInvalid = Split("9|10|11|13|34|42|47|58|60|62|63|92|124", "|")
    'Remove any illegal filename characters
    CleanFilename = strFilename
    For lng_Index = 0 To UBound(arrInvalid)
        CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))
    Next lng_Index
lbl_Exit:
    Exit Function
End Function

[hbelal]

Although a docx file cannot contain any macros, it can still employ any that are in its attached template. Try it for yourself.

Thanks for your support and follow up. Is this feature exclusive for Word or I can apply same concept in Excel?

[hbelal]

There are other potential issues involved here also. If the template is being used as Microsoft intended, then you should be creating new documents from the template. That document is 'ActiveDocument' and not 'ThisDocument' (which is the template). Then there are the issues of whether the content controls have been completed and whether the resulting filename contains illegal filename characters. Names can certainly do so and dates are a minefield in some languages, so you need to address both of those e.g. as shown below.

Thanks Gmayor for the support. Code is working perfectly. I made some changes to make customer's name proper (since it is all capital in the template) and I changed content control conditions to check if default values are displayed then msgbox will be executed.

Issue is solved