Populate combo box from spreadsheet?

[Chuck H]

Is there any way to populate a combo box from a spreadsheet? Back in January somone generously posted the code that appears below, as a way to populate a combo box from a Word table. I can't use a Word doc table as the data source (for various reasons). Instead I have a data export file (.csv) that will be updated daily from an external data source that I'd like to use as a data/row source for a combo box.

It would be great if the spreadsheet cell references could be used instead of the table cell referernces in the code below. However I'm not sure how to do this

Alternatively I could open the .csv file, count the lines (assuming each line is a row), then read each line, split the strings on the commas to get the individual colum values and load them into an array.

Any thoughts greatly appreciated -- thanks

Previous posted code follows

This routine loads a listbox or a combobox with client details stored in a
table in a separat
document (which makes it easy to maintain with additions, deletions etc.)
that document being saved as Clients.Doc for the following code

On the UserForm, have a list box (ListBox1) and a Command Butto
(CommandButton1) and use the following code in the UserForm_Initialize() an
the CommandButton1_Click() routine

Private Sub UserForm_Initialize(
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range
m As Long, n As Lon
' Modify the path in the following line so that it matches where yo
saved Suppliers.do
Application.ScreenUpdating = Fals
' Open the file containing the client detail
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc"
' Get the number or clients = number of rows in the table of clien
details less on
i = sourcedoc.Tables(1).Rows.Count -
' Get the number of columns in the table of client detail
j = sourcedoc.Tables(1).Columns.Coun
' Set the number of columns in the Listbox to matc
' the number of columns in the table of client detail
ListBox1.ColumnCount =
' Define an array to be loaded with the client dat
Dim MyArray() As Varian
'Load client data into MyArra
ReDim MyArray(i, j
For n = 0 To j -
For m = 0 To i -
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Rang
myitem.End = myitem.End -
MyArray(m, n) = myitem.Tex
Next
Next
' Load data into ListBox
ListBox1.List() = MyArra
' Close the file containing the client detail
sourcedoc.Close SaveChanges:=wdDoNotSaveChange
End Su

 

[Doug Robbins - Word MVP]

The procedure in the article “Load a ListBox from a Named Range in Excel
using DAO†at:

http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

will also work for a combobox.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Is there any way to populate a combo box from a spreadsheet? Back in

January somone generously posted the code that appears below, as a way to
populate a combo box from a Word table. I can't use a Word doc table as the
data source (for various reasons). Instead I have a data export file (.csv)
that will be updated daily from an external data source that I'd like to use
as a data/row source for a combo box.

It would be great if the spreadsheet cell references could be used instead

of the table cell referernces in the code below. However I'm not sure how
to do this.

Alternatively I could open the .csv file, count the lines (assuming each

line is a row), then read each line, split the strings on the commas to get
the individual colum values and load them into an array.