Compile Error: Method or data member not found

[ryguy7272]

I keep getting a message that says the following:

Compile Error:
Method or data member not found


The code looks like this:
Private Sub CommandButton1_Click()

ListBox1.BoundColumn = 1
ActiveDocument.Variables("FirstName").Range.InsertBefore
ListBox1.ValueNext i

ListBox1.BoundColumn = 2
ActiveDocument.Variables("LastName").Range.InsertBefore
ListBox1.ValueNext i

' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub


And the error occurs on this line:
ActiveDocument.Variables("FirstName").Range.InsertBefore



I got this idea form this site:
http://www.pcreview.co.uk/forums/thread-875661.php



I get my ListBox1 to load with this sub:
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\RMS\Desktop\Contacts.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

Does anyone have any ideas as to what I'm doing wrong?
This would be so awesome if I could get it to work!!!

Thanks,
Ryan--

 

[Jay Freedman]

I'm afraid my colleague Doug Robbins misspoke in the article you
cited. When you're using document variables and {DOCVARIABLE
"varname"} fields in the document, your macro needs to assign values
to the variables, rather than inserting anything physically into the
document body.

Each of those lines should be like

ActiveDocument.Variables("FirstName").Value = ListBox1.Value

Then when the ActiveDocument.Fields.Update statement runs, the
corresponding DocVariable fields will display the values of the
variables.

 

[ryguy7272]

Thank you 1,000,000*


That was EXACTLY what I was looking for (I won't even say how long I was
looking for it). I have been working with VBA in Excel for a while, and I am
thrilled to be learning VBA for Word now.

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--