从 Excel 中的列填充 Word 中的下拉列表，然后在下拉列表更改时使用相关字段填充文本框（VBA）
我在 word 2007 宏中有以下代码，其中我使用 Excel 电子表格中的客户名称填充下拉列表，
Private Sub UserForm_Initialize()
Dim i As Integer
Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=CCustomers.xls;Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With
rsT.Open "Select distinct * from Customer", cn, adOpenStatic
i = 0
With rsT
' This code populates the combo box with the values
' in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range.
Do Until .EOF
ComboBox_Company.AddItem (i)
ComboBox_Company.Column(0, i) = rsT.Fields(0).Value
.MoveNext
i = i + 1
Loop
End With
End Sub
因此我有一个包含客户名称的列，我创建了一个命名范围（客户），并填充了下拉列表。但是，当我在下拉列表中选择客户时，我想用客户的地址（1 个街道，2 个城市）填充两个地址字段。
Private Sub cbo_customer_Change()
Dim customerName As String
customerName = cbo_customer.Value
End Sub
该电子表格大约有 10 列，Customer 位于第一列，address1 位于第九列，address2 位于最后一列。如何使用变量 customer 填充地址字段？我是否必须创建一个包含所有字段的新命名范围并具有类似的内容 从 myRange 中选择客户、地址 1、地址 2，其中客户 = 客户名称？
I have the following code in a word 2007 macro where I populate a dropdown with customer names from an excel spreadsheet
Private Sub UserForm_Initialize()
Dim i As Integer
Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=CCustomers.xls;Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With
rsT.Open "Select distinct * from Customer", cn, adOpenStatic
i = 0
With rsT
' This code populates the combo box with the values
' in the YourNamedRange named range in the .xls file. this exampletable is 2 rows by 6 columns and is set as a named range.
Do Until .EOF
ComboBox_Company.AddItem (i)
ComboBox_Company.Column(0, i) = rsT.Fields(0).Value
.MoveNext
i = i + 1
Loop
End With
End Sub
So I I have a column with customer names and I created a named range (Customer) and it populates the dropdown. However, when I select a customer in the dropdown I want to populate two address fields with (1 street, 2 city) the customer's address.
Private Sub cbo_customer_Change()
Dim customerName As String
customerName = cbo_customer.Value
End Sub
The spreadsheet has about 10 columns, Customer in the first one and address1 in the 9th and address2 in the last one. How can I use the variable customer to populate the address fields? Do I have to create a new named range with all the fields and have something like
select customer, address1, address2 from myRange where customer = customerName?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您自己回答了这个问题，创建一个包含您需要的列的命名范围，然后执行选择：
进入一个新记录集，并从其中的第 0 项获取地址字段。
You answered the question yourself, create a named range with the columns your need and then do the select:
Into a new record set, and get the address fields from item 0 in it.