Word VBA宏导入Excel表格时Workbooks.Open语句报错求助

问题排查与修复方案 #

核心报错原因 #

• 最直接的错误：你调用了Dir(.SelectedItems(1))，该函数仅返回不带路径的纯文件名，而Workbooks.Open需要完整的文件路径才能定位目标文件，当选中文件所在目录和当前工作目录不一致时就会直接报错。
• 隐性错误1：重复声明了oExcelWorksheet变量，第一次声明为变体类型，第二次又声明为Excel.Worksheet类型，会引发变量冲突。
• 隐性错误2：没有处理用户点击文件对话框「取消」的情况，用户取消选择后代码仍会继续执行，也会触发报错。
• 优化点：当前硬编码了读取范围为A1:F31，若后续表格尺寸变动需要手动修改代码，可替换为自动识别工作表已使用范围的逻辑，适配性更强。

修复后的完整代码 #

Sub MakeTablefromExcelFile()
    ' 变量声明
    Dim oExcelApp As Object, oExcelWorkbook As Object, oExcelWorksheet As Object, oExcelRange As Object
    Dim nNumOfRows As Long, nNumOfCols As Long
    Dim fullFilePath As String
    Dim fd As Office.FileDialog
    Dim oTable As Table
    Dim x As Long, y As Long

    ' 初始化文件选择对话框
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .AllowMultiSelect = False
        .Title = "请选择需要导入的Excel表格文件"
        .Filters.Clear
        .Filters.Add "Excel文件", "*.xls;*.xlsx;*.xlsb;*.xlsm"
        ' 仅在用户确认选择后继续执行
        If .Show <> True Then
            Set fd = Nothing
            Exit Sub
        End If
        ' 直接保存完整文件路径，不使用Dir函数
        fullFilePath = .SelectedItems(1)
    End With
    Set fd = Nothing

    ' 启动Excel并打开目标文件
    Set oExcelApp = CreateObject("Excel.Application")
    oExcelApp.Visible = False ' 设为False可后台运行，不打扰用户操作
    Set oExcelWorkbook = oExcelApp.Workbooks.Open(fullFilePath)
    Set oExcelWorksheet = oExcelWorkbook.Worksheets(1)
    ' 自动识别已使用的表格范围，无需硬编码固定区域
    Set oExcelRange = oExcelWorksheet.UsedRange
    nNumOfRows = oExcelRange.Rows.Count
    nNumOfCols = oExcelRange.Columns.Count

    ' 在Word文档末尾创建表格并填充内容
    ActiveDocument.Range.InsertParagraphAfter
    Set oTable = ActiveDocument.Tables.Add(Range:=ActiveDocument.Paragraphs.Last.Range, NumRows:=nNumOfRows, NumColumns:=nNumOfCols)
    
    For x = 1 To nNumOfRows
        For y = 1 To nNumOfCols
            ' 处理空单元格避免赋值报错
            If Not IsEmpty(oExcelRange.Cells(x, y).Value) Then
                oTable.Cell(x, y).Range.Text = oExcelRange.Cells(x, y).Value
            End If
        Next y
    Next x

    ' 清理Excel进程，避免后台残留占用资源
    oExcelWorkbook.Close SaveChanges:=False
    oExcelApp.Quit
    Set oExcelRange = Nothing
    Set oExcelWorksheet = Nothing
    Set oExcelWorkbook = Nothing
    Set oExcelApp = Nothing

    ' 统一设置表格格式
    With oTable
        .Range.Font.Size = 9
        .Columns(1).Width = 225
        .Columns(2).Width = 75
        .Columns(3).Width = 60
        .Columns(4).Width = 60
        .Columns(5).Width = 60
        .Columns(6).Width = 60
        .Rows.Height = 20
    End With
End Sub

关键修改说明 #

• 移除Dir函数调用，直接传递完整文件路径给Workbooks.Open，解决找不到文件的报错
• 统一变量声明，删除重复声明的变量，避免类型冲突
• 新增用户取消选择的判断逻辑，用户点取消时直接退出程序不会触发报错
• 替换硬编码的固定范围为UsedRange自动识别表格尺寸，适配不同大小的表格
• 新增空单元格判断和Excel对象释放逻辑，避免程序异常退出时Excel进程后台残留
• 新增文件类型过滤，避免用户选中非Excel格式文件引发报错

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