Re: Fetching data in Excel from Word by foopbl
foopbl
Thu Apr 10 12:45:47 PDT 2008
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
"ryguy7272" <ryguy7272@discussions.microsoft.com> a écrit dans le message de
news:C1B60051-6A27-4BDB-A42D-5CF091CE2E4B@microsoft.com...
> 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---
>
>
> --
> RyGuy
>
>
> "Doug Robbins - Word MVP" wrote:
>
>> 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
>>
>> "foopbl" <foopbl@free.fr> wrote in message
>> news:ewW$pGPmIHA.1168@TK2MSFTNGP02.phx.gbl...
>> > 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
>>
>>
>>