从Excel中填充Word中的组合框 - 在移动Excel文件后停止工作 [英] Populating a combobox in Word from Excel - stops working after Excel file is moved

问题描述

感谢您的时间！使用在stackoverflow和其他地方收集的代码片段，我能够拼凑一个宏，它将从Excel中定义的数据范围中填充Word中的组合框，然后获取一个标签从组合框打印第二列（太长，无法在组合框中显示）。到现在为止还挺好。这是我的代码：

  Private Sub ComboBox1_DropButtonClick（）
'不需要对Excel对象的引用。 
 Dim xlApp As Object 
 Dim xlWB As Object 
 Dim xlWS As Object 
 Dim cRows As Long 
 Dim i As Long 
设置xlApp = CreateObject（ Excel.Application）
'打开电子表格以获取数据
设置xlWB = xlApp.Workbooks.Open（EXCEL FILEPATH）
设置xlWS = xlWB.Worksheets（1）
 cRows = xlWS.Range（$ A2：$ B216）。Rows.Count  -  xlWS.Range（$ A2：$ B216）Row + 1 
 ComboBox1.ColumnCount = 2 
'填充列表框。 
 With Me.ComboBox1 
 For i = 2 To cRows 
'使用.AddItem属性为每个记录添加一个新行并填充列0 
 .AddItem xlWS.Range（ $ A1：$ B216）。Cells（i，1）
'使用.List方法填充剩余列
 .List（.ListCount  -  1,1）= xlWS.Range ：$ B216）。Cells（i，2）
 Next i 
结束于
'清除
设置xlWS =无任何
设置xlWB =无
 xlApp.Quit 
'Make标签打印ComboBox的第2列
使用ComboBox1 
 Label1.Caption = .List（.ListIndex，1）
结束于
 End Sub 
  

问题是，当Excel文件被移动，然后Word文件关闭并重新打开时， comobobx不再填充。不幸的是，这需要是一个独立的Word文档，可通过电子邮件分发给多个用户。有没有办法填充组合框，以便Word文档保存数据，而不必每次打开文档时返回到Excel文件？

再次感谢！ / p>

解决方案

我之前使用的一种方法是不使用Excel，当你发送Word文档，它连接到一个信任的网站，返回的列表。

虽然我没有这样做在Word中，我已经做了Excel的电子表格电子邮件发送出来作为填充的空白模板。网站用于填充参考数据下拉列表。参考数据定期更改，因此这是提供最新数据的最佳方式，同时保留了人们熟悉的现有电子表格。

如果您不太在意填写从电子表格中，您当然可以从代码创建组合，组合的值可以保存在Word中的字段中。

让我知道如果这些是有帮助的&

您可以使用 DocProperty字段以存储您的组合的条目

创建一个名为组合选项的字段，

 选项1;选项2;另一个选项
   
 
 
在VBA中，您可以使用以下访问字段：
  ActiveDocument.CustomDocumentProperties（Combo选项）值
  
然后，您可以将字段拆分为它的组件，并迭代它们以添加组合框选项。
 
Thanks for your time! Using snippets of code gathered here on stackoverflow and elsewhere, I was able to cobble together a macro that will populate a combobox in Word from a defined range of data in an Excel file, and then get a label to print the second column from the combobox (which is too long to display in the combobox itself). So far, so good. Here's my code:
Private Sub ComboBox1_DropButtonClick()
'Late binding.  No reference to Excel Object required.
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
  Set xlApp = CreateObject("Excel.Application")
  'Open the spreadsheet to get data
  Set xlWB = xlApp.Workbooks.Open("EXCEL FILEPATH")
  Set xlWS = xlWB.Worksheets(1)
  cRows = xlWS.Range("$A2:$B216").Rows.Count - xlWS.Range("$A2:$B216").Row + 1
  ComboBox1.ColumnCount = 2
  'Populate the listbox.
  With Me.ComboBox1
    For i = 2 To cRows
       'Use .AddItem property to add a new row for each record and populate column 0
      .AddItem xlWS.Range("$A1:$B216").Cells(i, 1)
      'Use .List method to populate the remaining columns
      .List(.ListCount - 1, 1) = xlWS.Range("$A1:$B216").Cells(i, 2)
    Next i
  End With
  'Clean up
  Set xlWS = Nothing
  Set xlWB = Nothing
  xlApp.Quit
  'Make label print column 2 of ComboBox
  With ComboBox1
        Label1.Caption = .List(.ListIndex, 1)
    End With
End Sub
The problem is that when the Excel file is moved and then the Word file is closed and reopened, the comobobx no longer gets populated. Unfortunately, this needs to be a standalone Word doc that is distributable via e-mail to multiple users. Is there a way to populate the combobox so the Word document holds the data without having to refer back to the Excel file each time the doc is opened?

Thanks again!
解决方案
One way to achieve this that I've used before is not to use Excel at all, when you send out the Word document, have it connect to a trusted web site that returns the list instead.

While I've not done this in Word, I have done it with Excel where the spreadsheet was emailed out as a blank template for filling in. A web site was used to populate reference data dropdowns. The reference data changed periodically so that was the best way to provide the freshest data while retaining an existing spreadsheet that people were familiar with.

If you are not too bothered about populating from a spreadsheet, you can of course, create a combo from code, the values for the combo could be kept in a field in Word.

Let me know if either of these is helpful & I will try to expand if needed.

Update:

You can make use of a DocProperty Field to store the entries for your combo if you don't actually need anything else from your spreadsheet.

Create a field called Combo Options or whatever and put into it:
Option 1;Option 2;Another Option
Or whatever text options you want.

In VBA, you can access the fields using:
ActiveDocument.CustomDocumentProperties("Combo Options").Value
Then you can split the field into it's components and iterate over them to add the combo box options.

                        
这篇关于从Excel中填充Word中的组合框 - 在移动Excel文件后停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！