Runtime Error '5941' The Requested Member of Collection Does Not Exist

11k views Asked by At

When running the macro I am getting the following error:

"Runtime Error '5941' The Requested Member of Collection Does Not Exist"

The line below is highlighted in Yellow; This Macro works on the majority of users and the error has only been reported off two users.

Windows(DestTemplateName).Document.Bookmarks("fCustomer").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fCust").Range.Fields(1).Result.Text

Any Help Would Be Much Appreciated.

Sub PopulateBookingForm()

Dim SourceTemplateName As String, DestTemplateName As String

' Set Method Statement Name
SourceTemplateName = ActiveDocument.Name

' Open Heavy Cranes ICO Booking Form
ChangeFileOpenDirectory "\\SERVERSHARE\HCD\HCD General\Templates\"
Documents.Open FileName:= _
    "\\SERVERSHARE\HCD\HCD General\Templates\Heavy Cranes ICO Booking Form.docx", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto, XMLTransform:=""
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting

' Set Haulage Booking Form Document Name
DestTemplateName = ActiveDocument.Name

' ### START FIND AND REPLACE

' Populate Customer(Client)
Windows(DestTemplateName).Document.Bookmarks("fCustomer").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fCust").Range.Fields(1).Result.Text

' Populate Version
Windows(DestTemplateName).Document.Bookmarks("fVersion").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fRevision").Range.Fields(1).Result.Text

' Populate Entered Onto CRM
Windows(DestTemplateName).Document.Bookmarks("fEnteredOntoCRM").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fEnteredOntoCRM").Range.Fields(1).Result.Text

' Populate CRM Opportunity Name
Windows(DestTemplateName).Document.Bookmarks("fCRMOportunityName").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fCRMOportunityName").Range.Fields(1).Result.Text

' Populate Contact Name
Windows(DestTemplateName).Document.Bookmarks("fContactName").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fSiteContact").Range.Fields(1).Result.Text

' Populate Telephone No (Mobile)

If Replace(Windows(SourceTemplateName).Document.Bookmarks("fSiteMobile").Range.Fields(1).Result.Text, ChrW(8194), "") = "" Then
    Windows(DestTemplateName).Document.Bookmarks("fTelephoneNo").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fSiteTel").Range.Fields(1).Result.Text
Else
    Windows(DestTemplateName).Document.Bookmarks("fTelephoneNo").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fSiteMobile").Range.Fields(1).Result.Text
End If

' Populate Fax No(Email)
Windows(DestTemplateName).Document.Bookmarks("fFaxNo").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fSiteFax").Range.Fields(1).Result.Text

' Populate Site Address
Windows(DestTemplateName).Document.Bookmarks("fSiteAddress").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fSiteAddr").Range.Fields(1).Result.Text

' Populate Duration
Windows(DestTemplateName).Document.Bookmarks("fDuration").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fDuration").Range.Fields(1).Result.Text

' Populate Date of Hire/RFW
Windows(DestTemplateName).Document.Bookmarks("fTimeReadyForWork").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("dt1").Range.Fields(1).Result.Text
Windows(DestTemplateName).Document.Bookmarks("fDayDateOfHire").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("dt1").Range.Fields(1).Result.Text

' Populate Inspector
Windows(DestTemplateName).Document.Bookmarks("fFormCompletedBy").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fACHSiteInspector").Range.Fields(1).Result.Text
Windows(DestTemplateName).Document.Bookmarks("fSiteVisitedBy").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fACHSiteInspector").Range.Fields(1).Result.Text
Windows(DestTemplateName).Document.Bookmarks("fMethodStatementBy").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fACHSiteInspector").Range.Fields(1).Result.Text

' Populate CL
Windows(DestTemplateName).Document.Bookmarks("fCL").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fTermsCL").Range.Fields(1).Result.Text

' Populate CH
Windows(DestTemplateName).Document.Bookmarks("fCH").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fTermsCH").Range.Fields(1).Result.Text

' Populate Wires
Windows(DestTemplateName).Document.Bookmarks("fWires").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryWires").Range.Fields(1).Result.Text

' Populate Web Slings
Windows(DestTemplateName).Document.Bookmarks("fWebSlings").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryWebSlings").Range.Fields(1).Result.Text

' Populate Beams
Windows(DestTemplateName).Document.Bookmarks("fBeams").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryBeams").Range.Fields(1).Result.Text

' Populate Chains
Windows(DestTemplateName).Document.Bookmarks("fChains").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryChains").Range.Fields(1).Result.Text

' Populate Shackles
Windows(DestTemplateName).Document.Bookmarks("fShackles").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryShackles").Range.Fields(1).Result.Text

' Populate Other
Windows(DestTemplateName).Document.Bookmarks("fOther").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fAccessoryOthers").Range.Fields(1).Result.Text

' Populate Job Description
Windows(DestTemplateName).Document.Bookmarks("fJobDescription").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fDescOfWorks").Range.Fields(1).Result.Text & vbNewLine & vbNewLine & Windows(SourceTemplateName).Document.Bookmarks("fOperReqACHL").Range.Fields(1).Result.Text

' Populate Other Information
Windows(DestTemplateName).Document.Bookmarks("fOperationByClient").Range.Fields(1).Result.Text = Windows(SourceTemplateName).Document.Bookmarks("fOperReqClient").Range.Fields(1).Result.Text

' ### END FIND AND REPLACE

' Activate New Document
Windows(DestTemplateName).Activate
End Sub

The Macro Basically Copies Some Text From Fields In The Source Document Over To The Destination Document.

1

There are 1 answers

0
ChipsLetten On

This sounds like those users are running your macro when the ActiveDocument does not have the fCust bookmark. You can add this test to your code before you try to use the fCust bookmark:

If Windows(SourceTemplateName).Document.Bookmarks.Exists("fCust") = False Then
    MsgBox "You are not using the correct source document. Failed to find the 'fCust' bookmark in " & SourceTemplateName
    Exit Sub
End If

On a side note, you can probably use a Document object to simplify your code.

Dim sourceDocument As Document
Dim destDocument As Document

Set sourceDocument = ActiveDocument
If sourceDocument.Bookmarks.Exists("fCust") = False Then
    MsgBox "You are not using the correct source document. Failed to find the 'fCust' bookmark in " & sourceDocument.Name
    Exit Sub
End If

Set destDocument = Documents.Open (FileName:= _
"\\SERVERSHARE\HCD\HCD General\Templates\Heavy Cranes ICO Booking Form.docx", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:="")

   destDocument.Bookmarks("fCustomer").Range.Fields(1).Result.Text = sourceDocument.Bookmarks("fCust").Range.Fields(1).Result.Text

... the rest of the code goes here
destDocument.Activate

Post your code to the Code Review part of SE to get a more detailed review of your working code.