Solved

Delete Sheet if it exists on export to excel

Posted on 2007-04-10
4
872 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 250 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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

756 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