VBA 将 Microsoft Word 书签和选择文本导出到 Excel 电子表格

VBA Exporting Microsoft Word Bookmarks and Selection Text to Excel Spreadsheet

提问by user1059110

I've recently been put in charge of document standardization for a client at my work. They're government so I can't really post anything in the way of examples for reference. Sorry.

我最近在我的工作中负责为客户进行文档标准化。他们是政府，所以我不能以示例的方式发布任何内容以供参考。对不起。

What I'm trying to do in VBA is have a Word Document that has roughly 80 bookmarks (There are 27 files I have to do this to) extract the .Nameof the bookmark and .Selectionto an Excel sheet.

我想在 VBA 中做的是有一个 Word 文档，它有大约 80 个书签（我必须这样做 27 个文件）提取.Name书签和.SelectionExcel 工作表。

As an example I offer the following:

作为一个例子，我提供以下内容：

Hello, my name is World!

大家好，我叫世界！

If the above is a word document, World!is the .Selectionof the Bookmark and (Doc_World) would be the bookmark name. I'm trying to write the macro that will write "Doc_World"and "World!"to an excel sheet.

如果上面是一个word文档，World!则是书签的名称.Selection，( Doc_World)将是书签名称。我正在尝试编写将写入"Doc_World"并写入"World!"Excel 工作表的宏。

The last caveat is that nothing is currently standardized, so I would need it to just cycle through bookmarks in the current open document.

最后一个警告是目前没有任何标准化，所以我需要它来循环浏览当前打开的文档中的书签。

I actually managed to find something that sort of did what I wanted, and then spliced some other info I found together to create something that worked, but you had to create all the xls files before hand. @RachelHettinger has a much, much more elegant answer than I came up with on my own. For the sake of reference, the following is my frankenstein:

我实际上设法找到了一些可以做我想要的东西，然后将我找到的其他一些信息拼接在一起来创建一些有用的东西，但是你必须事先创建所有的 xls 文件。@RachelHettinger 的答案比我自己想出的要优雅得多。仅供参考，以下是我的弗兰肯斯坦：

Sub WdBkMktoXL()
Dim ObjExcel As Object, ObjWorkBook As Object, ObjWorksheet As Object
Dim Bmk() As String
Dim x As Integer, J As Integer

Set ObjExcel = CreateObject("EXCEL.APPLICATION")
Set ObjWorkBook = ObjExcel.Workbooks.Open("C:\Users\Zach\Desktop\ETTP\TermsAndConditions.xlsx")
Set ObjWorksheet = ObjWorkBook.Worksheets("Sheet1")

x = ActiveDocument.Bookmarks.Count
ReDim Bmk(x)
For J = 1 To x
Bmk(J) = ActiveDocument.Bookmarks(J).Name
ObjWorksheet.Range("A" & J) = ActiveDocument.Bookmarks(J).Range.Text
ObjWorksheet.Range("B" & J) = ActiveDocument.Bookmarks(J).Name
Next J

ObjWorkBook.Save
ObjWorkBook.Close
Set ObjWorksheet = Nothing
Set ObjWorkBook = Nothing
ObjExcel.Quit
Set ObjExcel = Nothing
End Sub

回答by Rachel Hettinger

If I understand your needs correctly, this macro should help. It loops through all bookmarks in the active document and exports them to a new file in Excel:

如果我正确理解您的需求，这个宏应该会有所帮助。它遍历活动文档中的所有书签并将它们导出到 Excel 中的新文件：

Sub ExportBookmarksToExcel()
    Dim bk As Bookmark
    Dim appXl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wst As Excel.Worksheet
    Dim lRow As Long

    Set appXl = CreateObject("Excel.Application")
    With appXl
        .Visible = True
        Set wbk = .Workbooks.Add
        Set wst = wbk.Worksheets(1)
        lRow = 1
        wst.Cells(lRow, 1) = "Bookmark name"
        wst.Cells(lRow, 2) = "Bookmark text"
        wst.Rows(lRow).Font.Bold = True
    End With

    For Each bk In ActiveDocument.Bookmarks
        lRow = lRow + 1
        wst.Cells(lRow, 1) = bk.Name
        wst.Cells(lRow, 2) = bk.Range.Text
    Next bk
    wst.UsedRange.Columns.AutoFit

End Sub

Note 1: Because this code uses early binding, it requires a reference to the Excel library (Tools: References). Note 2: It uses CreateObject to create the instance of Excel, so every time the macro runs, a new instance is created. (GetObject will use an existing instance but fails if none is found.)

注 1：由于此代码使用早期绑定，因此需要引用 Excel 库（工具：参考）。注 2：它使用 CreateObject 创建 Excel 实例，因此每次运行宏时，都会创建一个新实例。（GetObject 将使用现有实例，但如果找不到则失败。）