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

Population of pull down fields via data from Excel file

Dear Experts,
We are using MS Office 2007. To connect/link  excel files with other excel files to pull data is quite known and easy to realize. We have an excel file on the server which does contain a lot of "basic" information which is used constantly and allover in various other excel files and word files.
This are e.g. certain abbreviations, employee names etc.
In order to improve standardization, reduce spelling mistakes and work I'm in the process to link different files with the "coding" file. In terms of Excel it works fine but how I can populate pull down fields in Word forms with data stored in columns in Excel?

How to make sure that word is pulling last valid data in alphabetical order and without duplicates into the pull down fields upon opening?

Looking for an add in or existing but hidden function, macro etc. to realize that task. Depending on the MS Word form I would need to populate one up to 5 different pull down field with data all located in one and the same excel file but partly in different sheets.

Looking forward to get help here. I have only very basic VBA knowledge, thank you in advance for keeping this in mind.
Nils
0
Petersburg1
Asked:
Petersburg1
  • 9
  • 8
1 Solution
 
RobSampsonCommented:
Hi, here's some code from here:
http://stackoverflow.com/questions/3974618/populate-a-dropdown-in-word-from-column-in-excel-and-then-on-dropdown-change-popu

that will populate the drop down box in Word from an Excel spreadsheet.

The datasource is CCustomers.xls so change that to a UNC path if required.

The Sheet for the data is Customer as given here:
rsT.Open "Select distinct * from Customer", cn, adOpenStatic

so change that as required.

Finally, change
rsT.Fields(0).Value

such that you reference the column index for the data that is to be in the drop down box.  Zero refers to column A, one to column B, and so on.

Regards,

Rob.
Private Sub UserForm_Initialize() 
Dim i As Integer 
Dim cn As ADODB.Connection 
Dim rsT As New ADODB.Recordset 
Set cn = New ADODB.Connection 
With cn 
.Provider = "Microsoft.Jet.OLEDB.4.0" 
.ConnectionString = "Data Source=CCustomers.xls;Extended Properties=Excel 8.0;" 
.CursorLocation = adUseClient 
.Open 
End With 
rsT.Open "Select distinct * from Customer", cn, adOpenStatic 

i = 0 

With rsT 
' This code populates the combo box with the values 
' in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range. 

Do Until .EOF 
ComboBox_Company.AddItem (i) 
ComboBox_Company.Column(0, i) = rsT.Fields(0).Value 
.MoveNext 
i = i + 1 
Loop 
End With 
End Sub

Open in new window

0
 
Petersburg1Author Commented:
Dear Rob,
thanks for your help so far. I tried to have it working but so far it won't. Maybe this is because of placing or linking the macro wrongly...
I have saved a test file as macro enabled word document, changed the macro security settings and made adaptations as you advised but so far without result.
Please see attached pdf file with screen shots.
thanks in advance for further help.
Nils

PulldownpopulationinwordviaExcel.pdf
0
 
RobSampsonCommented:
OK, I've tested the code.  Put this in the Word document as the Sub Document_Open()

Regards,

Rob.
Private Sub Document_Open()
Dim i As Integer
Dim cn As New ADODB.Connection
Dim rsT As New ADODB.Recordset

Set cn = New ADODB.Connection
Dim strSource As String
strSource = "C:\Temp\Scripts\ComboBoxValues.xlsx"
strSheetName = "ComboBoxValues"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & strSource & """;Extended Properties=""Excel 12.0; HDR=YES;"""
strSQL = "SELECT * FROM [" & strSheetName & "$]"
Set rsT = New ADODB.Recordset
Set rsT = cn.Execute(strSQL)

i = 0

With rsT
' This code populates the combo box with the values
' in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range.

Do Until .EOF
ComboBox1.AddItem (i)
ComboBox1.Column(0, i) = rsT.Fields(0).Value
.MoveNext
i = i + 1
Loop
End With
End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Petersburg1Author Commented:
Hi Rob,
does not work...there is a compile error. See screenshot.
I have adapted your tested code to my excel file, my sheet name and my column. It is the B Column.

What is meant by "in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range."

How the code must be adapted/extended if I have not only one but a couple of pull down fields to populate with other columns from the same file and same sheet and other sheet? I can ask this as a separate question after I have at least one field running.

And as a third question I will ask how to populate a field depending on the value I have chosen in the pull down box. I will choose Nils in the pull down and as result this will populate a read only text box with the job title of Nils which also comes from that excel file. In Excel I have solved this with vlookup.

But back to question 1
Maybe it would be easier if you could send me your word and excel test file?

thanks


Pulldownfield.jpg
0
 
RobSampsonCommented:
Sorry I forgot to tell you that you need to click Tools --> References and add Microsoft ActiveX Data Object 6.0 Library.

That will get past that error, and it should work.  It worked for me.

To obtain column B values, change
ComboBox1.Column(0, i) = rsT.Fields(0).Value

to
ComboBox1.Column(0, i) = rsT.Fields(1).Value

See how that goes, and we'll address anything else after that.

Regards,

Rob.
0
 
Petersburg1Author Commented:
Hi Rob,
ok, thanks...but what if there is only Microsoft ActiveX Data Object 2.8 Library?
MS Office 2007 including service packs...maybe during "install" something need to be added?
thanks
Nils

Microsoft-ActiveX-Data.jpg
0
 
RobSampsonCommented:
Are you on Windows XP?  If so, that's the latest you've got.  Version 6.0 is the Vista and Windows 7 equivalent, as far as I can tell.  That should still work.  Tick it, and see what it does.

Regards,

Rob.
0
 
Petersburg1Author Commented:
Hi Rob,
we are coming closer..yeaah! :-) it works!
However not perfectly

See screenshot:
Although there is the debugger message the pull down is populated with the correct values, however the values have a heater and actually start in the second row. So the code must give a possibility to define not only the column but also the starting row. In almost all cases it will be the second row, sometimes third row.

How about get a sort listed by alphabet and without duplicates? Next question? I'm fine with that! Also for sure next question: What need to be done if I want:
1. Several pull down fields in one word document and dependency. Choice of pull down will fill a text field with the respective value...
thanks
Nils




TypeMismatch.jpg
0
 
RobSampsonCommented:
Hi, glad we're getting there....it's always good when things work ;-)

So, to have no duplicates, try changing this:
strSQL = "SELECT * FROM [" & strSheetName & "$]"

to this
strSQL = "SELECT DISTINCT * FROM [" & strSheetName & "$]"

If you want to actually specify a Named Range in the sheet to read from, you can name the range (Select the range in Excel, then give it a name), and the query can read from that specific range using:
strSQL = "SELECT * FROM [YourNamedRange]"

where YourNamedRange is the name you gave it.

However, if you want it sorted, we would need to either sort the data before hand, or temporarily write it somewhere else to sort (can be to memory), then add it to the list box.

As for the Type Mismatch....are there Null values within the range?

Try this code to have it sort, and also try to omit any Null values.  Feel free to change the query to point to your named range, if you make one.

Regards,

Rob.
Private Sub Document_Open()
Dim i As Integer
Dim cn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim dctData As New Dictionary

Set cn = New ADODB.Connection
Dim strSource As String
strSource = "C:\Temp\Scripts\ComboBoxValues.xlsx"
strSheetName = "ComboBoxValues"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & strSource & """;Extended Properties=""Excel 12.0; HDR=YES;"""
strSQL = "SELECT DISTINCT * FROM [" & strSheetName & "$]"
Set rsT = New ADODB.Recordset
Set rsT = cn.Execute(strSQL)

i = 0

Set dctData = New Dictionary

Do Until rsT.EOF
    If Not IsNull(rsT.Fields(0).Value) Then dctData.Add rsT.Fields(0).Value, 0
    rsT.MoveNext
Loop

SortArray dctData

For Each strValue In dctData
    ComboBox1.AddItem (i)
    ComboBox1.Column(0, i) = rsT.Fields(0).Value
    i = i + 1
End If

End Sub

Private Sub SortArray(ByRef aTempArray)
  Dim iTemp, jTemp, strTemp

  For iTemp = 0 To UBound(aTempArray)
    For jTemp = 0 To iTemp

      If StrComp(aTempArray(jTemp), aTempArray(iTemp)) > 0 Then
        'Swap the array positions
        strTemp = aTempArray(jTemp)
        aTempArray(jTemp) = aTempArray(iTemp)
        aTempArray(iTemp) = strTemp
      End If

    Next
  Next
End Sub

Open in new window

0
 
Petersburg1Author Commented:
Hi Rob,
one step for and one back :-)
Now I have a failure message again and population does not work. :-(

Did I understand you correctly...If I want that column b will start with the entries as of the 2 row it would be better to have a name space instead?
Is there not a parameter which allows me to define to start at 2nd row as I'm allowed to switch from A to the B column?
But ok, I can have a name range as well....
The source does not have any zero entries!
thanks for all your efforts.
Nils

compileerror.pdf
0
 
RobSampsonCommented:
Ok, that was my mistake. I lead you down the wrong path in the end.  The dictionary object isn't required, and a simple array with suffice.  I have attached an example using a word document and a named range in an excel spreadsheet.

To modify the named range if you want, you can follow the steps here:
http://www.dummies.com/how-to/content/managing-range-names-in-excel-2007.html

You can find the sample here:
https://filedb.experts-exchange.com/incoming/ee-stuff/8100-ComboBoxSample.zip 

Hope that helps.

Regards,

Rob.
0
 
RobSampsonCommented:
PS. That link is not suggesting that I think you're a "dummy"....it's just a reference I found for the Named Ranges....sorry....
0
 
Petersburg1Author Commented:
Hi Rob,
your sample works. I adapted my solution and it works too :-)
However a couple of questions:
1. The line "CombBox1.RemoveItem 0 is responsible to prevent failures based on zero entries?
2. My name range does not work (see below) If I add a "standard" name range it works. You know why?

thanks a lot
Nils

0
 
Petersburg1Author Commented:
Next question follows as I already told you :-)
0
 
Petersburg1Author Commented:
Excellent.
Lots of patience, thanks!!!
Nils
0
 
Petersburg1Author Commented:
Sorry forgot to show:
This does not work:
='People Coding'!$B$2:INDEX('People Coding'!$B:$B;MATCH(REPT("Z";20);'People Coding'!$B:$B))

This does work:
='People Coding'!$B$2:$B$169
0
 
RobSampsonCommented:
Hi, to answer your questions:
1. The line "CombBox1.RemoveItem 0 is responsible to prevent failures based on zero entries?

No, those three lines will just reset the combo box to empty before populating it from Excel.  Otherwise it would just continue to add to it.

2. With Named Ranges, I create them by selecting the range, then typing the name for it in the "reference" box in the top left.  But to edit them, I used the Named Range manager.  Not sure why it wouldn't work by directly using the Named Range manager though.

Regards,

Rob.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now