template and bookmarks as input. This error i am getting randomly and not at will in the middle of document generation.
Code:
Dim I As Integer, j As Integer, k As Integer, l As Integer, m As Integer, docCount As Integer, bookmarksCount As Integer
Dim n As Integer
Dim oWord As Word.Application
Dim oDoc As Document
Dim oSingleDoc As Document
Dim sTemplateUrl As String, sDocStorageURL As String, sDocumentClosed As String, sProtectPassword As String
Dim szServer As String, szUsername As String, szPassword As String, szRemoteFile As String, _
szRefNum As String, szSID As String, szConnect As String, szPortNbr As String
Dim oDocuments As Object, oDocument As Object, oTemplate As Object, oBookmarks As Object, _
oBookmark As Object
Dim sDocument As String, sBookmarkName As String, sBookmarkType As String, _
sBookmarkValue As String, sRowValue As String, sDocUrl As String
Dim sRows() As String, sColumns() As String
Dim rng As Word.Range
Dim frmFields As Word.FormFields
Dim frmField As Word.FormField
Dim rngStart As Integer
Dim rngEnd As Integer
Dim drive As String
Dim frmFieldEnable As Boolean
Dim oBooks As Object
Dim oBook As Object
Dim oContent As Object
On Error GoTo ErrorHandler:
drive = Environ$("SystemDrive")
sProtectPassword = oXML.getElementsByTagName("PasswordProtected").Item(0).Text
Set oDocuments = oXML.getElementsByTagName("Documents").Item(0)
docCount = oDocuments.ChildNodes.Length
sTemplateUrl = drive & oXML.getElementsByTagName("DocTemplateURL").Item(0).Text
sDocStorageURL = oXML.getElementsByTagName("DocStorageURL").Item(0).Text
sDocumentClosed = oXML.getElementsByTagName("DocumentClosed").Item(0).Text
Set oWord = New Word.Application
oWord.Visible = True
Set oDoc = oWord.Documents.Add(sTemplateUrl)
For I = 0 To docCount - 1
Set oDocument = oDocuments.ChildNodes(I)
Set oBooks = CreateObject("MSXML.DOMDocument")
Set oBook = oBooks.documentElement
Set oBook = oBooks.createElement("Books")
oBooks.appendChild oBook
Set oBookmarks = oDocument.getElementsByTagName("Bookmarks").Item(0)
bookmarksCount = oBookmarks.ChildNodes.Length
For n = 0 To bookmarksCount - 1
Set oBookmark = oBookmarks.ChildNodes(n)
Set oContent = oBooks.createElement(oBookmark.getElementsByTagName("Name").Item(0).Text)
oContent.Text = oBookmark.getElementsByTagName("Value").Item(0).Text
oBook.appendChild oContent
Next
Set oTemplate = oDocument.getElementsByTagName("Template").Item(0)
sTemplateUrl = drive & oTemplate.getElementsByTagName("URL").Item(0).Text
Set oSingleDoc = oWord.Documents.Open(FileName:=sTemplateUrl)
oSingleDoc.Activate
Set rng = oSingleDoc.Range
Set frmFields = rng.FormFields
If frmFields.Count > 1 Then
For j = 1 To rng.Bookmarks.Count
If (oBook.SelectNodes("//Books/" & UCase(rng.Bookmarks(j).Name)).Length > 0) Then
sBookmarkName = rng.Bookmarks(j).Name
sBookmarkValue = oBook.getElementsByTagName(UCase(rng.Bookmarks(j).Name)).Item(0).Text
oWord.Selection.GoTo -1, 0, 0, Name:=sBookmarkName
Set frmField = frmFields.Item(sBookmarkName)
frmFieldEnable = frmField.Enabled
frmField.Enabled = True
If frmField.Type = wdFieldFormTextInput Then
frmField.Result = sBookmarkValue
Else
If frmField.Type = wdFieldFormCheckBox Then
If Len(sBookmarkValue) > 0 Then
If StrComp(sBookmarkValue, "Y", vbTextCompare) <= 0 Then
frmField.CheckBox.Value = True
Else
frmField.CheckBox.Value = False
End If
End If
End If
End If
frmField.Enabled = frmFieldEnable
End If
Next
Else
For j = 1 To rng.Bookmarks.Count
If (oBook.SelectNodes("//Books/" & UCase(rng.Bookmarks(j).Name)).Length > 0) Then
sBookmarkValue = oBook.getElementsByTagName(UCase(rng.Bookmarks(j).Name)).Item(0).Text
oWord.Selection.GoTo -1, 0, 0, Name:=rng.Bookmarks(j).Name
If (InStr(sBookmarkValue, "|") > 0) Or (InStr(sBookmarkValue, "?") > 0) Then
sRows = Split(sBookmarkValue, "|")
For k = LBound(sRows) To UBound(sRows)
sColumns = Split(sRows(k), "?")
For l = LBound(sColumns) To UBound(sColumns)
oWord.Selection.TypeText (sColumns(l))
oWord.Selection.MoveRight Unit:=wdCell, Count:=1, Extend:=0
Next
Next
Else
oWord.Selection.TypeText (sBookmarkValue)
End If
End If
Next
End If
sDocUrl = GetPath() & "\temp.doc"
oSingleDoc.SaveAs (sDocUrl)
oSingleDoc.Close
Set oBooks = Nothing
oDoc.Activate
oWord.Selection.InsertFile FileName:=sDocUrl
If (I + 1 < docCount) Then
oWord.Selection.EndKey 6, 0
oWord.Selection.InsertBreak 2
oWord.Selection.GoTo rngEnd
End If
Next
oDoc.Protect wdAllowOnlyFormFields, Null, sProtectPassword
szServer = oXML.getElementsByTagName("Servername").Item(0).Text
szUsername = oXML.getElementsByTagName("Username").Item(0).Text
szPassword = oXML.getElementsByTagName("Password").Item(0).Text
szRemoteFile = oXML.getElementsByTagName("RemoteFile").Item(0).Text
szPortNbr = oXML.getElementsByTagName("PortNumber").Item(0).Text
oWord.Run "SetParams", szServer, szUsername, szPassword, szRemoteFile, szPortNbr
If StrComp(sDocumentClosed, "True", vbTextCompare) = 0 Then
oDoc.Close
oWord.Quit
Else
oWord.Visible = True
End If
Set rng = Nothing
Set oSingleDoc = Nothing
Set oDoc = Nothing
Set oWord = Nothing
ErrorHandler:
If Len(Err.Description) > 0 Then
MsgBox "Automation Error: " & Err.Description & " " & Err.Source & " " & Err.Number
oWord.Quit
Set rng = Nothing
Set oDoc = Nothing
Set oSingleDoc = Nothing
Set oWord = Nothing
End If
End Sub