Delete Sheet if it exists on export to excel

Access 2003 Excel 2003


The following routine is exporting a table to excel

The name is a variable I'am passing

What I need:
When this sheet is open I need to check and see if a sheet name exists "tblDescMatch"
if it does ...Delete  It


ExportFinalQueryCall  "10_RECORDS_TEST.xls"

Function ExportFinalQueryCall(ByVal strName As String)
On Error GoTo ExportFinalQueryCall_Err

' PASS THE SPREADHSEET NAME
'strName = "10_RECORDS_TEST.xls"

DoCmd.TransferSpreadsheet acExport, 8, "tblDescMatch", "C:\Dm\InBox\" & strName & " ", True, ""
ExportFinalQueryCall_Exit:
    Exit Function
ExportFinalQueryCall_Err:
    If Err.Number = "3010" Then
MsgBox "The Excel Sheet is Already Open. Close The Open Workbook", vbCritical, "Error Exporting Specific Query"
Exit Function
End If
Resume ExportFinalQueryCall_Exit
End Function
LVL 3
FordraidersAsked:
Who is Participating?
 
adraughnCommented:
You can use this:

The function below can be used to determine if a sheet exists in a workbook:
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

Sub DeleteSheet()
If  SheetExists("MySheetName") Then
    Application.DisplayAlerts = False
    Sheets(strSheetName).Delete
    Application.DisplayAlerts = True
End If

End Sub

 
 
0
 
jefftwilleyCommented:
This one will also

Function DeleteExcelSheet(strWB As String, strSheet As String) As Boolean
Dim xlApp As Object
Dim xlWB As Object
Dim xlWs As Object
Dim I As Long
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWB = xlApp.Workbooks.Open(strWB, True)
For I = 1 To xlWB.Worksheets.count
If xlWB.Worksheets(I).Name = strSheet Then
    xlWB.Worksheets(strSheet).Delete
    Exit For
End If
Next I
xlApp.Visible = True
xlWB.Save
xlWB.Close (False)
xlApp.DisplayAlerts = True
xlApp.Quit
Set xlApp = Nothing
End Function
0
 
stevbeCommented:
these are the kind of functions I *cheat* on ...

Function DeleteExcelSheet(strWB As String, strSheet As String) As Boolean
...

On Error Resume Next
xlWB.Worksheets(strSheet).Delete

...
End Function
0
 
jefftwilleyCommented:
>>On Error Resume Next
xlWB.Worksheets(strSheet).Delete


I farted around with THAT this morning...and if the sheet doesn't exist, it will not let you trap the error...It made me nuts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.