Populate DropDown FormField from Excel Range

[AlecL]

Hey everyone,
I'm new to the board and joined specifically to stack up on my VBA game. So far, I'm basically self-taught, with a lot of recording and scavenging from topics I found via google dealing with the tasks arising in my projects, so please bear with me doing stuff in convoluted or stupid ways.

So, here's my situation:

I to have a word document () representing a form I and colleagues at work have to use regularly in projects. In it, I use DropDown form fields so that everyone upon making a new document can pick their name from. So far, names are just added without any programming in word.

Now, I do have an excel book (C:\Documents\Users.xlsx), containing all the names in Sheet 1, column C.

I would like for the Document upon opening to access the excel file and add every cell from column C as a new entry to the form field. There could be varying numbers of entries, so it would be best to have a solution that is sensitive to that.

My two approaches so far were (a), import the Range from excel into an array in my document, or (b) import the data directly into the FormFields


What I have put together so far is the following, which produces an "Object not found" error, and after three days of playing around with it, I'd be really grateful if someone could point out where I went wrong.

Sub Document_Open()
Dim MyArr As Variant 'Array for variant (a)
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim i as integer


Set xlApp = CreateObject("Excel.Application")

'Open the spreadsheet to get data
Set xlWB = xlApp.Workbooks.Open("C:\Documents\Users.xlsx")
Set xlWS = xlWB.Worksheets(1)

MyArr = x1WS.Range("C1:C14")
ActiveDocument.FormFields("user1").DropDown.ListEntries.Clear
For i = 1 To 20
ActiveDocument.FormFields("user1").DropDown.ListEntries.Add (myArr (i, 2))
Next

'ActiveDocument.FormFields("user1").DropDown.ListEntries.Add (x1WS.Range("C1:C14").Cells(2, 6)) 'Left-over from variant (b) approach


'Clean up
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Bonus question: I tend to favour variant (a), as it would allow me in a next step to use further data from the table pertaining to the different people (such as phone numbers, e-mail-adresses) to be displayed according to the selection. At least that is the plan, unless anyone tells me this won't work anyway?

Thanks a lot for any help in advance!

[gmaxey]

I wouldn't bother physically opening Excel and while I was at it, I would just get all of the data in the sheet:


Option Explicit
Private arrData As Variant
Sub AutoOpen()
Dim lngIndex As Long
Dim strSQL As String
'Get data from column headed "Animal" in Sheet1
strSQL = "SELECT [Animal] FROM [Sheet1$];"
strSQL = "SELECT * FROM [Sheet1$];"
xlFillList arrData, "D:\Data Stores\Load Array from Excel.xls", "True", strSQL
For lngIndex = 0 To UBound(arrData, 2)
'Note 2 corresponds with column C of the Excel data.
ActiveDocument.FormFields(1).DropDown.ListEntries.Add arrData(2, lngIndex)
Next lngIndex
End Sub
Public Function xlFillList(arrPassed As Variant, strWorkbook As String, _
bSuppressHeader As Boolean, strSQL As String)
Dim oConn As Object
Dim oRS As Object
Dim lngNumRecs As Long
Dim strConnection As String
'Create connection:
Set oConn = CreateObject("ADODB.Connection")
If bSuppressHeader Then
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If
oConn.Open ConnectionString:=strConnection
Set oRS = CreateObject("ADODB.Recordset")
'Read the data from the worksheet.
oRS.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly
With oRS
'Find the last record.
.MoveLast
'Get count.
lngNumRecs = .RecordCount
'Return to the start.
.MoveFirst
End With
arrPassed = oRS.GetRows(lngNumRecs)
'Cleanup
If oRS.State = 1 Then oRS.Close
Set oRS = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
lbl_Exit:
Exit Function
End Function

[AlecL]

Thanks a lot for your quick reply and suggestion. I'll certainly work through it, but it'll take me a while to make heads and tails from it yet.

Now, just for educational purposes, and if it's obvious, can you tell why my code does not work? In fact, I originally came with a previous version of my problem to your website and there found the tutorial on "Populate Userform ListBox or ComboBox" (MANY thank and kudos to those articles on your website, by the way, they were an enormous pillar when I first began looking into VBA) and building on the stuff I found there, populating a Userform Dropdown field works just fine in another project. What is the difference there (other than the one being a userform, the other a formfield, or if that is it, what is the difference between the two, then)?

Thanks again!

[gmaxey]

Alec,

Yours didn't work because it was coded wrong ;-). First you declared the worksheet xlWS but you tried use it as x1WS. Secondly your had scope errors,
set a watch on MyArr. When it gets defines look at what it is: MyArr(1 to 13, 1 to 1)





Sub Document_Open()
Dim MyArr As Variant
Dim xlApp As Object, xlWB As Object, xlWS As Object
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
'Open the spreadsheet to get data
Set xlWB = xlApp.Workbooks.Open("D:\Data Stores\Data Source.xlsx")
Set xlWS = xlWB.Worksheets(1)
MyArr = xlWS.Range("C1:C4")
ActiveDocument.FormFields(1).DropDown.ListEntries.Clear
For i = 1 To UBound(MyArr, 1)
If Not MyArr(i, 1) = vbNullString Then
ActiveDocument.FormFields(1).DropDown.ListEntries.Add (MyArr(i, 1))
End If
Next
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub


A userform listbox is basically an array. It has both rows and columns so you can populate it directly from an array

myList.List = SomeArray

[AlecL]

Oh my :-/ I did not see that for Three. Days.

Thanks a lot!

[macropod]

Hint: In the VBE, go to Tools|Options|Editor and check the 'Require variable declaration' option. This will insert 'Option Explicit' at the top of each code module. It will help trap errors relating to unnamed & misused variables.