使用Excel 2010 VBA批量提取Word 2010书签内容至指定区域

Got it, let's build the Excel-side macro you need to pull data from those 200 Word docs. I'll walk you through setup, the code itself, and how to customize it for your specific bookmarks and Excel layout.

Prerequisites: Link Excel to Word #

First, we need to make sure Excel can communicate with Word. Here's how:

• Open your Excel workbook, press Alt + F11 to launch the VBA Editor.
• Go to Tools > References, scroll down and check Microsoft Word 14.0 Object Library (this is the 2010 version). Click OK.

The Full Macro Code #

Paste this into a new module in the VBA Editor (right-click your workbook in the Project pane > Insert > Module):

Sub PullWordBookmarkData()
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim folderPath As String
    Dim fileName As String
    Dim targetSheet As Worksheet
    Dim nextEmptyRow As Long
    
    ' --- Customize these values to match your setup ---
    folderPath = "C:\Your\Word\Documents\Folder\" ' Path to your 200 Word files
    Set targetSheet = ThisWorkbook.Sheets("DataSheet") ' Excel sheet to paste data
    ' Bookmark names (match exactly what's in your Word docs)
    Const BOOKMARK_NAME As String = "CustomerName"
    Const BOOKMARK_DATE As String = "SubmissionDate"
    Const BOOKMARK_NUMBER As String = "ProjectID"
    ' --- End customization ---
    
    ' Initialize Word (run in background, no visible window)
    Set wordApp = New Word.Application
    wordApp.Visible = False
    
    ' Start writing data after the last filled row in column A
    nextEmptyRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    On Error GoTo Cleanup ' Handle errors without crashing the whole process
    
    ' Loop through every Word document in the folder
    fileName = Dir(folderPath & "*.docx")
    Do While fileName <> ""
        Set wordDoc = wordApp.Documents.Open(folderPath & fileName)
        
        ' Extract content from each bookmark (skip if bookmark is missing)
        On Error Resume Next
        targetSheet.Cells(nextEmptyRow, "A").Value = wordDoc.Bookmarks(BOOKMARK_NAME).Range.Text
        targetSheet.Cells(nextEmptyRow, "B").Value = CDate(wordDoc.Bookmarks(BOOKMARK_DATE).Range.Text)
        targetSheet.Cells(nextEmptyRow, "C").Value = CInt(wordDoc.Bookmarks(BOOKMARK_NUMBER).Range.Text)
        On Error GoTo Cleanup
        
        ' Close the Word doc without saving changes
        wordDoc.Close SaveChanges:=False
        
        ' Move to next row and next file
        nextEmptyRow = nextEmptyRow + 1
        fileName = Dir()
    Loop
    
    MsgBox "Data extraction finished! Check your Excel sheet.", vbInformation

Cleanup:
    ' Ensure Word is closed even if an error occurs
    If Not wordDoc Is Nothing Then
        wordDoc.Close SaveChanges:=False
        Set wordDoc = Nothing
    End If
    If Not wordApp Is Nothing Then
        wordApp.Quit
        Set wordApp = Nothing
    End If
    
    ' Show error message if something went wrong
    If Err.Number <> 0 Then
        MsgBox "Oops, an error occurred: " & Err.Description, vbExclamation
    End If
End Sub

How to Customize & Run #

1. Update the folder path: Replace "C:\Your\Word\Documents\Folder\" with the actual path where your 200 Word files are stored.
2. Bookmark names: Change BOOKMARK_NAME, BOOKMARK_DATE, and BOOKMARK_NUMBER to match the exact names of your 3 bookmarks in the Word docs.
3. Target sheet: Adjust Set targetSheet = ThisWorkbook.Sheets("DataSheet") to the name of your Excel sheet where you want the data pasted.
4. Run the macro: Go back to Excel, press Alt + F8, select PullWordBookmarkData, and click Run.

Quick Tips for Your Workflow #

• Handle missing bookmarks: The code skips any docs with missing bookmarks without crashing. If you want to track which files failed, you can add a line to write the filename to a "Errors" sheet.
• Data formatting: The code uses CDate and CInt to convert Word's text into proper Excel date/integer types. If your dates use non-standard formats, you might need to tweak the conversion (e.g., DateValue() instead of CDate()).
• Easy access: For periodic runs, add a button to your Excel ribbon and assign this macro to it—no need to open the VBA Editor every time.

内容的提问来源于stack exchange，提问作者Samuel Everson