Help with ADO Excel to Word Userform from closed workbook

[electronicpi]

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:

[macropod]

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.

[gmayor]

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.

[electronicpi]

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