Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2014, 09:21 AM
Faldinio Faldinio is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 64bit
Novice
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data
 
Join Date: Oct 2014
Posts: 4
Faldinio is on a distinguished road
Default Populate Word Drop-down list with Excel column then auto fill form fields with Excel data

Hi,



I need to auto populate a drop down list with Excel data for example, Column A in 'data.xlsx'. Which I have managed to do by searching the forum and finding a macro to do it.

I now need other form fields to populate based on the selection in the drop down list.

I am not an expert in Word as my experience is based more in Excel.
I basically need the Word template to do a VLOOKUP based on what the user selects in the first drop down box, then populate about 5 other fields automatically.

Any help would be much appreciated. If giving advice, please remember that I'm not an expert in Word so may need more detailed instructions!

Mark
Reply With Quote
  #2  
Old 10-18-2014, 01:42 PM
macropod's Avatar
macropod macropod is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Have you considered using a mailmerge and selecting just the record(s) you're interested in? No code required. What you're asking for is in most respects the same as what a mailmerge would do, the only difference being that mailmerges are designed with multiple records in mind.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-18-2014, 02:39 PM
Faldinio Faldinio is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 64bit
Novice
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data
 
Join Date: Oct 2014
Posts: 4
Faldinio is on a distinguished road
Default

I already created a mailmerge version but my client wants to be able to select a via a dropdown list rather than using mailmerge to 'find' a single recipient - as it only has a blank text field or browsing through all the records (there are around 500 entries).
Reply With Quote
  #4  
Old 10-18-2014, 06:07 PM
macropod's Avatar
macropod macropod is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

A drop-down list in Word would have the same 500 entries. I can't see how that's an improvement over what mailmerge offers - without the need for any code. But, since it seems you're wedded to a macro solution and the same issue has been addressed that way before, see: https://www.msofficeforums.com/word-...drop-down.html

PS: Your thread title mentions formfields. A formfield dropdown is limited to 25 entries...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 10-18-2014, 10:03 PM
gmayor's Avatar
gmayor gmayor is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

As Paul implies Form Field drop boxes are limiting (the 25 item list limit is a particular brick wall) and as you would need to use VBA to do what you require in any case, then his suggestion of mail merge is a good one.

However if you are going to use macros then rather than use a form field for the dropdown, you could employ instead a userform with a list box or combo box populated from the Excel worksheet. The box could hold all the fields from each record and thus lookup wouldn't be required. The form fields (if you need to use them) could be populated directly from the columns of the userform.

See http://www.gmayor.com/Userform.htm and http://www.gmayor.com/Userform.htm.

For a more in depth explanation, see http://gregmaxey.com/Create_and_employ_a_UserForm.htm

If you want to get adventurous, you could limit the display by a selected initial letter, but let's not run before we can walk .
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 10-19-2014, 03:24 AM
Faldinio Faldinio is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 64bit
Novice
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data
 
Join Date: Oct 2014
Posts: 4
Faldinio is on a distinguished road
Default

Following on from the tutorials you listed, I think a User Form will suffice.

I have tried to follow the instructions here: http://www.gmayor.com/Userform_ComboBox.html

However I cannot get it to work. There are no errors, but the ComboBox remains empty - it isn't populating it.

The code is exactly as in the tutorial

Code:
Option Explicit
Private rs As Object
Private cn As Object
Private numrecs As Long, q As Long
Private strWidth As String

Public Function xlFillList(ListOrComboBox As Object, _
strWorkbook As String, _
strRange As String, _
bisRangeASheet As Boolean)
'A function to fill a list or combo box with data from an Excel worksheet or a named range in a worksheet
'ListOrComboBox is the name of the list or combo box
'strWorkbook is the name of the Excel data file
'strRange is the part of the data file to be used
'bisRangeASheet - is the range 'strRange' a sheet (true) or a named range (false)


Set cn = CreateObject("ADODB.Connection")

'Alternative connection 1
'*********************************
cn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'*********************************

'Alternative connection 2
'*********************************
'cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
'cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strWorkbook
'cn.Open
'*********************************

Set rs = CreateObject("ADODB.Recordset")
If bisRangeASheet = True Then
'strRange is a named worksheet use:
rs.Open "SELECT * FROM [" & strRange & "$]", cn, 2, 1 'read the data
Else
'strRange is a named range use:
rs.Open "SELECT * FROM [" & strRange & "]", cn, 2, 1 'read the data
End If

With rs
.MoveLast 'find the last record
numrecs = .RecordCount 'and note its number
.MoveFirst 'return to the start
End With

With ListOrComboBox 'load the records into the columns of the named list/combo box
.ColumnCount = rs.Fields.Count
.Column = rs.GetRows(numrecs)

'set the widths of the combo/list box columns (optional)
'the alternative is to display all the columns

'***********************
 strWidth = .Width - 2 & " pt;"
 For q = 2 To .ColumnCount
 strWidth = strWidth & "0 pt"
 If q < .ColumnCount Then
 strWidth = strWidth & ";"
 End If
 Next q
 .ColumnWidths = strWidth
'***********************
End With

'Cleanup
If rs.State = 1 Then rs.Close
If cn.State = 1 Then cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Sub CallUF()

Dim oFrm As UserForm1
Dim oVars As Word.Variables
Dim strTemp As String
Dim oRng As Word.Range
Dim i As Long
    
  Set oFrm = New UserForm1
  With oFrm
    .Show
  End With
    

End Sub
This is called when the UserForm is initialised.

Code:
Private Sub UserForm_Initialize()

xlFillList UserForm1.ComboBox1, "E:\path_to_file\test.xlsx", "Sheet1", True

End Sub
If the filename is incorrect then an error occurs, so I know it is checking for the data, it's just not putting it in the ComboBox...

Can anyone guide me here?

Thanks in advance.


Mark
Reply With Quote
  #7  
Old 10-19-2014, 04:57 AM
gmayor's Avatar
gmayor gmayor is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The problem relates to your initialization string which includes

UserForm1.ComboBox1

whereas you have called the userform oFrm from the calling macro

Change it to

Me.ComboBox1

or just ComboBox1


but try the following instead, which will allow you to configure which column to display and set a prompt text for combo boxes.

Code:
Option Explicit
Private RS As Object
Private CN As Object
Private numrecs As Long, q As Long
Private strWidth As String
Public Function xlFillList(ListOrComboBox As Object, _
                           iColumn As Long, _
                           strWorkbook As String, _
                           strRange As String, _
                           RangeIsWorksheet As Boolean, _
                           RangeIncludesHeaderRow As Boolean, _
                           Optional PromptText As String = "[Select Item]")


    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

    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    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
Call it with

Code:
Private Sub UserForm_Initialize()
    xlFillList UserForm1.ComboBox1, 1, "E:\path_to_file\test.xlsx", "Sheet1", True, True
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 10-19-2014, 06:03 AM
Faldinio Faldinio is offline Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Windows 7 64bit Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Office 2010 64bit
Novice
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data
 
Join Date: Oct 2014
Posts: 4
Faldinio is on a distinguished road
Default

Excellent thank you so much. That works a treat.

I'll carry on now with filling the document variables with the data and make sure I can do it right!
Reply With Quote
Reply

Tags
auto-fill, content controls, cross platform

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Auto Populate Word From to Excel File webber Word 1 10-02-2013 02:52 PM
Auto-populate from form fields kenelder Word 3 05-23-2013 07:50 AM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data drop down list from external data source Excel 2007? Intruder Excel 1 08-03-2012 05:41 AM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248 Joe Patrick Word VBA 2 01-30-2012 07:23 AM
Word Fill-in form with Excel spreadsheet karik Word 0 01-11-2010 08:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft