如何使用Word VBA将所选内容从单词复制到Excel

Question

我正在尝试将单词从我的 Word 文档复制到 excel 工作簿中的特定单元格。我使用书签找到我需要的文本并复制它，然后我打开工作簿以粘贴到特定的单元格 - 这是一个 vlookup 引用。

我的代码运行，但粘贴实际上并未发生。我知道代码的复制部分有效，因为当我运行到那时然后手动粘贴选择时，它工作得很好。我已经尝试了多种粘贴选项，但到目前为止没有任何效果......

Selection. Paste
Selection.PasteSpecial (xlPasteAll)
Selection.PasteSpecial (xlPasteValues)

这是我的代码：

Sub copypastewordtoexcel()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
ActiveDocument.Bookmarks("Name").Select
Selection.Copy
WorkbookToWorkOn = "C:UsersarboariDesktopBook1.xlsx"
Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
ActiveDocument.Bookmarks("Name").Select
Selection.Copy
For Each oSheet In oXL.ActiveWorkbook.Worksheets
oSheet.Range("A1").Select
Selection.PasteSpecial (xlPasteValue)
Next oSheet
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
End Sub

我

不确定我做错了什么，但我希望得到一些指导！

谢谢！

Answer 1

不需要复制/粘贴：可以直接分配

Sub copypastewordtoexcel()
    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim ExcelWasNotRunning As Boolean
    Dim WorkbookToWorkOn As String
    WorkbookToWorkOn = "C:UsersarboariDesktopBook1.xlsx"
    Set oXL = GetObject(, "Excel.Application")
    Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
    For Each oSheet In oXL.ActiveWorkbook.Worksheets
            oSheet.Range("A1").Value = ActiveDocument.Bookmarks("Name").Range.Text
    Next oSheet
End Sub

编辑：从表格单元格读取

txt = ActiveDocument.Tables(1).Cell(1, 1).Range.Text
oSheet.Range("A1").Value = Left(txt, Len(txt)-2)

您需要去除两个字符的"单元格结束"标记。