Bulletted List Creation

jdean · #1 11-11-2013, 03:31 PM

Quote:

Originally Posted by macropod

I see that you have 'Set wrdApp = Nothing', but I don't see where you've terminated the wrdApp session (i.e. wrdApp.Quit). Not having that means you'll have an orphaned Word session floating around.

Hint: Since it seems you want your Word session to be running for multiple documents, you should start the Session independently of all the looping that opens the documents, then kill it once all the processing has been done. See, for example: https://www.msofficeforums.com/excel...readsheet.html

Macropod, I'm not sure if I want my word session to run for multiple documents necessarily. This is what is happening: If I run it, it applies the styles ok. I then manually either close or save the generated word doc. That is fine.
However, if I run the code a second time, just to see if I get the same results, that's when I get Run-Time Error '462': The remote server machine does not exist or is unavailable.

When I get this error, this part of the line is highlighted in yellow:

If bBulletA = False Then Call AddBulletStyle("BulletA", ChrW(61623), InchesToPoints(0), InchesToPoints(0.5), True)

I also suspected another instance of Word running, but I am a novice in Word VBA and wasn't sure how to handle it. Anyway, I placed "wrdApp.Quit" right before setting wrdDoc and WrdApp to Nothing in my code but the error persisted.
Also, when you say "start the Session independently of all the looping that opens the documents, then kill it once all the processing has been done," I don't think I start wrdApp or wrdDoc in a loop in my code above, so I am unsure what to do. So sorry to bother you this much but I am not that experienced in word vba.

I have an idea ... how about a code to check and close any open/orphaned Word session(s) before a new one is open each time the code runs?

Thanks!
Jay

fumei · #2 11-11-2013, 08:30 PM

You do not want to get orphaned sessions in the first place. Word rarely ever needs multiple instances, as it is very robust and can handle many documents in one session.

jdean · #3 11-11-2013, 09:11 PM

Quote:

Originally Posted by fumei

You do not want to get orphaned sessions in the first place. Word rarely ever needs multiple instances, as it is very robust and can handle many documents in one session.

Fumei,
I agree with you. My code runs ok the first time everytime from Excel. But when I run it a first time, and do another run then I get that Run-Time Error '462': The remote server machine does not exist or is unavailable. I have qualified the variables as I described in my earlier post. Macropod also gave me very insightful tips but I am still getting the error after a back-to-back run.

I even Dimmed all the Word objects in the Option Explicit section, and set them in the main code. That even enabled me to qualify Activedocument with Wrddoc.Application.Activedocument and so on but I still got the error. I also added wrdApp.Quit at the end of my code.. same problem.

Maybe, if I breakdown my process (assuming I am starting from scratch), you can look at it from a different coding technique.

Assume In Excel, Range("A:B") contains data.

I want to create a word document, then load the data from Excel in succession. That is Range("A1").value in Excel will be in 1st paragraph of Word, B1.value in 2nd Word paragraph, A2 in 3rd para, B2 in 4th para and so on...

There will be only 2 bulletting styles for the paragraphs:

Paragraphs 1, 3, 5, 7, etc should have this style applied:
Bullet ChrW(61623). NOTE: Bullet should be on/or closest to page margin but distance between the bullet and the paragraph should be about 0.5 inch. Also, the paragraph font.bold = true.

Paragraphs 2, 4, 6, 8, etc should have this style applied:
Hollow circle bullet "O" . NOTE: Bullet should be 1 inch from page margin but distance between the bullet and the paragraph should be about 0.5 inch. Also the paragraph font.bold = false

That is the entire problem in a nutshell. Macropod, used his approach because I had already successfully loaded the data into Word and was struggling with the bulltting. However, I am not sure if bulletting as the data are entered into the Word will be more efficient...

If you can also look at it differently from how I have described the problem I'd appreciate it.

Thanks!
Jay

macropod · #4 11-12-2013, 12:21 AM

Try all of the following in the same code module:

Code:

Option Explicit
' Define Word variables
Dim wdApp As Word.Application, wdDoc As Word.Document, wdStl As Word.Style
 
Sub MyCode()
Dim a As Range, b As Range, i As Long, t As Long, bBulletA As Boolean, bBulletB As Boolean
bBulletA = False: bBulletB = False
Application.ScreenUpdating = False
t = Worksheets(1).Range("A3:A10000").SpecialCells(xlCellTypeConstants).Cells.Count
Set a = Worksheets(1).Range("A5").Cells
Set b = Worksheets(1).Range("B5").Cells
Set wdApp = New Word.Application
wdApp.Visible = True
wdApp.WindowState = wdWindowStateMinimize
Set wdDoc = wdApp.Documents.Add
With wdDoc.Range
  .InsertAfter "Drafted" & " Something" & vbCr
  For i = 1 To t
    .InsertAfter a.Value & vbCr & b.Value & vbCr
    Set a = a.Offset(1, 0).Cells
    Set b = b.Offset(1, 0).Cells
  Next i
End With
For Each wdStl In wdDoc.Styles
  If wdStl.NameLocal = "BulletA" Then bBulletA = True
  If wdStl.NameLocal = "BulletB" Then bBulletB = True
Next
If bBulletA = False Then Call AddBulletStyle("BulletA", ChrW(61623), _
  wdApp.InchesToPoints(0), wdApp.InchesToPoints(0.5), True)
If bBulletB = False Then Call AddBulletStyle("BulletB", "o", _
  wdApp.InchesToPoints(1), wdApp.InchesToPoints(1.5), False)
With wdDoc.Range
  .Style = "BulletA"
  .Paragraphs.First.Style = "Normal"
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "^13Exceeded[!^13]@^13"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    .Start = .Start + 1
    .End = .End - 1
    .Style = "BulletB"
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
wdApp.WindowState = wdWindowStateNormal
Set wdApp = Nothing: Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub 
 
Sub AddBulletStyle(StrNm As String, StrBullet As String, iIndnt As Single, iHang As Single, bFntBld As Boolean)
Set wdStl = wdDoc.Styles.Add(Name:=StrNm, Type:=wdStyleTypeParagraph)
With wdApp.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
  .NumberFormat = StrBullet
  .TrailingCharacter = wdTrailingTab
  .NumberStyle = wdListNumberStyleBullet
  .NumberPosition = wdApp.InchesToPoints(0)
  .Alignment = wdListLevelAlignLeft
  .ResetOnHigher = 0
  .StartAt = 1
  .LinkedStyle = StrNm
End With
With wdStl
  .AutomaticallyUpdate = False
  .BaseStyle = "Normal"
  .LinkToListTemplate ListTemplate:=wdApp.ListGalleries(wdBulletGallery).ListTemplates(1), ListLevelNumber:=1
  With .ParagraphFormat
    .LeftIndent = iHang
    .RightIndent = wdApp.InchesToPoints(0)
    .FirstLineIndent = -iHang + iIndnt
    .TabStops.ClearAll
    .TabStops.Add Position:=iHang, _
      Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
  End With
  .Font.Bold = bFntBld
End With
End Sub

Note the extent of the explicit referencing to Word & wdApp.

jdean · #5 11-12-2013, 01:07 AM

Macropod,

You are a genius !! Let me do a few tests and I will give you a full report later.

Jay

jdean · #6 11-12-2013, 06:29 AM

Macropod, your code works and the error doesn't show when I run the code back-to-back. I just want to make a few final touches:

1. I want that single top line "Drafted" & " Something" to be bolded
2. No line spaces should be between the paragraphs
3. For every paragraph that starts with "Exceeded" I want the first 2 words italicized. This will be equivalent to the first 22 characters of that paragraph (spaces included) italicized.

Thanks for all the help.

Jay

macropod · #7 11-12-2013, 03:23 PM

1. After:
.Paragraphs.First.Style = "Normal"
insert:
.Paragraphs.First.Range.Style = "Strong"

2. Before 'With wdDoc.Range', add:

Code:

With wdDoc.Styles("Normal").ParagraphFormat
  .SpaceBefore = 0
  .SpaceAfter = 0
End With

3. After '.Style = "BulletB"', insert:

Code:

With .Paragraphs.First.Range.Words.First
  .Style = "Emphasis"
  .Next.Words.First.Style = "Emphasis"
End With

jdean · #8 11-12-2013, 04:32 PM

Macropod,
This is phenomenal, and thanks a boatload for the assistance!

1. What if I only wanted a space after just the first paragraph "Drafted Something"? It acts like a header in the doc and it would be good to have a space between just that first paragraph and the second, although it's not a big deal.

2. In Word VBA, what is the difference between a section and a range?

3. In Excel VBA, I think the smallest range refers to a cell, what is the smallest range in Word VBA?

4. In Word VBA, does paragraphs(i).range refer to an entire paragraph including the blank spaces inbetween words in the paragraph, or it is just an array containing the successive words in the paragraph?

5. What Word VBA beginners' book would you recommend for me?

Once again, thanks a bunch!
Jay

macropod · #9 11-12-2013, 04:46 PM

1. You could try changing:
.InsertAfter "Drafted" & " Something" & vbCr
to:
.InsertAfter "Drafted" & " Something" & Chr(11) & vbCr

2. A Section is an object spanning a Range. In a document without Section breaks, the first Section spans the whole document.

3. The insertion point, which has 0 length, or a 0-length range (e.g. MsgBox Len(ActiveDocument.Range(1, 1)))

4. .Paragraphs(i).Range refers to the entire paragraph i.

5. About the only such books I've ever used (and still have in my posession) are the manuals that came with Office Version 4 in 1993...

fumei · #10 11-13-2013, 12:06 AM

An important point regarding #4. Paragraphs(i).Range includes the paragraph mark. This is significant.

Regarding books, while out of print, and dated version-wise, if you can find them the following give an in-depth accounting of Word. You may possibly find them in a used book store.

VBA Developer's Handbook (Ken Getz, Mike Gilbert) Sybex
Word Developer's Handbook (Guy Hart-Davis) Sybex. This is a monster at 1300 pages.

jdean · #11 11-17-2013, 03:09 AM

Macropod,

Sorry, I didn't get a chance to post sooner. The additional suggestions worked great and I am very grateful. Thanks, for the answers and thanks also to Fumei as well, for chiming in. I will check out those books for sure.

1. Apart from the header, "Drafted" & " Something," every paragraph that does not start with "Exceeded" contains one comma (that is ","). What if I wanted all the words starting from the comma to the end of such paragraphs not bolded? This will be equivalent to saying that if a paragraph has a bullet style ChrW(61623), then make the font of the words bold until you hit a comma in the paragraph then starting from the comma, the font for all the remaining words in the paragraph will not be bolded.

2. So does that mean that the current position of the cursor anywhere in a Word document can be described using rows and columns addressing? As in Range(cells(a,b), cells(d,c)).

3. If (2) is correct then what is the maximum number of rows and columns in a Word document? Or, maybe, it will be better to ask whether it's a good practice for one to think of the location of a "Selection" in terms of rows and columns?

4. In the code is wdApp just a Word application instance and wdDoc a document instance? I ask this because one would think that by hierarchy and maybe polymorphism, the properties of wdApp would be automatically inherited by wdDoc, but they seem to have different properties.

5. In your code how is Find/Replace applying the style to the paragraphs? This is fascinating to me.

6. What do "^13" and "[!^13]@^13" mean? What does [] mean in Word VBA?

7. Please take the time to comment your code out beginning from the line..With wdDoc.Range. I am particularly interested in comments in the Find/Replace and in the Sub AddBulletStyle() procedure. I think the comments will really help me understand and learn from your code.

Please pardon me for asking too many questions.

Thanks!
Jay

macropod · #12 11-17-2013, 04:27 AM

Re 1: In that case, you'd define the "BulletA" Style as not bold, then use Find/Replace to apply the built-in "Strong" character Style to everything up to (and including?) the first comma.

Re 2: No, not unless you're actually working in a table and, even then, you can differentiate between ranges that span cells and ranges within cells.

Re 3: N/A

Re 4: Yes and Yes.

Re 5: the F/R only applies the "BulletB" Style, via '.Style = "BulletB"' in the loop.

Re 6: See: http://word.mvps.org/FAQs/General/UsingWildcards.htm

Re 7: See comment in the following, which includes the modifications in (1) above:

Code:

Option Explicit
' Define Word variables
Dim wdApp As Word.Application, wdDoc As Word.Document, wdStl As Word.Style, wdRng As Word.Range
 
Sub MyCode()
' Define generic variables
Dim a As Range, b As Range, i As Long, t As Long, bBulletA As Boolean, bBulletB As Boolean
bBulletA = False: bBulletB = False
Application.ScreenUpdating = False
t = Worksheets(1).Range("A3:A10000").SpecialCells(xlCellTypeConstants).Cells.Count
Set a = Worksheets(1).Range("A5").Cells
Set b = Worksheets(1).Range("B5").Cells
' Start a new Word session
Set wdApp = New Word.Application
' Make sure the Word session is visible
wdApp.Visible = True
' Minimse the Word session
wdApp.WindowState = wdWindowStateMinimize
' Create a new Word document
Set wdDoc = wdApp.Documents.Add
With wdDoc.Range
  ' Insert the first paragraph
  .InsertAfter "Drafted" & " Something" & Chr(11) & vbCr
  ' insert the subsequent paragraphs
  For i = 1 To t
    .InsertAfter a.Value & vbCr & b.Value & vbCr
    Set a = a.Offset(1, 0).Cells
    Set b = b.Offset(1, 0).Cells
  Next i
End With
' Check whether the "BulletA" & "BulletB" Styles exist
For Each wdStl In wdDoc.Styles
  If wdStl.NameLocal = "BulletA" Then bBulletA = True
  If wdStl.NameLocal = "BulletB" Then bBulletB = True
Next
' Create the "BulletA" & "BulletB" Styles if necessary,
' passing parameters for the Style name, bullet type,
' hanging & left indents and font bolding
If bBulletA = False Then Call AddBulletStyle("BulletA", _
  ChrW(61623), wdApp.InchesToPoints(0), wdApp.InchesToPoints(0.5), False)
If bBulletB = False Then Call AddBulletStyle("BulletB", "o", _
  wdApp.InchesToPoints(1), wdApp.InchesToPoints(1.5), False)
' Edit the "Normal" Style
With wdDoc.Styles("Normal").ParagraphFormat
  .SpaceBefore = 0
  .SpaceAfter = 0
End With
' Set a range variable for the whole document
Set wdRng = wdDoc.Range
With wdDoc.Range
  ' Apply the "BulletA" Style to the whole document
  .Style = "BulletA"
  ' Apply the "Normal" Style to the first paragraph
  .Paragraphs.First.Style = "Normal"
  ' Apply the "Strong" Character Style to the first paragraph
  .Paragraphs.First.Range.Style = "Strong"
  ' Find paragraphs containing a comma"
  With .Find
    .Text = "^13[!^13,]@,"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  ' For each found paragraph
  Do While .Find.Found
    ' Move the start of our range forward one character,
    ' because the Find expression includes the paragraph
    ' break from the previous paragraph
    .Start = .Start + 1
    ' Move the end of our range back to the first comma
    .End = .Start + InStr(.Text, ",") - 1
    ' Apply the "Strong" Character Style up to the first comma
    .Style = "Strong"
    ' Collapse the Find range to its end
    .Collapse wdCollapseEnd
    ' Look for the next instance
    .Find.Execute
  Loop
  ' Re-set the Find range
  .Start = wdRng.Start
  .End = wdRng.End
  ' Look for paragraphs starting with "Exceeded"
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "^13Exceeded[!^13]@^13"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  ' For each found paragraph
  Do While .Find.Found
    ' Move the start of our range forward one character,
    ' because the Find expression includes the paragraph
    ' break from the previous paragraph
    .Start = .Start + 1
    ' Move the end of our range backwards one character,
    ' because the next Find expression includes the paragraph
    ' break from this paragraph
    .End = .End - 1
    ' Clear the "Strong" Character Style if it's been set
    .Font.Reset
    ' Apply the "BulletA" Style to the paragraph
    .Style = "BulletB"
    ' Apply the "Emphasis" Style to the first two words in the paragraph
    With .Paragraphs.First.Range.Words.First
      .Style = "Emphasis"
      .Next.Words.First.Style = "Emphasis"
    End With
    ' Collapse the Find range to its end
    .Collapse wdCollapseEnd
    ' Look for the next instance
    .Find.Execute
  Loop
End With
wdApp.WindowState = wdWindowStateNormal
Set wdApp = Nothing: Set wdDoc = Nothing: Set wdRng = Nothing
Application.ScreenUpdating = True
End Sub

 
Sub AddBulletStyle(StrNm As String, StrBullet As String, iIndnt As Single, iHang As Single, bFntBld As Boolean)
' Add the Style, as a Paragraph Style
Set wdStl = wdDoc.Styles.Add(Name:=StrNm, Type:=wdStyleTypeParagraph)
' Apply the required bullet format to the Style
With wdApp.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
  .NumberFormat = StrBullet
  .TrailingCharacter = wdTrailingTab
  .NumberStyle = wdListNumberStyleBullet
  .NumberPosition = wdApp.InchesToPoints(0)
  .Alignment = wdListLevelAlignLeft
  .ResetOnHigher = 0
  .StartAt = 1
  .LinkedStyle = StrNm
End With
' Define the Style's formatting, based on the Normal Style
With wdStl
  .AutomaticallyUpdate = False
  .BaseStyle = "Normal"
  .LinkToListTemplate ListTemplate:=wdApp.ListGalleries(wdBulletGallery).ListTemplates(1), ListLevelNumber:=1
  With .ParagraphFormat
    .LeftIndent = iHang
    .RightIndent = wdApp.InchesToPoints(0)
    .FirstLineIndent = -iHang + iIndnt
    .TabStops.ClearAll
    .TabStops.Add Position:=iHang, _
      Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
  End With
  .Font.Bold = bFntBld
End With
End Sub

jdean · #13 11-17-2013, 02:51 PM

Macropod,

Your code works wonderfully. However, I just realized that some of the target paragraphs contain multiple commas so the Font.Bold=False should be applied starting from the last comma in the paragraph, not the first comma in the paragraph as I indicated earlier.

That is, if a paragraph has a bullet style ChrW(61623), then all the words starting from the last comma in the paragraph to the end of the paragraph should not be bolded.

Thanks, again and sorry for the confusion!
Jay

macropod · #14 11-17-2013, 05:29 PM

Ho hum, try:

Code:

Sub MyCode()
Dim a As Range, b As Range, i As Long, t As Long, bBulletA As Boolean, bBulletB As Boolean
bBulletA = False: bBulletB = False
Application.ScreenUpdating = False
t = Worksheets(1).Range("A3:A10000").SpecialCells(xlCellTypeConstants).Cells.Count
Set a = Worksheets(1).Range("A5").Cells
Set b = Worksheets(1).Range("B5").Cells
Set wdApp = New Word.Application
wdApp.Visible = True
wdApp.WindowState = wdWindowStateMinimize
Set wdDoc = wdApp.Documents.Add
With wdDoc.Range
  ' Apply the "BulletA" Style to the whole document
  .Style = "BulletA"
  ' Apply the "Normal" Style to the first paragraph
  .Paragraphs.First.Style = "Normal"
  ' Apply the "Strong" Character Style to the first paragraph
  .Paragraphs.First.Range.Style = "Strong"
  ' Find paragraphs containing a comma"
  With .Find
    .Text = ",[!^13,]{1,}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  ' For each found paragraph
  Do While .Find.Found
    ' Move the start of our range forward one character,
    ' to start the new format after the comma and
    ' because the Find expression includes the comma
    ' from the previous iteration
    .Start = .Start + 1
    ' Apply the "Strong" Character Style from the last comma
    .Style = "Strong"
    ' Collapse the Find range to its end
    .Collapse wdCollapseEnd
    ' Look for the next instance
    .Find.Execute
  Loop
  ' Re-set the Find range
  .Start = wdRng.Start
  .End = wdRng.End
  ' Look for paragraphs starting with "Exceeded"
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "^13Exceeded[!^13]@^13"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  ' For each found paragraph
  Do While .Find.Found
    ' Move the start of our range forward one character,
    ' because the Find expression includes the paragraph
    ' break from the previous paragraph
    .Start = .Start + 1
    ' Move the end of our range backwards one character,
    ' because the next Find expression includes the paragraph
    ' break from this paragraph
    .End = .End - 1
    ' Clear the "Strong" Character Style if it's been set
    .Font.Reset
    ' Apply the "BulletA" Style to the paragraph
    .Style = "BulletB"
    ' Apply the "Emphasis" Style to the first two words in the paragraph
    With .Paragraphs.First.Range.Words.First
      .Style = "Emphasis"
      .Next.Words.First.Style = "Emphasis"
    End With
    ' Collapse the Find range to its end
    .Collapse wdCollapseEnd
    ' Look for the next instance
    .Find.Execute
  Loop
End With
wdApp.WindowState = wdWindowStateNormal
Set wdApp = Nothing: Set wdDoc = Nothing: Set wdRng = Nothing
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calendar Creation	sjvjoe	Publisher	1	07-14-2016 06:32 PM
Document Creation	Privateer	Word	3	06-20-2013 08:15 PM
Help with Complex Table Creation	saquib	Word	0	02-12-2013 06:28 AM
Label Creation	speloquin	Word	1	05-27-2011 03:08 PM
Report creation.	Igtech	Excel	1	04-02-2010 03:33 PM