将 Word 段落复制到 Excel 单元格 [英] Copy Word Paragraph to Excel Cells

问题描述

我正在尝试将 Word 段落复制到 Excel 单元格，但我挂断了运行时错误 9:下标超出范围.

I am trying to copy Word paragraphs to Excel cells, but I am hung up on Runtime error 9: Subscript out of range.

我已经搜索过了.我读过的所有内容都说找不到该文件，但该文件在同一文件夹中，并且名称没有拼写错误，并且扩展名正确.所以，我很难过.原始代码来自这里:如何复制从 Word 2013 到 Excel 的格式化段落?.

I have searched. Everything I read says it cannot find the file, but the file is in the same folder, and the name is not mis-spelled, and the extension is correct. So, I am stumped. The original code comes from here: How to copy a formatted paragraph from Word 2013 to Excel?.

    Private Sub Load_Schedule()
    Dim ParaCount As Integer
    Dim wDoc As Word.Document
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wDoc = ActiveDocument
    Set wb = Workbooks("new.xlsm")        
    Set ws = wb.Sheets("Sheet1")
        ws.Activate
        ws.Columns(1).AutoFit
    For ParaCount = 1 To wDoc.Paragraphs.Count
        wDoc.Paragraphs(ParaCount).Range.FormattedText.Copy
        Sheets(ws).Cells(ParaCount, 1).PasteSpecial 
        Paste:=xlPasteFormats
    Next ParaCount
    End Sub

错误出现在这一行:Set wb = Workbooks("new.xlsm")

The error comes on this line: Set wb = Workbooks("new.xlsm")

推荐答案

当您使用这两个应用程序时，您应该使用完整的声明，例如 Word.Document 和 Excel.Workbook(如果您已经引用了相应的库).

As you work with both applications, you should use full declarations like Word.Document and Excel.Workbook (if you already referenced the appropriate libraries).

已打开的 Excel 文件可以不带路径引用.

An already opened Excel file can be referenced without path.

Paste:= ... 参数属于上一行代码，所以你必须在上一行的末尾添加一个空白 + undersore 或将它们放在一起.

The Paste:= ... parameter belongs to the previous code line, so you have to add a blank + undersore at the end of the previous line or put them together into one line.

请通过 ws.Cells ... 而不是通过 Sheets(ws) 引用您工作表的单元格，作为您的ws"已经是一个工作表对象而不是一个字符串.

Please reference your worksheet's cell by ws.Cells ... and not by Sheets(ws), as your "ws" already is a worksheet object and not a string.

进一步的答案取决于您是从 Word-VBA 还是从 Excel-VBA 运行代码.

The further answer depends, if you run your code from Word-VBA or from Excel-VBA.

如果要从 Word-VBA 引用 Excel 文件，则还需要 Excel.Application 对象.
如果 Excel 已经启动，您可以使用现有的应用程序对象 - 否则您创建一个并使其可见.

If you want to reference an Excel file from Word-VBA, you need the Excel.Application object additionally.
If Excel is already started, you can use the existing application object - otherwise you create one and make it visible.

与您的 Excel 文件相同:如果它已经打开，则使用它 - 如果没有，则打开它.

Same with your Excel file: If it's already open, you use it - if not, you open it.

Private Sub LoadSchedule()
    Dim ParaCount As Integer
    Dim wDoc As Word.Document
    Dim objExcel As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    On Error GoTo 0
    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
    End If

    On Error Resume Next
    Set wb = objExcel.Workbooks("new.xlsm")
    On Error GoTo 0
    If wb Is Nothing Then
        Set wb = objExcel.Workbooks.Open(objExcel.DefaultFilePath & "\new.xlsm")
        ' or ThisDocument.Path or whatever path
    End If
    
    Set wDoc = ActiveDocument
    Set ws = wb.Sheets("Sheet1")
    For ParaCount = 1 To wDoc.Paragraphs.Count
        wDoc.Paragraphs(ParaCount).Range.FormattedText.Copy
        ws.Cells(ParaCount, 1).PasteSpecial Paste:=xlPasteFormats
    Next ParaCount
    
    ws.Columns(1).AutoFit
    'ws.Activate
End Sub

---

Excel VBA

在 Excel 中，您可以尝试将已打开的 Word 文件直接引用为 ActiveDocument，而无需另外获取 Word.Application.

---

Excel VBA

In Excel you can try to reference an already opened Word file directly as ActiveDocument without getting the Word.Application additionally.

Private Sub LoadSchedule()
    Dim ParaCount As Integer
    Dim wDoc As Word.Document
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    On Error Resume Next
    Set wb = Workbooks("new.xlsm")
    On Error GoTo 0
    If wb Is Nothing Then
        Set wb = Workbooks.Open(Application.DefaultFilePath & "\new.xlsm")
    End If
    
    Set wDoc = ActiveDocument
    Set ws = wb.Sheets("Sheet1")
    For ParaCount = 1 To wDoc.Paragraphs.Count
        wDoc.Paragraphs(ParaCount).Range.FormattedText.Copy
        ws.Cells(ParaCount, 1).PasteSpecial Paste:=xlPasteFormats
    Next ParaCount
    
    ws.Columns(1).AutoFit
    'ws.Activate
End Sub

这篇关于将 Word 段落复制到 Excel 单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！