Excel --> Word. Creating a LIST in a word doc.... ? [VBA]

[Terry_Orange]

Hi folks,

I have code to write information from Excel to Word, text, images, graphs, tables etc but given I have some information

eg.

FRUITS
apples
pears
bananas

I would like to write a list in the Word document that I create on the fly.

I have tried using the Word macro recorder and pasting this within Excel:

    With ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
        .NumberFormat = ChrW(61623)
        .TrailingCharacter = wdTrailingTab
        .NumberStyle = wdListNumberStyleBullet
        .NumberPosition = CentimetersToPoints(0.63)
        .Alignment = wdListLevelAlignLeft
        .TextPosition = CentimetersToPoints(1.27)
        .TabPosition = CentimetersToPoints(1.27)
        .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 = "Symbol"
        End With
        .LinkedStyle = ""
    End With
    ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""
    obj.Selection.Range.ListFormat.ApplyListTemplate ListTemplate:=ListGalleries( _
        wdBulletGallery).ListTemplates(1), ContinuePreviousList:=False, ApplyTo:= _
        wdListApplyToWholeList, DefaultListBehavior:=wdWord9ListBehavior
    obj.Selection.TypeText Text:="apples"
    obj.Selection.TypeParagraph
    obj.Selection.TypeText Text:="bananas"
    obj.Selection.TypeParagraph
    obj.Selection.TypeText Text:="pears"
    obj.Selection.TypeParagraph

where obj is my word document that I have created / opened....

It returns an error saying that Method 'ApplyListTemplate' of object 'ListFormat' failed

Anyone any ideas ? Anyone tried creating a LIST in word automatically from Excel ????

Thanks again for the help...

TO.

 

[NateO]

Re: Excel --> Word. Creating a LIST in a word doc.... ? [

Hello, something like the following works for me,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> test()
<SPAN style="color:darkblue">Dim</SPAN> myWd <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Set</SPAN> myWd = GetObject(, "Word.Application")
myWd.Documents(1).content = Replace(Join$(Application.Transpose( _
    Range([a1], [a65536].End(3))), "|"), "|", vbNewLine)
<SPAN style="color:darkblue">Set</SPAN> myWd = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Looks like you're using Word constants in Excel, you'll want to set a reference to the Word object library and use an early bind to set your object. Otherwise use numeric constants in place of word constants (e.g., wdUndefined)

To get these use debug.print or msgbox constant, e.g.,

msgbox wdUndefined

Replace this constant with the numeric equivalent in the Excel procedure.

Hope this helps.

 

[Terry_Orange]

Re: Excel --> Word. Creating a LIST in a word doc.... ? [

Cheers NateO. Seen many of your posts around and they've been an invaluable help in the past (so thanks!).

I'm using word ref library binds, but I get an Object Required error when I lift the

obj.Documents(1).Content = Replace(Join$(Application.Transpose( _
Range([a4], [a65536].End(3))), "|"), "|", vbNewLine)

section.

using obj as used Set obj = CreateObject("Word.Application.9") earlier.

any further ideas? or have I missed something there !

Thanks
TO.

 

[NateO]

Re: Excel --> Word. Creating a LIST in a word doc.... ? [

Hello Terry, you are welcome.

Hmmm, Word 9 should word 2k. Might want to drop the .9, i.e.,

obj = CreateObject("Word.Application")

Should be alright with a late bind. Another possibility is that you're using XL '97 which doesn't like the replace and join functions, these are vba 6 upgrades... Getting warmer?