Help with ADO Excel to Word Userform from closed workbook

Greetings all,

Long time lurker of the site and have found so many better ways to do things with examples already given, but first time I haven't already found my answer using search. So here goes this is for Word 2007 project I made.

I have templated letters (52 of them) which display a userform for simple data entry which I designed. The letters are sent to customers or companies, the userform uses variables and simple ways to import the entered data into the document. If someone would be kind enough to point me in the right direction, this is what I need.

• I need my userform to open a closed workbook when a command button is clicked and display a list showing the data contained within the workbook
• The list needs to be scrollable as it will have many entries 100+, so that the user may double click an entry to copy the data from the selected company & address to either textboxes on the userform in word or variables
• The data I am importing is on a network location I have company names & address which a lot of letters are sent to
• Basically instead of typing the company name & address the user could "load" from a master list on a workbook which could be updated (dynamic) making it easy to add new entries without coding

The workbook itself has the data Company Name from A2-100,Address Line 1 B2-100,,Address Line 2 C2-100,,Address Line 3,Address Line 4,Postcode the headings all in A1,B1,C1 etc..

I am aware that mail merge would possibly be useful here however, users are generally doing 1 document at a time and these must be made on demand rather than in batches. I have considered hard coding into the documents but this would be a nightmare in the event of a company changing address.

Thanks for any help :hi:

You could use the mailmerge filters to select just the record(s) you're interested in. Although mailmerges are designed for batch processing, they can be used for single records.

You could use the code near the end of http://www.gmayor.com/Userform_ComboBox.html to populate a userform list box This is the basic ADO coding to populate such a box (the code will populate either list or combo boxes, but for this type of task a list box is better).

If you want to see how it works in practice, see the Excel part of http://www.gmayor.com/Envelope_Label_Add_In.htm. (However that has the additional filtering shown below, to allow filtering by initial letter). You can select as many or as few addresses as you wish and you can insert them on envelopes, labels, letters etc. Somwhat similar (and includes the ability to add addresses to the worksheet without the need for users to manually access the worksheet) is http://www.gmayor.com/Address_Book.htm

The code including the filtering is:

Code:

---

Option Explicit
Private RS As Object
Private CN As Object
Private numrecs As Long, q As Long
Private strWidth As String
Private strFilter As String
Private strFilter2 As String
Private strFilter3 As String
Public Function xlFillList(ListOrComboBox As Object, _
                          iColumn As Long, _
                          strWorkbook As String, _
                          strRange As String, _
                          RangeIsWorksheet As Boolean, _
                          RangeIncludesHeaderRow As Boolean, _
                          ColumnTitle As String, _
                          Optional PromptText As String = "[Select Item]", _
                          Optional sLetterA As String, _
                          Optional sLetterB As String, _
                          Optional sLetterC As String)


    If RangeIsWorksheet = True Then strRange = strRange & "$]"

    Set CN = CreateObject("ADODB.Connection")


    If RangeIncludesHeaderRow Then
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Else
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
    End If


    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3

    RS.Open "SELECT * FROM [" & strRange, CN, 2, 1        'read the data from the worksheet

    If Not sLetterA = vbNullString And _
      Not sLetterB = vbNullString And _
      Not sLetterC = vbNullString Then
        strFilter = "([" & ColumnTitle & "] LIKE '" & sLetterA & "*') OR ([" _
                    & ColumnTitle & "] LIKE '" & sLetterB & "*') OR ([" _
                    & ColumnTitle & "] LIKE '" & sLetterC & "*')"
    ElseIf Not sLetterA = vbNullString And _
          sLetterB = vbNullString And _
          sLetterC = vbNullString Then
        strFilter = "[" & ColumnTitle & "] LIKE '" & sLetterA & "*'"
    Else
        strFilter = ""
    End If


    If TypeName(ListOrComboBox) = "ComboBox" Then
        If Not strFilter = vbNullString Then
            If Not strFilter2 = vbNullString Then
                strFilter = strFilter & "AND " & strFilter2
            End If
            If Not strFilter3 = vbNullString Then
                strFilter = strFilter & "AND " & strFilter3
            End If
        End If
    End If

    If TypeName(ListOrComboBox) = "ListBox" Then
        If strFilter = vbNullString Then
            If Not strFilter2 = vbNullString Then
                strFilter = strFilter & "AND " & strFilter2
            End If
            If Not strFilter3 = vbNullString Then
                strFilter = strFilter & "AND " & strFilter3
            End If
        End If
    End If

    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
        If Not strFilter = vbNullString Then
            On Error Resume Next
            .Filter = strFilter
        End If
    End With

    With ListOrComboBox
        .ColumnCount = RS.Fields.Count
        .Column = RS.GetRows(numrecs)

        strWidth = vbNullString
        For q = 1 To .ColumnCount
            If q = iColumn Then
                If strWidth = vbNullString Then
                    strWidth = .Width - 4 & " pt"
                Else
                    strWidth = strWidth & .Width - 4 & " pt"
                End If
            Else
                strWidth = strWidth & "0 pt"
            End If
            If q < .ColumnCount Then
                strWidth = strWidth & ";"
            End If
        Next q
        .ColumnWidths = strWidth
        If TypeName(ListOrComboBox) = "ComboBox" Then
            .AddItem PromptText, 0
            If Not iColumn - 1 = 0 Then .Column(iColumn - 1, 0) = PromptText
            .ListIndex = 0
        End If
    End With

    'Cleanup
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing

lbl_Exit:
    Exit Function
End Function

---

If you want to roll your own, and require more information on how to address the filtering, let me know.

gmayor thank you for the suggestions I am going to put something together and then if I can't figure something out will come back. Hopefully I can use these suggestions to make something of my own. I actually visited your site one day and decided to start a project so thanks for the inspiration :D