Word VBA创建Excel工作簿时报运行时错误424如何解决

你遇到的424报错（对象 required）核心有3个语法/逻辑错误：

1. SaveAs和Close方法连用语法错误：你代码里写的.SaveAs.Close FileName:=xxx是非法写法，两个方法不能合并调用
2. 操作已关闭的工作簿：你创建完工作簿立刻关闭，后续还尝试激活这个已经不存在的工作簿对象，自然找不到对应对象
3. 单元格内容提取不规范：直接赋值Word.Cell对象会返回对象本身而非文本内容，需要取Range.Text属性，还要剔除末尾自带的回车换行标记

Function SelectFile() As String
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show <> 0 Then
            SelectFile = .SelectedItems(1)
        End If
    End With
End Function

Sub getFirstColumn()
    ' 变量声明
    Dim wbPath As String, wbName As String, parentFld As String
    Dim TableCount As Integer, tableNum As Integer, RowCount As Integer, rowNum As Integer
    Dim printRowCounter As Long
    Dim app As Object, NewBook As Object
    Dim fileObj As Object
    Dim firstColArray As Variant, arrIndex As Long
    ReDim firstColArray(0 To 10000) ' 预先分配数组空间，可根据实际数据量调整
    arrIndex = 0
    
    ' 初始化对象
    Set fileObj = CreateObject("Scripting.FileSystemObject")
    Set app = CreateObject("Excel.Application")
    app.Visible = True ' 开发阶段建议设为True方便调试，正式使用可改为False
    
    ' 选择待处理的Word文件
    MsgBox "请选择包含表格的Word文件"
    wbPath = SelectFile()
    If wbPath = "" Then Exit Sub ' 没选文件直接退出
    wbName = fileObj.GetFileName(wbPath)
    Documents.Open wbPath ' 避免文件没打开报错
    Documents(wbName).Activate
    parentFld = fileObj.GetParentFolderName(wbPath)
    
    ' 提取所有表格第一列内容
    TableCount = ActiveDocument.Tables.Count
    For tableNum = 1 To TableCount
        RowCount = ActiveDocument.Tables(tableNum).Range.Rows.Count
        For rowNum = 1 To RowCount
            ' 提取单元格文本，去掉末尾的单元格结束标记（两个字符：回车+换行）
            cellContent = Left(ActiveDocument.Tables(tableNum).Cell(rowNum, 1).Range.Text, _
                Len(ActiveDocument.Tables(tableNum).Cell(rowNum, 1).Range.Text) - 2)
            If Trim(cellContent) <> "" Then
                firstColArray(arrIndex) = cellContent
                arrIndex = arrIndex + 1
                If arrIndex > UBound(firstColArray) Then ReDim Preserve firstColArray(UBound(firstColArray) + 1000)
            End If
        Next rowNum
    Next tableNum
    ReDim Preserve firstColArray(0 To arrIndex - 1) ' 裁剪数组到实际数据长度
    Documents(wbName).Close SaveChanges:=wdDoNotSaveChanges ' 处理完关闭Word文件
    
    MsgBox "数据收集完成，正在生成Excel文件..."
    
    ' 创建Excel工作簿写入数据
    Set NewBook = app.Workbooks.Add
    With NewBook
        .Title = "Numbers"
        .Subject = "Documentation"
        ' 先写数据再保存
        For printRowCounter = 1 To UBound(firstColArray) + 1
            .Sheets(1).Cells(printRowCounter, 1) = firstColArray(printRowCounter - 1)
        Next printRowCounter
        ' 写入完成再保存
        .SaveAs Filename:=parentFld & "\Numbers.xlsx", FileFormat:=51 ' 51对应xlsx格式
        .Close SaveChanges:=False ' 已经保存过了直接关就行
    End With
    
    app.Quit ' 退出Excel进程，避免后台残留
    ' 释放对象
    Set NewBook = Nothing
    Set app = Nothing
    Set fileObj = Nothing
    MsgBox "Excel文件生成完成，路径：" & parentFld & "\Numbers.xlsx"
End Sub

• 如果你坚持要用ArrayList，需要先在VBA编辑器的「工具-引用」中勾选mscorlib.dll，否则也会触发对象找不到的报错
• Word中调用Excel对象默认是后台运行，开发阶段设置app.Visible = True可以直观看到运行状态，方便排查问题
• 所有创建的COM对象用完要主动释放，避免Excel进程后台驻留占用资源

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