Consulting

Results 1 to 4 of 4

Thread: Help with ADO Excel to Word Userform from closed workbook

  1. #1

    Question 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
    Last edited by electronicpi; 12-03-2014 at 04:43 PM. Reason: Added Version info

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    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:

    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    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

Posting Permissions

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