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.