Runtime error when attempting to delete a workshee

Question

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).

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

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".

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?

Answer 1

Like this:

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