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

 

[NdNoviceHlp]

Untested. HTH. Dave

Dim rfooter As Object
Dim oHF As Object
Set oHF = WordApp.activedocument.Sections(1).Footers(1)
   With oHF
      .LinkToPrevious = False
      Set rfooter = oHF.Range
      With rfooter
         .Text = "Page "
         .collapse Direction:=0
         .Fields.Add Range:=rfooter, Type:=33
      End With
   End With
Set rfooter = Nothing
Set oHF = Nothing

'set print preview for footer view
WordApp.activedocument.ActiveWindow.View.Type = 3
WordApp.activedocument.ActiveWindow.View.Zoom.Percentage = 100

edit: WordApp is the word application object

 

[MPW]

Well Glory,

I apparently failed to set the library the 1st time around and most of my problems stemmed from that. I did finally get it set and the world is right again.

I used the last suggestion from theaudioguy and it worked fine.

Dave, I decided to leave well enough alone since the code was working. However, I was wondering what

Type:=33

does if you care to explain it.

Other than that question I consider this post answered.

Thanks for all the help.

 

[NdNoviceHlp]

I really hate that early binding thing. If you do the late binding thing.... there's no need for any library reference (please spare me the debate wrath). You can change all of those Word specific things to numerical constants. The type 33 thing some how numbers your pages. Dave

 

[MPW]

Thanks Dave,

No debate here, I don't know enough about either camp to take a side. I hear a lot about binding but didn't really understand the concept until I found this link: http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm
I don't suppose you are the same Dave that wrote it since he liked the early binding, but it did break it down for me. Apparently I have been doing a mixture of both for some time! Do you think he missed anything?

Anyway, I do like simple, and less reliance on 'libraries' sounds good. If type 33 is for the page number what type is for the total number of pages?

Mark

 

[RSXchin]

How did you guys post your code with syntax coloring?

 

[theaudioguy]

We clicked the 'Wrap {PHP} Tags around selected text' button. I'm not sure if that's right the thing to do.

 

[RSXchin]

It's a lot easier to read. I think I'll do that from now on.

 

[theaudioguy]

I agree. I saw somebody else use it, so I decided to use it. Hopefully we won't get anybody telling us we did it wrong.