Excel Macro to Articulate & Reset MS Word List Bullets

[RandySS]

Hi Folks,

Am using Excel VBA macro to create a MS Word document. Seeking efficient method to reset bullet lettering in Word list back to its first value, that is, reset to “a)” prior to printing a second, or additional list.

I'm using template and styles in Word to establish desired formatting there. Also using TypeText, With WordApp.Selection.ParagraphFormat, TypeParagraph, etc. in Excel macro. All works well.
Only issue is second typing instance of bulleted list (style) in same document. Lettering in second list always continues from first list existing earlier in same document; ie, first letter in second list is h), or similar.

Tried recording a Word macro to identify critical language that might be incorporated into Excel macro, but no such luck. I think critical term is “ContinuePreviousList:= _ False” but am not certain. Am not able to implement same in Excel. One attempt is here:

[START]
With WordApp.Selection
.Style = WordApp.ActiveDocument.Styles("MyBulletTemplate")
.ContinuePreviousList = False
End With
[END]
The above results in error message: “Object doesn’t support this property or method.”

Relevant portions of the Word macro I recorded is here:

With WordApp.Selection
ListGalleries(wdNumberGallery).ListTemplates(1).Name = ""
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior

Since my use of bullet formatting is inside a loop, repeating production of a list, the need to reset the start of the bullet lettering is multiple.

Is there a line of text that I can insert that will cause the list to reset prior to running? Any insights you can offer are appreciated.

Thanks!

 

[strongm]

Your recorded macro ought to do the trick - remembering that Excel has its own Selection object, so you need to make sure you are referencing the one in Word. Assuming you have set a reference to the Word object library (to ensure Word constants are valid):

[blue]WordApp.Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior[/blue]

 

[RandySS]

StrongM, Thanks very much for your help. So apparently I have not "set a reference to the Word object library." Can you possibly point me to a source that would describe what that is and how to do it?
Thx!

 

[SkipVought]

In the VB Editor...

Tools > References --- Microsoft Word m.n Object Library

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

In Excel IDE: Tools - References... - (select) Microsoft Word 14.0 Object Library - OK
(That's for Word 2010)

Have fun.

---- Andy

 

[RandySS]

Thanks much for your help, but bummer, still not having success. Got the Object Library issue switched on via the References menu okay. But, still, the code copied from Word's macro recorder doesn't run in Excel. Specifically, the code that appears to crash is:

[START]
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior
[END]

MVB's error message is:
"Run-time error '450': Wrong number of arguments, or invalid property assignment."

When I alt-tab back to the created Word document, and while the cursor there is in the correct location (first line of the second bulleted/numbered paragraph), I can run the Word version of the above recorded macro and it runs fine, resetting the bullet paragraph to "a)" .

Note that, really the complete Word recorded macro is the below lines, all of which are required for the macro to run successfully. I've copied all of it into my Excel macro. However, it's the content seen above, which appears at end of below code, that causes the Excel macro to crash with Run-time error 450. (Highlights in yellow upon debug.) Anyway, here's the complete Word macro:

[START]
Sub ResetLevel1Para()
With ListGalleries(wdNumberGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = "%1)"
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleLowercaseLetter
.NumberPosition = InchesToPoints(0.75)
.Alignment = wdListLevelAlignLeft
.TextPosition = InchesToPoints(1)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
With .Font
.Bold = wdUndefined
.Italic = wdUndefined
.StrikeThrough = wdUndefined
.Subscript = wdUndefined
.Superscript = wdUndefined
.Shadow = wdUndefined
.Outline = wdUndefined
.Emboss = wdUndefined
.Engrave = wdUndefined
.AllCaps = wdUndefined
.Hidden = wdUndefined
.Underline = wdUndefined
.Color = wdUndefined
.Size = wdUndefined
.Animation = wdUndefined
.DoubleStrikeThrough = wdUndefined
.Name = ""
End With
.LinkedStyle = "H2E_Lvl1_Item_BOM_Quote"
End With
ListGalleries(wdNumberGallery).ListTemplates(1).Name = ""
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
End Sub
[END]

I'm not sure, really just guessing, but it seems that the ListGalleries... function is looking for something that Word provides/needs but Excel does not have.

Thanks very much for your continued help and engagement!

 

[strongm]

You are till trying to use the Excel Selection object, rather than the Word selection object. because they BOTH have a selection object you have to be specific about which one (i.e. from which object model) you want to use. Sorry if I wasn't clear about that in my earlier post.

 

[ChrisHirst]

Am using Excel VBA macro to create a MS Word document.

Wouldn't forum707 be a better place for this question?

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[RandySS]

Strongm,

Other manipulation of the Word document from Excel is being successfully accomplished using lines as seen here:

[START]
With WordApp.Selection
.TypeText Text:=[insert text, or Excel variable, etc]
.Font.[formatting]
.TypeParagraph
.Style = WordApp.ActiveDocument.Styles("Normal")
[other]
End With
[END]

Perhaps the "With WordApp.Selection" is the Word Selection Object you're referring to. I tried putting the subject lines...

ListGalleries(wdNumberGallery).ListTemplates(1).Name = ""
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _

inside the With Word.App.Selection / End With frame, but it didn't work either.

Perhaps you can more thoroughly explain what would need to be changed in my program in order to use the Word selection tool?

Thank you.

 

[SkipVought]

Maybe...

Wordapp.listgalleries.......

The APPLICATION OBJECT is vital on ALL application objects!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

A very late response here I know, but:

>Perhaps the "With WordApp.Selection" is the Word Selection Object you're referring to

No. That bit's fine.

It's

Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _

that is the problem, just as the VBA editor was trying to tell you - and simply sticking it inside the With block won't help, since

a) you seem to have lost part of the statement during cutting and pasting. You firstly need your original

[Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
[blue][b]wdWord10ListBehavior[/b][/blue]

b) It is the "Selection" here that refers to Excel's Selection object (because you are running your VBA in Excel; if you were running in Word it would refer to Word's Selection object (Selection being a global object in each application). So you have to be explicit and use:

[blue][b]WordApp.[/b][/blue]Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior

Or put it in the With block, and change it to:

[blue][b].[/b][/blue]Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior

Also, as previously hinted, VBA questions should really go in forum707.