How to work with Word from Excel using VBA

[MPW]

Hey All,

Using Office 2003 in XP.

I am using Excel to Generate a report in Word.
I am having 2 separate issues.
Problem 1. I want to underline certain words and the spaces after them.
Example: Approved By:
I can turn it bold but the underline option has eluded me.
Here is a chunk of the code:

Set r = wrdDoc.Range
With r.Find
   Do While .Execute(Findtext:="Approved By:                ",  _
   Forward:=True) = True
      r.Font.Underline = wdUnderlineSingle

I have tried recording a macro to do it and have rewritten it multiple times but nada. I think that it is because I am doing it from Excel but don't know for sure.

Problem 2. I am also trying to create a footer in the same document that will contain the Name of the Excel Workbook that it came from on the left side of the Footer and the "Page " & # & of Pages " & ## on the right side of the Footer.

I normally work just with Excel. Word is very a different creature. Both of these thing have really stumped me. Any help on either or both of these issues will be great. Thanks

MPW

 

[theaudioguy]

I've been wondering how to do manipulate Word from Excel. I haven't been able to find much on the subject.

 

[Glory]

It really helps if you set a library reference first. Tools -> References and select the option for Word from the list.

That way you can "Dim As New" and some Word data type, then type "Type." and get intellisense to help you.

On the OP: From what I can see your example should be working.

Weird.

 

[theaudioguy]

MPW,

Try changing

r.Font.Underline = wdUnderlineSingle

to

r.Font.Underline = True

 

[MPW]

Excel-lent!

Thanks guys.

I have set the Word Library. (I haven't seen the benefits of it yet but I'm sure I will in time.) One question though, Will there be any problems running the code if this is moved to a machine that does not have this library set?

Thanks to "theaudioguy" for the underline answer. It worked great. It was like it would be written in Excel instead of Word. Who knew?

Only one more giant . . . Setting up the footer to handle both words and page values. Example: "Page " (1) " of " (5)



MPW

 

[theaudioguy]

I'll have to admit, I found the answer online somewhere.

 

[Glory]

Excel-lent!

Thanks guys... Only one more giant . . . Setting up the footer to handle both words and page values. Example: "Page " (1) " of " (5)

MPW

I was just messing around with this the other day. I was trying to add a conditional field to a footer that would put a "0" at the front of any number less than ten.

Wish I could help you. All my intuitive solutions failed.

 

[MPW]

Well Glory

Using this code I can add text.
<div class="smallfont" style="margin-bottom: 2px;"><code style="white-space: nowrap;"><code></code></code>

 	 		 			 Set r = wrdDoc.Range
r.Sections(1).Footers(1).Range.InsertAfter "Page "

r.Sections(1).Footers(1).Range.InsertAfter " of "

I can also add the page numbers with this piece that I stole from a macro and adapted.

r.Sections(1).Footers(1).PageNumbers.Add PageNumberAlignment:= _
wdAlignPageNumberRight, FirstPage:=True

I can't seem to figure out how to mix the two. If I could get a handle on how to concatenate them it would be great. Any flashes of brilliance are welcome, heck even a good guess would be nice!<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->[FONT=&quot][/FONT]

MPW

 

[theaudioguy]

I don't know how the code in Excel would look like. But in Word...

To select the current page's footer:

ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter

To align the footer to the right:

Selection.ParagraphFormat.Alignment = wdAlignParagraphRight

To insert page number:

NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=Selection. _
     Range, RichText:=True

I'll leave it up to you to put it into Excel.

 

[theaudioguy]

Try this. I got it to work a little. (Again, taken from other people across the web)

Dim wdApp As Word.Application
Dim WdDoc As String
Dim oRg As Word.Range


With ActiveDocument
   
    Set oSec = .Sections(1)
    
    Set oRg = ActiveDocument.Sections(1).Footers(1).Range
    
    oRg.Text = "Page "
    oRg.Collapse Direction:=wdCollapseEnd
        
    .Fields.Add Range:=oRg, Type:=wdFieldPage
    Set oRg = oSec.Footers(wdHeaderFooterPrimary).Range
    oRg.Collapse Direction:=wdCollapseEnd
    

    
    oRg.Text = " of "
    Set oRg = oSec.Footers(wdHeaderFooterPrimary).Range
    oRg.Collapse Direction:=wdCollapseEnd

    
    .Fields.Add Range:=oRg, Type:=wdFieldSectionPages
     Set oRg = oSec.Footers(wdHeaderFooterPrimary).Range
     oRg.Collapse Direction:=wdCollapseEnd

        
     Set oRg = oSec.Footers(wdHeaderFooterPrimary).Range
     oRg.Fields.Update
        
End With