Dynamic content from Excel to Word Bookmark after select from a combobox

[teddy beh via OfficeKB.com]

I'm new to vba. Hope someone can help.

I have text file to stored the category id. Then my word template have a
macro to call out all the company names with the same category id stored in
the text file and list in a combobox. After i select a company name, it will
pull the related information from excel like address, tel no, fax no...to my
word bookmarks.

This would help if got changes on the data. I no need to hard coded the
detail in vba. If i have whole list of company names i just need to
categories them into different category without to change the code and the
template can be reused.

 

[teddy beh via OfficeKB.com]

May be can do it in 1 excel file. Sheet 1 stored the ID and another sheet
store the data.

Any idea?

 

[teddy beh via OfficeKB.com]

Here is my code. But not so good to do it in this way. Anyone can help?

I prefer can read from 1 excel file and compared between 2 sheets.

Private Sub btnOk_Click()

Dim i As Integer
CompanyName = ""
RegNo = ""
Address1 = ""
Address2 = ""
Country = ""
TelNo = ""
FaxNo = ""
URL = ""

For i = 1 To cboAddress.ColumnCount
cboAddress.BoundColumn = i
If i = 1 Then
CompanyName = cboAddress.Value
ElseIf i = 2 Then
RegNo = cboAddress.Value
ElseIf i = 3 Then
Address1 = cboAddress.Value
ElseIf i = 4 Then
Address2 = cboAddress.Value
ElseIf i = 5 Then
Country = cboAddress.Value
ElseIf i = 6 Then
TelNo = cboAddress.Value
ElseIf i = 7 Then
FaxNo = cboAddress.Value
ElseIf i = 8 Then
URL = cboAddress.Value
End If
Next i

'Address = Address &cboAddress.Value & vbCr
ActiveDocument.Bookmarks("CompanyName").Range.InsertBefore CompanyName
ActiveDocument.Bookmarks("RegNo").Range.InsertBefore RegNo
ActiveDocument.Bookmarks("Address1").Range.InsertBefore Address1
ActiveDocument.Bookmarks("Address2").Range.InsertBefore Address2
ActiveDocument.Bookmarks("CountryPostalcode").Range.InsertBefore Country
ActiveDocument.Bookmarks("TelNo").Range.InsertBefore TelNo
ActiveDocument.Bookmarks("FaxNo").Range.InsertBefore FaxNo
ActiveDocument.Bookmarks("URL").Range.InsertBefore URL
Letterhead1.Hide

End Sub


Private Sub UserForm_Initialize()
Dim x As Integer
x = FreeFile
Open "C:\Filepath\CateID.txt" For Input As #x
'store contents in array
MyString = Input(LOF(x), x)
Close #x


Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m
As Long, n As Long
' Modify the path in the following line so that it matches where you
' Saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="C:\Filepath\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count - 1

' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
cboAddress.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
Set myUpitem = sourcedoc.Tables(1).Cell(m + 2, j + 1).Range
If Left(myUpitem, 1) = MyString Then
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
End If
Next m
Next n
' Load data into cboAddress
cboAddress.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub