使用Excel VBA从Word表格的单元格中删除内容

I am currently working on a project and am looking for some assistance. To give you guys a layout of what is happening, I will run through the scenario step by step.

我目前正在开展一个项目，正在寻求一些帮助。为了给你们一个正在发生的事情的布局，我将逐步完成这个场景。

1) Currently I have a string array called “AnimalNamesToRemove” (For this example the array with contain the following words), that contains words that are used as bookmarks in a word document that I am looking to remove off a word table referenced below:

1）目前我有一个名为“AnimalNamesToRemove”的字符串数组（对于此示例包含以下单词的数组），其中包含用作word文档中书签的单词，我希望从下面引用的单词表中删除：

AnimalNamesToRemove

AnimalNamesToRemove

AnimalCat, AnimalDog, AnimalBird

AnimalCat，AnimalDog，AnimalBird

2) In addition to the array, a table in a word document exists that has the name of the animal in column one, as well as some information about the animal (the only information that is of importance is the name of the animal):

2）除了数组之外，还存在word文档中的表格，其中第一列中包含动物的名称，以及有关动物的一些信息（唯一重要的信息是动物的名称）：

Word Table

单词表

3) For this scenario, I have an excel table that I am looking to use to reference the words in the array and the word table names, as there are already bookmarks in the word document being used that hold the names existing in the array. To bring these together, a two column excel spreadsheet exists that has the name of the bookmark and the actual animal name (Column two is referenced using the range named “myRangeRef”):

3）对于这种情况，我有一个excel表，我希望用它来引用数组中的单词和单词表名，因为在word文档中已经有书签用于保存数组中存在的名称。为了将这些组合在一起，存在两列excel电子表格，其中包含书签的名称和实际的动物名称（使用名为“myRangeRef”的范围引用第二列）：

Spreadsheet

电子表格

4) What I am looking to do is that for every value in the array stated above, if that value (ex. “AnimalDog”) is found in the spreadsheet table (i.e. column two “Bookmark Reference”) then offset to the respective cell beside it in the first column (i.e. “Dog”) and create a new comma delimited string with those values, the same as “AnimalNamesToRemove” (i.e. Cat, Dog, Bird) and then turn it into a string array named “AnimalsToDelete”. Once the array is created, and all the values have been selected in the first column and made into an array based on the reference in column two, I want to go row by row in the word table and for every value existing in the new array “AnimalsToDelete”, if that value (i.e. Cat, Dog, and Bird) exists in the word table (found in column one), I want the code to delete the entire row that the name is found in (see result shown below)

4）我想要做的是，对于上述数组中的每个值，如果在电子表格表中找到该值（例如“AnimalDog”）（即第二列“书签参考”），则偏移到相应的单元格在第一列旁边（即“Dog”）并使用这些值创建一个新的逗号分隔字符串，与“AnimalNamesToRemove”（即Cat，Dog，Bird）相同，然后将其转换为名为“AnimalsToDelete”的字符串数组。一旦创建了数组，并且已经在第一列中选​​择了所有值并根据第二列中的引用将其作为一个数组，我想在word表中逐行并且对于新数组中存在的每个值“AnimalsToDelete”，如果单词表中存在该值（即Cat，Dog和Bird）（在第一列中找到），我希望代码删除找到该名称的整行（请参阅下面显示的结果）

Example Result

示例结果

Dim wdTable As Object
Dim myRangeRef As Range
Dim AnimalNamesToRemove As Variant
Dim AnimalsToDelete As Variant
Dim wdDoc As Object

Set myRangeRef = ThisWorkbook.Sheets("Bookmark References").Range("B1:B6")

Set wdTable = wdDoc.Tables(1)
For i = LBound(AnimalNamesToRemove) To UBound(AnimalNamesToRemove)
    For Each cell In myRangeRef
        If InStr(1, cell.Value, AnimalNamesToRemove(i), vbTextCompare) Then
            aCell = cell.Offset(, -1).Value
            stTemp = stTemp & "," & aCell
        End If
    Next cell
Next i

stTemp = Mid(stTemp, 2)
If Not Len(Trim(stTemp)) = 0 Then
    AnimalsToDelete = Split(stTemp, ",")
    For i = LBound(AnimalsToDelete) To UBound(AnimalsToDelete)
        For j = wdTable.Rows.Count To 2 Step -1
            If wdTable.cell(j, 1).Range.Text = AnimalsToDelete(i) Then wdTable.Rows(j).Delete
        Next j
    Next i
End If

If you have any solutions and/or suggestions please comment them down below.

如果您有任何解决方案和/或建议，请在下面评论。

NOTE: The first section of code works for creating the string array (i.e. from "set wdTable =" to "next i"), its the removal of information from the word table that I'm having the issues with. Best,

注意：第一部分代码适用于创建字符串数组（即从“set wdTable =”到“next i”），它从字表中删除我遇到问题的信息。最好，

Jack Henderson

杰克亨德森

1 个解决方案

#1

Alright, based on your code I added a Reference to the Microsoft Word 16.0 Object Library in my Excel VBE (Tools - References, check the box) so we have the Word stuff available. Next I wrote the following procedure:

好吧，基于你的代码，我在Excel VBE中添加了对Microsoft Word 16.0对象库的引用（工具 - 参考，选中复选框），这样我们就可以使用Word。接下来我写了以下程序：

Sub Test()
Dim BookMarksToDelete() As String
Dim ReturnsToDelete() As String
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim wdTable As Word.Table
Dim myRangeRef As Range
Dim cel As Range
Dim aCell As String

Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open("C:\Temp\Col1.docx")
Set wdTable = wDoc.Tables(1)

ReDim BookMarksToDelete(0 To 1)
    BookMarksToDelete(0) = "BlahOne"
    BookMarksToDelete(1) = "BlahThree"

Set myRangeRef = Worksheets("Sheet1").Range("B1:B5")

For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
    For Each cel In myRangeRef
        If InStr(1, cel.Value, BookMarksToDelete(i), vbTextCompare) Then
            aCell = cel.Offset(0, -1).Value
            stTemp = stTemp & "," & aCell
        End If
    Next cel
Next i

stTemp = Mid(stTemp, 2)
If Not Len(Trim(stTemp)) = 0 Then
    ReturnsToDelete = Split(stTemp, ",")
    For i = LBound(ReturnsToDelete) To UBound(ReturnsToDelete)
        For j = wdTable.Rows.Count To 2 Step -1
             If Left(wdTable.cell(j, 1).Range.Text, Len(wdTable.cell(j, 1).Range.Text) - 2) = ReturnsToDelete(i) Then
                wdTable.Rows(j).Delete
            End If
        Next j
    Next i
End If

wDoc.Save
wDoc.Close
wApp.Quit

Set wdTable = Nothing
Set wDoc = Nothing
Set wApp = Nothing
Set myRangeRef = Nothing
End Sub

As you can see, I basically stuck to your exact same structure and it works perfectly.

正如你所看到的，我基本上坚持你完全相同的结构，它完美地工作。

Your main issue (the rows in the word doc not being deleted or found) is because the text in a Cell in a table in word actually contains 2 extra characters in the very end. One is a "fake new line" and the other one shows up when you hit this paragraph button on the word GUI - It's an "end of cell" marker.

您的主要问题（单词doc中的行未被删除或找到）是因为单词表中单元格中的文本实际上最后包含2个额外字符。一个是“假新线”，另一个是当你点击GUI上的这个段落按钮时出现的 - 它是一个“细胞末端”标记。

See for example this discussion

例如，参见本讨论

EDIT I based myself on the "BlahOne" and "NameOne" example, but yeah, you can edit it for animals, of course...

编辑我基于“BlahOne”和“NameOne”示例，但是，你可以为动物编辑它，当然......