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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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