Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 03-17-2022, 12:30 AM
ericliu24 ericliu24 is offline How to populate a dropdown list with a spcific column in a specific worksheet Windows 10 How to populate a dropdown list with a spcific column in a specific worksheet Office 2019
Novice
How to populate a dropdown list with a spcific column in a specific worksheet
 
Join Date: Mar 2022
Location: China
Posts: 21
ericliu24 is on a distinguished road
Default How to populate a dropdown list with a spcific column in a specific worksheet

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!
Attached Files
File Type: xlsx test2.xlsx (13.0 KB, 7 views)
File Type: docm test-doc.docm (19.9 KB, 8 views)

Last edited by ericliu24; 03-17-2022 at 04:41 AM.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to populate a dropdown list with a spcific column in a specific worksheet Populate Dependent Word Dropdown List from Excel Narcissus Word VBA 1 05-05-2020 02:55 PM
How to populate a dropdown list with a spcific column in a specific worksheet 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft