How to populate a dropdown list with a spcific column in a specific worksheet

ericliu24 · #1 03-17-2022, 12:30 AM

I am new to VBA, your help is greatly appreciated.

In the attached xls file , there is a column "question type" in the worksheet "general".
Now I want to populate the texts in this column to the dropdownlist entries in the docx file everytime I open it .

Many thanks in advance.

I found some great code in the following link. But it seems it does not offer the function of selecting the column.

Import Excel List into Word Dropdown List

Code is:

Quote:

Private Function fcnExcelDataToArray(strWorkbook As String, _
Optional strRange As String = "Sheet1", _
Optional bIsSheet As Boolean = True, _
Optional bHeaderRow As Boolean = True) As Variant
'Default parameters include "Sheet1" as the named sheet, range of the full named sheet _
and a header row is used.
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
strHeaderYES_NO = "YES"
If Not bHeaderRow Then strHeaderYES_NO = "NO"
If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRS
.MoveLast
lngRows = .RecordCount
.MoveFirst
End With
fcnExcelDataToArray = oRS.GetRows(lngRows)
lbl_Exit:
If oRS.State = 1 Then oRS.Close
Set oRS = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
Exit Function
End Function

Please help me out!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Populate Dependent Word Dropdown List from Excel	Narcissus	Word VBA	1	05-05-2020 02:55 PM
Auto populate a text field based on Content Control Dropdown List	matthias92701	Word VBA	2	05-02-2019 03:54 PM
Dropdown list from a value of another cell in dynamic worksheet	Javir	Excel	5	06-07-2018 02:52 AM
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet	CaptainRetired	Excel Programming	18	01-04-2018 07:22 PM
Populate dropdown list with data from Access table	spw4000	Office	0	02-24-2012 05:22 AM