Populate drop down list from txt file

[Lee Kiwiflame]

I have six templates that have a drop down list which lists Committee Names.
I want to have a txt file with the Committee Names and each template reads
the names from the txt file, then populates a dropdown list.

It would be so much easier to change a txt file than having to change six
different templates each time a new committee is added and/or name changed.

Any help is greatly appreciated.

 

[Doug Robbins - Word MVP]

If you have the names in a file C\Committee.txt in the following format

[Lists]
Members=Member One Name|Member Two Name|Member Three Name|etc

Then the following command in the Initialize() event of a userform will load
a combobox, cmbCommittee with the names:

cmbCommittee.List() = Split(System.PrivateProfileString("c:\Committee.txt",
"Lists", "Members"), "|")

Alternatively, this routine loads a listbox (or combobox) with client
details (more than just the name if necessary) stored in a table in a
separate 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 Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
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:\Company.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
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.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
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Lee Kiwiflame]

Thank you. I have gone with having client details stored in a table in a
separate document.

I need to re-use most of that code on three different forms within the same
template. The only things that will be different is the sourcedoc and the
name of the listbox I load the array into, e.g. if I needed to read another
doc and load it to another listbox?

Also, how could I re-use the code to load the same sourcedoc into two
different listboxes? The listboxes are on two different forms within the
same template.

Regards
Lee

 

[Doug Robbins - Word MVP]

I am not sure why you would have so many forms within the one template.

See the following page of fellow MVP, Greg Maxey's website
http://gregmaxey.mvps.org/Repeating_Data.htm

and some of the other pages on Greg's website

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Lee Kiwiflame]

Sorry, I said three forms but it is only two forms within one template. The
template is for Minutes. The minutes template is used for different
committees, sub-committees and workshops.

Sub-Committee and Workshop minutes get confirmed at another committee
therefore, users need to pick the name of the sub-committee AND the name of
the committee that confirms the minutes. A sub-committee / workshop has a
chairperson but the meeting where the minutes are confirmed has a different
chairperson and the minutes need to reflect this.

Hence why I have 2 forms. Thanks for your help.

 

[Lee Kiwiflame]

I have the following code which I know is wrong but I want to re-use the Sub
GetCommitteeInfo( ) code. There are two forms: one called frmMinutes and the
other frmDifCom.

I need to populate frmMinutes and frmDifCom with the Committee list.

Sub GetCommitteeInfo()
'Opens and reads Committees.doc then loads it into an array

Dim myArray() As Variant
Dim sourcedoc As Document
Dim i, j As Integer
Dim myitem As Range
Dim m, n As Long
Application.ScreenUpdating = False
'Set the file location and name of document to open
Set sourcedoc = Documents.Open(FileName:="M:\NEW -
TEMPLATES\_TEMP\Committees.doc")
'Get the number of table rows (i.e. - 1 if header row is used)
i = sourcedoc.Tables(1).Rows.Count - 1
''Get the number of table columns
j = sourcedoc.Tables(1).Columns.Count
'Load list members into an array
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
myitem.End = myitem.End - 1
myArray(m, n) = myitem.Text
Next m
Next n

End Sub

Sub CommitteeList()

Call Insert.GetCommitteeInfo

'Populate the Dropdown List using the array
frmMinutes.CboCommittee.List() = myArray
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

End Sub

Sub CommitteeList2()

Call Insert.GetCommitteeInfo

'Populate the Dropdown List using the array
frmDifCom.CboCommittee2.List() = myArray
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

End Sub

 

[Doug Robbins - Word MVP]

The following loads comboboxes on two forms in the same template with the
data from the one source file:

Sub ShowFOrms()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range
Dim m As Long, n As Long
' Modify the path in the following line
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(filename:="c:\Company.doc")
' Get the number or clients = number of rows in the table of client
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
Form1.ComboBox1.ColumnCount = j
Form2.ComboBox1.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
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
Form1.ComboBox1.List = MyArray
Form2.ComboBox1.List = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
Form1.Show vbModeless
Form2.Show vbModeless
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP