RE: Load Data from ListBox into Word Doc. BoundColumn? by ryguy7272
ryguy7272
Sun Oct 14 13:19:01 PDT 2007
For those interested, here is the final arrangement of code:
Code in Module:
Sub Userform_Initialize()
UserForm1.Show
End Sub
Code in UserForm (there are two subs here):
Sub Userform_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Documents and Settings\Contcts.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)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub
Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("FirstName").Value = ListBox1.Value
ListBox1.BoundColumn = 2
ActiveDocument.Variables("LastName").Value = ListBox1.Value
ListBox1.BoundColumn = 3
ActiveDocument.Variables("Company").Value = ListBox1.Value
ListBox1.BoundColumn = 4
ActiveDocument.Variables("BusinessStreet").Value = ListBox1.Value
ListBox1.BoundColumn = 5
ActiveDocument.Variables("BusinessCity").Value = ListBox1.Value
ListBox1.BoundColumn = 6
ActiveDocument.Variables("BusinessState").Value = ListBox1.Value
ListBox1.BoundColumn = 7
ActiveDocument.Variables("BusinessPostalCode").Value = ListBox1.Value
' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub
In your Excel file, name your range (my range is called 'List' and the Excel
file is called 'Contacts.xls'). Finally, on the UserForm, you need to have a
CommandButton, named CommandButton1 and you also need a ListBox, named
ListBox1. The last step is to go to Word, click Insert > Field > DocVariable
> (assign a name in the â??New Nameâ?? box) > Ok. Assign the links to
â??DocVariableâ?? wherever required throughout your document, fiddle with it a
little if it doesnâ??t work after the first attemptâ?¦you will get it to work.
Kind Regards,
Ryanâ??
PS, I extend a warm thanks to Jay Freedman and Doug Robbins for the
assistance. You two are amazing; I tip my hat to you guys.
--
RyGuy
"ryguy7272" wrote:
> From Wordâ??s help menu, I can tell that the BoundColumn identifies the column
> of associated data values stored for the control. There doesnâ??t seem to be a
> whole lot of documentation on this subject, but I am pretty sure it does what
> I want it to do.
>
> Thanks to some slick code provided by Doug Robbins, I can get data from an
> Excel workbook to populate my ListBox, but I canâ??t seem to get the code form
> the ListBox into my Word document.
>
> The code that I am currently working with is shown below:
> Sub CommandButton1_Click()
> Set NewDoc = ActiveDocument
> With NewDoc
> cmbFirstName.BoundColumn = 1
> .Variables("varFirstName") = cmbFirstName.Value
>
> End With
> UserForm1.Hide
> End Sub
>
> Private Sub Click()
>
> Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
> "DOCVARIABLE FirstName ", PreserveFormatting:=True
>
> Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
> "DOCVARIABLE LastName ", PreserveFormatting:=True
>
> End Sub
>
>
> In my word document, I have several fields such as this:
> Error! No document variable supplied.
> Error! No document variable supplied.
>
> I keep getting this message when I hit Ctrl+F9:
> Error! No Document variable Supplied.
>
> What am I doing wrong?
>
> Cordially,
> Ryan---
>
>
> --
> RyGuy