通过VBA用Excel数据填充Word Combobox [英] Populate Word Combobox with Excel data through VBA

问题描述

我是VBA的新手，所以我为此苦了几天.

I'm new to VBA, so I'm struggling with this for a couple of days now.

我在Word中有一个带有联系人的组合框，我也有一个带有一栏(所有联系人的名称)的excel文件(contacts.xls).当我打开Word文档时，宏必须用Excel文件中的所有名称填充组合框.

I have a combobox in Word with contacts, I also have an excel file(contacts.xls) with one column (names of the all the contacts). When I open the Word-document the Macro has to fill in the combobox with all the names from the excel file.

是否可以给我发送Word 2007的工作示例?似乎很简单，但无法正常工作...

Is it possible to send me a working example of this for word 2007? Seems quite simple, but just can't get this to work...

提前谢谢！

推荐答案

我现在为您提供了更多完整代码:

I have more full code for you now:

Option Explicit

Sub TestDropDownFromExcel()

    Dim counter As Long
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim oCC As ContentControl

    Set oCC = ActiveDocument.ContentControls(1)

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Path\To\MyFile.xlsx")

    If xlBook Is Nothing Then
        Exit Sub
    End If

    oCC.DropdownListEntries.Clear

    For counter = 1 To 10
        oCC.DropdownListEntries.Add Text:=xlBook.Worksheets(1).Cells(counter, 1), Value:=CStr(counter)
    Next counter

    xlApp.Visible = True

    Set xlBook = Nothing
    Set xlApp = Nothing 

End Sub

请注意，此处几乎没有错误检查.另外，如果您不希望用户看到它，也可以简单地调用.Close，而不是最后调用.Visible(对于最终项目，这可能是更好的选择.有，但是VBA在执行结束时会自动清理.很明显，我假设您要第一个下拉列表(ContentCOntrols(1))，但这可能不是正确的.

Be aware that there is very little error checking going on here. Also, rather than the call to .Visible at the end, you can simply call .Close if you do not want the user to see it (for the final project, this is probably preferable. The deferencing (= Nothing) is good practice to have, but VBA cleans up automatically at the end of execution. Obviously I am assuming tyou want the first dropdown (ContentCOntrols(1)) but this may not be true.

这篇关于通过VBA用Excel数据填充Word Combobox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！