CODEWITHSUNDEEP
excelvbamacosms-word
Michael
Michael

Reputation: 13

Excel VBA - Generate New Word Document and Protect for Forms

I have a a macro enabled excel file (note that this is running on a Mac using Office for Mac) which has several saved macros, one of which is to:

  1. Create a new .docx document file from a saved .dotx template file.
  2. Populate bookmarks in the new .docx file with data in the spreadsheet.
  3. Protect the new document to only allow form fields to be edited by the final user.
  4. Save the new document to a specific folder.

The code I current currently works except for point 3, I have used Document.Protect method to apply the required protection but seems to have no effect/doesn't work. There is also no error message shown. See current code below:

Sub GenerateDocType(DocType As String)
    Dim form As Worksheet
    Dim db As Worksheet
    Dim WordApp As Object
    Dim WordDoc As Object
    Dim InPath As String
    Dim OutPath As String
    Dim OutFile As String
    Dim temp As String
    
        Set form = Worksheets("Bookings")
        Set db = Worksheets("Database")
    
        On Error Resume Next
        Set WordApp = GetObject(, "Word.application")
        If Err = 429 Then
            Set WordApp = CreateObject("Word.application")
            Err.Clear
            Application.Wait (Now + TimeValue("0:00:03"))
        End If
        On Error GoTo 0
        Set WordApp = GetObject(, "Word.application")
        WordApp.Visible = True

        f = form.Range("Booking_Ref").Value
        With db.Range("G2", "G" & i)
            Set r = .Find(What:=f)
        End With
    
        InPath = ThisWorkbook.path & Application.PathSeparator & "Templates" & Application.PathSeparator & "Booking Form.dotx"
        Set WordDoc = WordApp.Documents.Add(InPath)
        
        With WordDoc.Bookmarks
             .Item("BookingRef").Range.InsertAfter CStr(db.Cells(r.Row, 7).Value)
             'Have removed other bookmark inserts as these are not relevant to issue, they are similar to above line
        End With
        
        OutPath = ThisWorkbook.path & Application.PathSeparator & "Bookings" & Application.PathSeparator
        OutFile = "Booking Form - " & Replace(form.Range("Booking_Ref").Value, "/", "") & ".docx"

        WordDoc.SaveAs2 OutPath & OutFile
        WordApp.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, noreset:=True, Password:="password"
        'Above line seems to have no effect on new document created
        WordDoc.Save
        WordApp.Quit
End Sub

Any help on how to correct protect the new .docx file would be appricated, not sure if doing this on Mac may be the cause.

Thanks.

Upvotes: 1

Views: 242

Answers (1)

Timothy Rylatt
Timothy Rylatt

Reputation: 7850

It looks as though you are using late binding which means that you cannot use constants and enums from the Word object library, e.g. wdAllowOnlyFormFields. So your code should be:

WordDoc.Protect Type:=2, noreset:=True, Password:="password"

Upvotes: 1

Related Questions