Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Delete Sheet if it exists on export to excel

Posted on 2007-04-10
4
Medium Priority
?
887 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
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

564 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