populating form combo box (word 2002/2003)

Topic

New Reply

We have a fax template. One of the combo boxes lists company names. These are picked up from the names of text files in an explorer folder. Would it be possible to direct the code to an excel file called c:textcompanynames.xls with the names typed in column A. So I would have one file rather than a folder of files.

The code used at present is

‘This subroutine is called when the form is first opened and it populates the drop list
‘with the company names held in the text file folder.
P = “c:text*.txt”
F = Dir(P)

Do
‘loop through populating the drop list with the names of the address files minus the “.txt” on the end.
F = Left(F, Len(F) – 4)
cboCompany.AddItem F
F = Dir()

If F = “” Then
Exit Do
End If
Loop
End If

Many thanks

Roberta

Reply | Quote

Replies

Here is code that uses Excel VBA:

Private Sub Form_Initialize()
Dim objXL As Object
Dim objWb As Object
Dim objWs As Object
Dim i As Long
Dim n As Long

On Error GoTo ErrHandler

Set objXL = CreateObject(“Excel.Application”)
Set objWb = objXL.Workbooks.Open(“F:ExcelList.xls”)
Set objWs = objWb.Worksheets(“Sheet1”)

n = objWs.Range(“A65536”).End(-4162).Row ‘ -4162 = xlUp
For i = 1 To n
cboCompany.AddItem objWs.Range(“A” & i)
Next i

ExitHandler:
On Error Resume Next
objWb.Close SaveChanges:=False
objXL.Quit

Set objWs = Nothing
Set objWb = Nothing
Set objXL = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

It uses late binding, so you don’t need to set a reference to the Excel object library.

Reply | Quote

One possibility is to use VBS scripting (from Word) to create such a text file listing. You could then use that file to populate the list. This Quick and simple directory listing in VBS might be of use.
BTW, is there a particular reason for wanting an intermediate directory listing file?

Alan

Reply | Quote