Populate Dropdown Box in Word

I have a drop down in my word document. It's not a list, but a form
drop down (HTMLSelect) using the Web Tools toolbar. I want to
populate
this drop down with values from an Excel sheet. How do I do this? I'm
connecting to Excel, but unable to figure out how to populate the drop
down.

jille
Mon Feb 12 13:59:01 CST 2007

So if you're already connecting to Excel (ie opened the Excel file as an
object) you should be able to do something like this:

yourcomboname.RowSource = "NamedRangeName"

The one I did was purely in Excel, using a named range to populate a
combobox, but the principle should work. Since the combobox is in your
document and not a userform, I would recommend positioning it in the autoopen
procedure so that when you open the document, the combobox auto populates.

Hope this helps!

"No_Spam" wrote:

> I have a drop down in my word document. It's not a list, but a form
> drop down (HTMLSelect) using the Web Tools toolbar. I want to
> populate
> this drop down with values from an Excel sheet. How do I do this? I'm
> connecting to Excel, but unable to figure out how to populate the drop
> down.
>
>

JodieM
Mon Feb 12 17:06:55 CST 2007

Hi,
this piece of code works well for me, it's in the initialize section
of the form with the drop down field on it. I got it from somewhere
else, but I'm not sure where.

Private Sub UserForm_Initialize()
Dim i As Integer
Dim aResults()

Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=\\server\path
\filename.xls;Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With
rsT.Open "Select * from YourNamedRange", cn, adOpenStatic


i = 0

With rsT
' This code populates the combo box with the values
' in the YourNamedRange named range in the .xls file. this example
table is 2 rows by 6 columns and is set as a named range.

Do Until .EOF
cboDiv.AddItem (i)
cboDiv.Column(0, i) = rsT.Fields(0).Value
cboDiv.Column(1, i) = rsT.Fields(1).Value
.MoveNext
i = i + 1
Loop

End With
End Sub


Hope that helps