Fetching data in Excel from Word

[foopbl]

Hello,

I have a Word document.
I have in an Excel file with thousands of IP adresses and other similar type
of information.

In the word document, I have to create a chapter for each physical servers
(a lot).
In each chapter, a table display the IP configuration of the sever as well
as virtualized servers inside it (a lot more)

These information are randomy defined in the Excel file.

What is the best way to do that ?

I'd like to have a button in the word document which call a macro.
This macro would open the Excel document and lookup the IP information based
on the server name.
Then, it would update tags or fields or variables that I would insert here
and there in the word document.

The goal here is to avoid regrouping & copying by hand thousands of IP
adresses, which is prone to mistakes.
Moreover, whenever the IP adresses are updated in the Excel file, I would
run the macro to update the word document.


Any good idea ?

I know how to do a macro in Excel, but never did one in word.
My main problem is how to create a "field" or a "variable" in the text
(generally in -but not restricted to- a table) that my macro would recognise
in the word document and update it ?

Thanks in advance.

JD

 

[Doug Robbins - Word MVP]

Addressing only your "main problem", I would use DOCVARIABLE fields in the
document where you want to display the information and use code to set the
value of the variables based on the data that you extract from Excel and
have the code update the fields in the document.

Here is a bit of code in a Word macro that does that sort of thing, making
use of the Microsoft DAO 3.6 Object Library

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase(ThisDocument.Path & Application.PathSeparator &
"GTData.xls", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `Liability`")
' Determine the number of retrieved records
rs.MoveFirst
rs.MoveNext
With ActiveDocument
.Variables("varStatement").Value = rs.Fields(1).Value
.Variables("varStatement1").Value = rs.Fields(2).Value
.Range.Fields.Update
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

The DOCVARIABLE fields in that case were

{ DOCVARIABLE varStatement }

and

{ DOCVARIABLE varStatement1 }
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ryguy7272]

Doug is pretty darn proficient with all things pertaining to Word; he helped
me with several issues. See the code below for more guidance (I think Doug
gave it to me):

Private Sub CommandButton1_Click()

ListBox1.BoundColumn = 1
ActiveDocument.Variables("IDNumber").Value = ListBox1.Value

ListBox1.BoundColumn = 2
ActiveDocument.Variables("First_Name").Value = ListBox1.Value


' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub
Private Sub UserForm_Initialize()
'Dim qd As DAO.QueryDef


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\ExcelModel.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub


Regards,
Ryan---


Alternatively, you can use something like this, from EXCEL, to push data to
a WORD template with DocVariables embedded in it.

Sub ControlWordFromXL()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
On Error Resume Next

Sheets("LOOKUP").Activate
objWord.ActiveDocument.Variables("First_Name").Value =
Range("First_Name").Value
objWord.ActiveDocument.Variables("Last_Name").Value =
Range("Last_Name").Value


objWord.ActiveDocument.Fields.Update

On Error Resume Next
objWord.Visible = True

End Sub


Regards,
Ryan---

 

[foopbl]

Hi,

I finally found a solution with DOCVARIABLE and it finally worked.

However, I prefer your way using a "database", it looks better than scanning
the Excel cells until it's empty....

I'll try it right away....

Thanks
JD