Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    populating form combo box (word 2002/2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: populating form combo box (word 2002/2003)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populating form combo box (word 2002/2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •