• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Population of multi select field in word with data from txt Outlook or Excel file

Dear Experts,
a while ago I asked and received a nice solution how to populate pull down field with data from an excel file. Now I need a similar solution but this time I need multi choice field population from an Excel file and as alternative also from a txt file as this works a bit quicker and from Outlook. E.g. it would be perfect to have a multi select field in a word form which gets its data from the full name field from a contact folder located in the public folder structure.
Any help in any direction would very helpful.
Thanks a lot!
0
Petersburg1
Asked:
Petersburg1
  • 5
  • 4
2 Solutions
 
NorieCommented:
Do you mean a dropdown added to a Word document?

As far as I'm aware they can't be used for multiple selections.

You would need another type of field/control to allow multiple selection.

An ActiveX ListBox control would allow you to list and select multiple values.
0
 
NorieCommented:
Here's some very simple code that will add a listbox to a document and populate it with data from an Excel workbook or from a text file.

The data needs to be in Sheet1 of the workbook in column A starting at row 2 (first row is a header?).

The Outlook thing might be a bit more complicated, for example how would the contact folder to use be determined?
Option Explicit

Sub CreateAndPopulateListbox()
Dim ctlLB As MSForms.ListBox
Dim I As Long

    ' ListboxLocation is bookmark for the where the listbox should go in the document
    
    Set ctlLB = ActiveDocument.Bookmarks("ListboxLocation").Range.InlineShapes.AddOLEControl(ClassType:="Forms.ListBox.1").OLEFormat.Object
    
    ctlLB.MultiSelect = fmMultiSelectMulti

    PopulateLB ctlLB, "File" ' populate from text file
    
    PopulateLB ctlLB, "File" ' populate from text file
End Sub

Sub PopulateLB(ctlLB As MSForms.ListBox, Optional Source = "Excel")
Dim appXL As Object
Dim wbXL As Object
Dim wsXL As Object
Dim rngXL As Object
Dim strItem As String
Dim FF As Long

    Select Case Source
        Case "Excel"

            Set appXL = CreateObject("Excel.Application")

            Set wbXL = appXL.Workbooks.Open("C:\WordListBoxData.xlsm")

            Set wsXL = wbXL.worksheets("Sheet1")

            Set rngXL = wsXL.Range("A2")

            While rngXL.Value <> ""

                strItem = rngXL.Value

                ctlLB.AddItem strItem

                Set rngXL = rngXL.Offset(1)
            Wend

            wbXL.Close False

            Set wbXL = Nothing

            appXL.Quit

            Set appXL = Nothing
        Case "File"
            FF = FreeFile

            Open "C:\WordListboxData.txt" For Input As #FF

            Do
                Line Input #FF, strItem

                ctlLB.AddItem strItem

            Loop Until EOF(FF)

            Close #FF
        Case "Contacts"
                 ' <<<<<<<< TO DO >>>>>>>>
    End Select


End Sub

Open in new window

0
 
Petersburg1Author Commented:
Hi imnorie:
thanks for the answer, I will check your code soon.
As I understood this is for Excel, how it should look like for txt file?
If I want to have not only one Listbox populated from that excel file but a second list box on the same word document with data from the same excel file but from another sheet and column B?
I don't know VBA...would be very helpful if you could add max amount of comments into the code so it will be easier for me in future to adapt to the needed situation/environment.

Outlook I can put into a separate question not to overload this one here but to answer your question:
The Outlook folder is located in the public folder structure and the path to this folder would look like this:
AssetCodingFldr= "Public Folders\All Public Folders\Jensen Group\Internal\JG-Asset-Coding"
This is a contact folder with a customized contact form and from here I would like to get a data export from certain fields to certain columns in Excel on button click
thanks
Nils

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
NorieCommented:
Nil

It's for Excel and a text file.

It was late when I wrote it but I can add comments, and review it later.

Adding another listtbox further data from Excel shouldn't be a problem.3

The only thing I'm wondering about is where you would want the listbox(es) located.

I tested the code on a blank document and just placed the listbox right at the start.

The difficulty I saw with Outlook was that you seem to be saying that the user would navigate
to a folder of contacts.

If it's a specific Outlook folder with a known, fixed path that might not be an issue.

Anyway, like I said I'll review and comment the cod.

One bit I definitely forgot was how to set an Excel workbook and text file to test the code.

I can do that if you want, or even attach files for both, and a file for the code.
0
 
Petersburg1Author Commented:
Hi Imnorie,
I have attached the word file with a list box for the data from a txt file and a second list box for data from an excel file.
but it does not work....?

In regards of Outlook:
It is of course a fixed folder in the public folder structure. Please would be nice if you could take that file as a base and try to get it working...
thanks
Nils
In order to be able to upload the file I deleted the docm extension....

Listbox
0
 
NorieCommented:
The code I posted created a listbox, it will need to be altered to work with existing controls.

Here's the code with added comments.
Option Explicit


Sub CreateAndPopulateListbox()
Dim shp As InlineShape
Dim ctlLB As MSForms.ListBox
Dim I As Long


    ' create reference to first listbox
    Set shp = ThisDocument.InlineShapes(1)

    Set ctlLB = shp.OLEFormat.Object

    ' set properties of listbox for multiselect with checkboxes
    ctlLB.ListStyle = fmListStyleOption
    ctlLB.MultiSelect = fmMultiSelectMulti

    ' populate first listbox from text file WordListboxData.txt
    PopulateLB ctlLB, "File", "C:\WordListboxData.txt"

    ' create reference to second listbox
    Set shp = ThisDocument.InlineShapes(2)

    ' set properties of listbox for multiselect with checkboxes
    Set ctlLB = shp.OLEFormat.Object    ' AddOLEControl(ClassType:="Forms.ListBox.1").OLEFormat.Object

    ctlLB.ListStyle = fmListStyleOption
    ctlLB.MultiSelect = fmMultiSelectMulti

    ' populate second listbox from Excel workbook  file WordListboxData.xlsx
    PopulateLB ctlLB, "Excel", "C:\WordListBoxData.xlsx"

End Sub

Sub PopulateLB(ctlLB As MSForms.ListBox, Source As String, strDataFile As String)
' populate a listbox from an Excel workbook or text file
' strDataFile should contain the path and name of file with data

Dim appXL As Object
Dim wbXL As Object
Dim wsXL As Object
Dim rngXL As Object
Dim strItem As String
Dim FF As Long

    ' check if data file exists
    If Len(Dir(strDataFile)) = 0 Then
        MsgBox "File " & strDataFile & " not found." & vbCrLf & vbCrLf & "Please check path and filename", vbInformation + vbOKOnly, "File Not Found"
    End If

    ' populate listbox based on value of Source argument
    Select Case Source


        Case "Excel"
            ' populate listbox with data from Excel workbook

            ' create an instance of Excel
            Set appXL = CreateObject("Excel.Application")

            ' open file with data
            Set wbXL = appXL.Workbooks.Open(strDataFile)

            ' set reference to workshet with data for listbox
            Set wsXL = wbXL.worksheets("Sheet1")

            ' set range in worksheet where data starts
            Set rngXL = wsXL.Range("A2")

            ' loop down from range set above until blank cell found
            While rngXL.Value <> ""

                strItem = rngXL.Value

                ' add data item to listbox
                ctlLB.AddItem strItem

                ' move down a cell
                Set rngXL = rngXL.Offset(1)
            Wend

            ' Finished so close workbook, quit Excel and tidy up
            wbXL.Close False

            Set wbXL = Nothing

            appXL.Quit

            Set appXL = Nothing

        Case "File"
            ' populate listbox with data from text file

            FF = FreeFile

            ' open data file
            Open strDataFile For Input As #FF

            ' loop through data file till end of file,
            ' adding each line as an item to the listbox
            Do
                Line Input #FF, strItem

                ctlLB.AddItem strItem

            Loop Until EOF(FF)

            ' close data file
            Close #FF
        Case "Contacts"

            ' populate listbox with data from Outlook contacts folder
            ' <<<<<<<< TO DO >>>>>>>>
    End Select


End Sub

Open in new window


0
 
Petersburg1Author Commented:
Hi imnorie:
Sorry for the long break...I still did not find time to check it...did not forget you. Thanks
Nils
0
 
Petersburg1Author Commented:
late thank you!
0
 
NorieCommented:
Nils

Thank you, and apologies for the all the spelling mistakes and typos.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now