Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2007
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating a combo box from an external file (MS Word 2003 SP1)

    Morning everyone

    I am looking to populate a combobox in one of my forms. I want to have a central file (doesnt matter what it is, Excel, CSV etc) that is updated centrally but it called from within the document to populate the combobox with the relevent information ( ie populating the list of people in the company). I am at a loss as to how this is done so any help would be much appreciated.

    Thanks in advance

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

    Re: Populating a combo box from an external file (MS Word 2003 SP1)

    Welcome to Woody's Lounge!

    You could use a plain text file, with one name per line. You can read the names form the file in the Initialize event of the form:

    Private Sub UserForm_Initialize()
    Dim f As Integer
    Dim strLine As String

    On Error GoTo ErrHandler

    f = FreeFile
    ' Modify the path and filename as needed
    Open "P:CommonList.txt" For Input As #f
    Do While Not EOF(f)
    Line Input #f, strLine
    Substitute the name of your combo box
    Me.ComboBox1.AddItem strLine
    Loop

    ExitHandler:
    Close #f
    Exit Sub

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

  3. #3
    Lounger
    Join Date
    Mar 2007
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a combo box from an external file (MS Word 2003 SP1)

    Thanks Hans that works perfectly. How easy is it to create an excel spreadsheet (again doesnt have to be this format) with multiple pieces of information that fills in a number of fields ie email, DDI etc when the user selects a person from the drop down list? This would also be froma central file if possible so the maintenance would be in one place.

  4. #4
    Lounger
    Join Date
    Mar 2007
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a combo box from an external file (MS Word 2003 SP1)

    sorry didnt read the first line of your post (one of those days beleive me) - I am glad to be in the lounge, you have already saved me hours of work so thankyou.

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

    Re: Populating a combo box from an external file (MS Word 2003 SP1)

    The code to read an Excel worksheet is a bit more complicated. I have attached a zip file with a sample Word document and Excel workbook.
    Before trying out the userform in the document, you must substitute the correct path to the workbook.

    The combo box has 4 columns, of which 3 are hidden by setting their width to 0 in the ColumnWidths property.
    The code in the Initialize event of the userform uses Automation to open the workbook and read the data into the 4 columns of the combo box. Automation means controlling one application in code from another one.
    The Change event of the combo box uses these hidden columns to populate text boxes.

    Post back if you have questions.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Mar 2007
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a combo box from an external file (MS Word 2003 SP1)

    thanks very much Hans, your a genius and I now have a perfectly working Letter template. If I ever meet you I will buy you a beer.

Posting Permissions

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