Consulting

Results 1 to 7 of 7

Thread: VBA to save from template dotm to VBA enabled file docm

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    14
    Location

    Question VBA to save from template dotm to VBA enabled file docm

    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
    Last edited by macropod; 11-04-2018 at 02:46 AM. Reason: Repaired code structure

  2. #2
    VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,442
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Posts
    14
    Location
    Quote Originally Posted by macropod View Post
    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.


    Quote Originally Posted by macropod View Post
    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).
    Attached Files Attached Files

  4. #4
    VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,442
    Location
    Quote Originally Posted by hbelal View Post
    Quote Originally Posted by macropod View Post
    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.
    Quote Originally Posted by hbelal View Post
    Quote Originally Posted by macropod View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Posts
    14
    Location
    Quote Originally Posted by macropod View Post
    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?

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Posts
    14
    Location
    Quote Originally Posted by gmayor View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •