combobox, fill with data of txt file

[Schorrie]

hello,

i have a combobox in my form which has to be filled with the data of a txt
file,

this is in my program code in the userform initialize sub

Dim i As Integer
Dim arrCombobox
i = FreeFile
Open c:\data\contents.txt For Input As #i
'store contents in array
arrCombobox = Split(Input(LOF(i), i), vbCrLf)
Close #i
'transfer array to list property of combo
Me.cmbCombobox1.List = arrCombobox

at home this works fine in MS Word XP

problem is that at work i am running MS Word 97,

everytime i try to start, i get an error on the Split command,

someone knows an answer

thanks, Schorrie

 

[Schorrie]

or is it the array that isn't recognized, don't know what the problem is???

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Schorrie,

Split meant something else in Word97 VBA. Press F1 after the word Split in
the VBE in Word 97 and you will see.

The following information is applicable to a combobox as well as a listbox
and would work in Word 97.

This routine loads a listbox with client details 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 Suppliers.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\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
' 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

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


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

 

[Schorrie]

hi dough

the .doc file, there has to be a table in it then,

is'nt it possible, like it is now, that it's only a .doc file, without
table, just a number of lines in which every line is a new item in the
combobox,

there's also another problem, when something new is entered in the textbox,
the program code sees or the item is already in the combobox list, if its
not, the new item will be entered by the program code into the .doc file so
the next time the user starts the program, there's a new item,

here's the code that does it

SEE IF THE ITEM IS ALREADY IN THE COMBO
Function ListItem_Exists(ByVal NewItem As String) As Boolean
Dim x As Integer
'run through combobox listitems to check
'whether NewItem exists
For x = 0 To Me.cmbPlaats.ListCount - 1
'match, function returns true
If NewItem = Me.cmbPlaats.List(x) Then
ListItem_Exists = True
Exit Function 'step out
End If
Next
'function remains false
End Function

'ADD NEW ITEM

Private Sub cmbPlaats_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim NewItem, DirDoc As String
Dim i As Integer
DirDoc = lblDirDoc.Caption
If Trim(cmbPlaats) = vbNullString Then Exit Sub
If Not ListItem_Exists(Trim(Me.cmbPlaats)) Then
NewItem = Me.cmbPlaats
If MsgBox("Wilt item toevoegen aan lijst standplaatsen: '" &
NewItem & "'", _
vbQuestion + vbYesNo) = vbYes Then
i = FreeFile
'add to textfile
Open DirDoc & "standplaats.txt" For Append As #i
Print #i, NewItem
Close #i
'repopulate
UserForm_Initialize
'show new listitem
Me.cmbPlaats = NewItem
End If
End If

End Sub

 

[Perry]

That's a familiar piece of code you got there Schorrie )

Split() isn't part of Office97. Was introduced in Office2K
You'll have to replace Split() function by some StringToArray function.

In below code, you Input() function reads the entire textfile
into one string.
Mind you, next to the field delimiter, the string will contain EOL (end of
line) signs
as well, in your case vbCR.
For a parsing function, that will store the contents of that string into an
array, this
is something you will have to take into account.

I've written a function listed below, that examplifies the above.
In Office97, parsing code looks ugly, brace yourself and be prepared )
Try to implement.
If you get stuck, kindly remail.

Situation:
textfile c:\temp\Book1.txt
Tab delimited, number of columns: 6

Krgrds,
Perry

== begincode

This is how your UserForm_Initialize() code will like
Private Sub UserForm_Initialize()
Dim i As Integer
Dim arrCombobox
i = FreeFile
Open "c:\temp\Book1.txt" For Input As #i
'store contents in array
MyString = Input(LOF(i), i)
arrCombobox = StringToArray(MyString, vbTab)
Close #i
'transfer array to list property of combo
Me.ListBox1.ColumnCount = 6
Me.ListBox1.Column = arrCombobox
End Sub

'The function StringToArray is listed here ...
Function StringToArray(ByVal MyString As String, _
ByVal Delimiter As String) As Variant

Dim MyArray() As Variant
Dim sLeft As String
Dim sPart As String
Dim lArray As Long
'adjust columnnumber according to your situation
Const numCols As Long = 5
Dim lCnt As Long

'first loop, for the EOL signs (vbCR)
While InStr(MyString, vbCr)
sLeft = Left$(MyString, InStr(MyString, vbCr) - 1)

'Second loop, dealing with field delimiter
While InStr(sLeft, Delimiter)

ReDim Preserve MyArray(numCols, lArray)
sPart = Left$(sLeft, InStr(sLeft, Delimiter) - 1)
sLeft = Right$(sLeft, Len(sLeft) - InStr(sLeft, Delimiter))
MyArray(lCnt, lArray) = sPart
lCnt = lCnt + 1
Wend
MyArray(lCnt, lArray) = sLeft
lCnt = 0
MyString = Right$(MyString, Len(MyString) - InStr(MyString, vbCr))
lArray = lArray + 1
Wend
'return array to function
StringToArray = MyArray

End Function
==end code

 

[Schorrie]

another question,

i got another piece of code from you, a few months ago, to see if the item
is already in the combobox,

if not, the item will be added to the txt file and will appear the next time
in the combobox

here's the code you wrote, is this code still allright,


---
'nazicht bestaand item
Function ListItem_Exists(ByVal NewItem As String) As Boolean
Dim x As Integer
'run through combobox listitems to check
'whether NewItem exists
For x = 0 To Me.cmbPlaats.ListCount - 1
'match, function returns true
If NewItem = Me.cmbPlaats.List(x) Then
ListItem_Exists = True
Exit Function 'step out
End If
Next
'function remains false
End Function

'toevoegen nieuw item
Private Sub cmbPlaats_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim NewItem, DirDoc As String
Dim i As Integer
DirDoc = lblDirDoc.Caption
If Trim(cmbPlaats) = vbNullString Then Exit Sub
If Not ListItem_Exists(Trim(Me.cmbPlaats)) Then
NewItem = Me.cmbPlaats
If MsgBox("Wilt item toevoegen aan lijst standplaatsen: '" &
NewItem & "'", _
vbQuestion + vbYesNo) = vbYes Then
i = FreeFile
'add to textfile
Open DirDoc & "standplaats.txt" For Append As #i
Print #i, NewItem
Close #i
'repopulate
UserForm_Initialize
'show new listitem
Me.cmbPlaats = NewItem
End If
End If

End Sub

 

[Perry]

here's the code you wrote, is this code still allright,

Do you have reason to believe it isn't ?
I'm inclined to answer yes ...

If not, y'll have to state the problem

Krgrds,
Perry

 

[Schorrie]

ok perry
i am testing the code you gave me, but i always get an error 9 while
starting,

 

[Schorrie]

if you want i send the testdoc.doc to you by mail

 

[Perry]

Err 9 - Out of Scope

What section of the code are you in, when the debugger
jumps in with above error?

I presume
If so, is the combobox cmbPlaats filled before you test it?

If not, you'll have to indicate in which other part the error occurs.

Krgrds,
Perry

 

[Schorrie]

Ok Perry

experimented again with the code,
now i get 'error 62 input after end of file'

the file book1.txt
should it be a special file, made with which program, is it a word document
or a simple txt file made with wordpad or something else???

 

[Schorrie]

in your code is also the line

Me.ListBox1.ColumnCount = 6

what columns do you mean,

is 6 the amount of items, if so, what if you add an item in the textfile,

??
thanks, Schorrie

 

[Schorrie]

Ok, me again,

did some testing,

it appends on the file which contains the items that should appear in the
combobox, which error i get,

if the file is a simple wordpad textfile
error 9
if the file is a simple word document
error 62, input after end of file

what kind of file (containing the items) should i use,

sorry to bother you, but i'm trying to get this to work

thanks, schorrie