Populating a column in Excel from MS Word

[NMorales76]

Hi, I am an intermediate MS Excel user and I have a question for those of you who are more savy. I am trying to figure out if it is possible to populate a field in Excel by reading the footer of a MS Word document. Has anyone had experience doing this sort of thing?

Thanks,
-Neil

 

[Worf]

Hi Neil

The following code will get the footer's first word and place it in a cell...

Sub WordProbe()
Dim DocPath$, wApp As Word.Application

DocPath = ThisWorkbook.Path
Set wApp = CreateObject("Word.Application")
wApp.Visible = msoTrue
wApp.Documents.Open DocPath & "\Main2.docx"

With wApp
    .WordBasic.viewfooteronly
    .Selection.WholeStory
End With

Sheets("Sheet1").Range("e15").Value = wApp.Selection.Words(1)
Set wApp = Nothing

End Sub

 

[NMorales76]

Hi Worf, thanks for the reply. However, there are more than one word that I need to get from the footer. Can I just change the value at the bottom of your code? Or is it more complex than that?

 

[Worf]

Hi

This version will fetch all footer's text.

Sub WordProbe()
Dim DocPath$, wApp As Word.Application

DocPath = ThisWorkbook.Path
Set wApp = CreateObject("Word.Application")
wApp.Visible = msoTrue
wApp.Documents.Open DocPath & "\Main2.docx"

With wApp
    .WordBasic.ViewFooterOnly
    .Selection.WholeStory
End With

Sheets("Sheet1").Range("e15").Value = wApp.Selection.Text

Set wApp = Nothing

End Sub

 

[NMorales76]

Hey I tried the code but it is giving me an error on the "wApp As word.Application" portion of it. It is saying that there is a "Compile Error - User-defined type not defined."

 

[Worf]

Hi

In the VB window, go to Tools / References, and make sure the following is checked:

- Microsoft Office Object Library
- Microsoft Word Object Library

This should solve it...

 

[NMorales76]

It worked. You are the MAN! Thank you very much for your help. Much appreciated.

 

[Worf]

You are welcome...

Thanks for the feedback.