I am using excel to populate a substantial word report, by reference to bookmarks.
The problem is how to look within an enclosing word bookmark, and then delete the bookmark (and text) of any bookmarks that are contained within that first enclosing bookmark.
I have tried various things, but keep struggling to get (I think) the syntax right for looping through the all the word bookmarks that are within a word bookmark range.
As part of this there are various sections, which contain differently formatted lists. The number of entries is not fixed (there is an upper limit for each list). The word document has a bookmark for each entry on the list (e.g. listentry1, listentry2, listentry3 etc, and have it set up to replace the text at each entry. That works brilliantly.
The problem I have is getting rid of the unused 'listentry' bookmarks and text (which includes the paragraph marker) before formatting.
This is necessary as I need the entries to end with a semicolon, with the penultimate entry ending "; and" and the last entry ending with a period (house style - don't ask). This means I have to dynamically amend the list entries once the list is completed as it is not certain which will be the last /penultimate entry - the number of entries being variable. I could do this for individual lists, but given the number of lists, I am looking for a bit of code that be called on to do any list. I have a function that almost does all this as follows (any variables not set up in the function are global and set up elsewhere), the function compiles fine and the module it is in does start with 'option explicit':
Function format_lists()
Dim k As Integer
Dim list_count As Integer
Dim insert_para As Paragraph
Dim para_text As String
Dim bmk As Word.Bookmark
For i = 1 To count_repeat_bookmarks("notelist_bm") 'this is a sub that counts bookmarks with a name increments a number at the end
bookmark_ID_temp = "notelist_bm" & CStr(i) 'set a temp bookmark name based on the name's root with a number increment added
'blank the remaining bookmarks in the list
'***THIS IS WHERE THE ERROR OCCURS
For Each bmk In objDoc.Bookmarks(bookmark_ID_temp).Range
insert_text "", bmk.name 'this is a sub used to change bookmark content - this works fine as a sub
Next bmk
If objDoc.Bookmarks.exists(bookmark_ID_temp) Then
'format the list
With objDoc.Bookmarks(bookmark_ID_temp).Range
.ListFormat.ApplyListTemplate ListTemplate:=objDoc.ListTemplates(2), ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList
'Count the list entries
list_count = .Paragraphs.Count
'add to the last entry in the list
For k = 1 To list_count
Set insert_para = .ListParagraphs(k)
para_text = .ListParagraphs(k).Range.Text
If k < list_count - 1 Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & (";") & vbNewLine
If k = list_count - 1 Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & ("; and") & vbNewLine
If k = list_count Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & (".") & vbNewLine
Next k
End With
End If
Next i
End Function
The amendment of the entries works fine to add the semicolons etc.
The problem is that after population of the initial list, if there are say 3 entries from a list limited to 5 there will be two blank entries with unused bookmarks at each (e.g. named listentry4 and listentry5 etc). I need to delete bookmarks and the text including the paragraph marker which is in the bookmarks from the word list before the formatting occurs.
The entire list is within a bookmark named for example "notelist_bm" (the code is testing on a single list - the idea is the name of the list encasing bookmark is carried to the function each list - I will amend to do this once the single list code works)
Any thoughts on how to delete the word bookmarks (and their enclosed text) that falls within another word bookmark? I have put in bold my latest thought that did not work - this is where the error occurs.
The problem is how to look within an enclosing word bookmark, and then delete the bookmark (and text) of any bookmarks that are contained within that first enclosing bookmark.
I have tried various things, but keep struggling to get (I think) the syntax right for looping through the all the word bookmarks that are within a word bookmark range.
As part of this there are various sections, which contain differently formatted lists. The number of entries is not fixed (there is an upper limit for each list). The word document has a bookmark for each entry on the list (e.g. listentry1, listentry2, listentry3 etc, and have it set up to replace the text at each entry. That works brilliantly.
The problem I have is getting rid of the unused 'listentry' bookmarks and text (which includes the paragraph marker) before formatting.
This is necessary as I need the entries to end with a semicolon, with the penultimate entry ending "; and" and the last entry ending with a period (house style - don't ask). This means I have to dynamically amend the list entries once the list is completed as it is not certain which will be the last /penultimate entry - the number of entries being variable. I could do this for individual lists, but given the number of lists, I am looking for a bit of code that be called on to do any list. I have a function that almost does all this as follows (any variables not set up in the function are global and set up elsewhere), the function compiles fine and the module it is in does start with 'option explicit':
Function format_lists()
Dim k As Integer
Dim list_count As Integer
Dim insert_para As Paragraph
Dim para_text As String
Dim bmk As Word.Bookmark
For i = 1 To count_repeat_bookmarks("notelist_bm") 'this is a sub that counts bookmarks with a name increments a number at the end
bookmark_ID_temp = "notelist_bm" & CStr(i) 'set a temp bookmark name based on the name's root with a number increment added
'blank the remaining bookmarks in the list
'***THIS IS WHERE THE ERROR OCCURS
For Each bmk In objDoc.Bookmarks(bookmark_ID_temp).Range
insert_text "", bmk.name 'this is a sub used to change bookmark content - this works fine as a sub
Next bmk
If objDoc.Bookmarks.exists(bookmark_ID_temp) Then
'format the list
With objDoc.Bookmarks(bookmark_ID_temp).Range
.ListFormat.ApplyListTemplate ListTemplate:=objDoc.ListTemplates(2), ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList
'Count the list entries
list_count = .Paragraphs.Count
'add to the last entry in the list
For k = 1 To list_count
Set insert_para = .ListParagraphs(k)
para_text = .ListParagraphs(k).Range.Text
If k < list_count - 1 Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & (";") & vbNewLine
If k = list_count - 1 Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & ("; and") & vbNewLine
If k = list_count Then insert_para.Range.Text = Mid(para_text, 2, Len(para_text) - 2) & (".") & vbNewLine
Next k
End With
End If
Next i
End Function
The amendment of the entries works fine to add the semicolons etc.
The problem is that after population of the initial list, if there are say 3 entries from a list limited to 5 there will be two blank entries with unused bookmarks at each (e.g. named listentry4 and listentry5 etc). I need to delete bookmarks and the text including the paragraph marker which is in the bookmarks from the word list before the formatting occurs.
The entire list is within a bookmark named for example "notelist_bm" (the code is testing on a single list - the idea is the name of the list encasing bookmark is carried to the function each list - I will amend to do this once the single list code works)
Any thoughts on how to delete the word bookmarks (and their enclosed text) that falls within another word bookmark? I have put in bold my latest thought that did not work - this is where the error occurs.