尝试从新创建的工作簿（Outlook VBA）中删除工作表时的运行时错误 [英] Runtime error when attempting to delete a worksheet from a newly created workbook - Outlook VBA

问题描述

我有一个Outlook宏，用于将用户 Tasklist 导出到存储在网络驱动器上的Excel电子表格。

I have an Outlook macro which works to export the users Tasklist to an Excel spreadsheet stored on a network drive.

我正在尝试检查目录中是否存在工作簿（如果从这里得到的语句）。

I am attempting to check if there is a workbook already present in the directory (If statement taken form here).

如果没有一个，则使用一个名为Sheet 1的工作表创建一个新的工作簿，如果已经有一个正确的用户名，然后打开它（ add声明从这里）：

If there isn't one, then make a new workbook with one worksheet called "Sheet 1", and if there is already one with the correct username, then open it (add statement taken from here):

Dim FilePath As String
Dim TestStr As String
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim NAME_s As String

objExcel.DisplayAlerts = False
 'Use the Application Object to get the Username
 NAME_s = Environ("USERNAME")



    FilePath = "the\directory\" & NAME_s & ".xlsx"

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
    Set exWb = objExcel.Workbooks.Add(1)
    exWb.Worksheets(1).Name = "Sheet1_old"

Else
    Set exWb = objExcel.Workbooks.Open("J:\Efficiency Measures\PTaR\" & NAME_s & ".xlsx")
End If

exWb.Sheets.Add().Name = "Sheet1"
exWb.Sheets("Sheet1_old").Delete

当我通过If语句，TestStr值正在触发第一个条件，这是正确的，但是然后是 exWb.Sheets（Sheet1）。删除行正在触发一个运行时错误（工作簿必须至少包含一个可见的表格）。但是我认为拥有 objExcel.Workbooks.Add（1）将添加一个新的工作簿到一个名为Sheet 1的工作表指定的目录。

When I step through the If statement, the TestStr value is firing the first condition, which is correct, but then the exWb.Sheets("Sheet1").Delete line is triggering a runtime error (Workbook must contain at least one visible sheet). But I thought that having the objExcel.Workbooks.Add(1) would add a new workbook to the directory specified with one worksheet called "Sheet 1".

如何修改上述代码以确保生成的新工作簿具有Sheet 1的名称，并保存在 FilePath 变量？

How do I modify the above code to ensure the new workbook generated has the "Sheet 1" name, and is saved in the nework location specified in the FilePath variable?

推荐答案

像这样：

exWb.Sheets("Sheet1").Name = "Sheet1Old"
exWb.Sheets.Add().Name = "Sheet1"
exWb.Sheets("Sheet1Old").Delete

这篇关于尝试从新创建的工作簿（Outlook VBA）中删除工作表时的运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！