Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete Sheet if it exists on export to excel

Posted on 2007-04-10
4
Medium Priority
?
882 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
adraughn earned 1000 total points
ID: 18882319
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 1000 total points
ID: 18882748
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18884675
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18884741
>>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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question