时间戳

首页 > 解决方案 > 运行时错误 1004 方法添加对象表失败

excel - 运行时错误 1004 方法添加对象表失败

问题描述

我正在尝试在工作簿中添加一张工作表，但出现了这个错误。有什么建议么？

运行时错误 1004 方法添加对象表失败

Do While MyFile <> ""
    Workbooks.Open (MyFile)
    
    Set ws = ActiveWorkbook.Sheets.Add(Before:=ActiveWorkbook.Sheets(1))
    ws.Name = "Global OHS RA Form"

标签： excelvba

解决方案

避免使用ActiveWorkbook. 将打开的工作簿设置为变量并直接使用它。

我还建议检查文件是否正确打开以及工作簿是否受到保护。

Do While MyFile <> vbNullString
    Dim OpenedWb As Workbook
    Set OpenedWb = Nothing  ' initialize because looping
    Set OpenedWb = Workbooks.Open(MyFile)

    If Not OpenedWb Is Nothing Then  ' check if file was opened properly
        If OpenedWb.ProtectStructure Then  ' check if workbook is protected
            MsgBox "Workbook is protected, cannot add worksheets."
        Else
            Dim ws As Worksheet
            Set ws = OpenedWb.Worksheets.Add(Before:=OpenedWb.Sheets(1))
            ws.Name = "Global OHS RA Form"

            ' … your other code here
        End If
    Else
        MsgBox "File could not be opened properly."
    End If
Loop

推荐阅读