deleting excel worksheets

[neerak via AccessMonster.com]

i'm having trouble deleting excel worksheets from access. I've tried all
four methods below but none of them seem to work.

Set xlApp = CreateObject("excel.application")
Set xlWkBk = xlApp.workbooks.Open(CurrentProject.Path & "\" & strFilename)

xlApp.UserControl = True
xlApp.sheets("Sheet1").select <------- first method
xlApp.activewindow.selectedsheets.Delete

xlApp.worksheets(strReportName).Delete <------- second method

xlapp.worksheets("Sheet1").select <------- third method
xlapp.activewindow.selectedsheets.delete

xlApp.sheets("Sheet1").delete <------ fourth method

Set db = Nothing
Set rst = Nothing
Set xlApp = Nothing
Set xlWkBk = Nothing
Set xlWkSh = Nothing

xlApp.Visible = True

 

[Daniel Pineault]

I was rapidly able to put this together, and it works for me.

Set xlApp = CreateObject("excel.application")
Set xlWkBk = xlApp.workbooks.Open("c:\test.xls")

xlApp.DisplayAlerts = False
xlApp.worksheets("Sheet1").Delete
xlApp.DisplayAlerts = True
xlApp.Visible = True

Set xlApp = Nothing
Set xlWkBk = Nothing

 

[Daniel Pineault]

You're question interested me for my own personal knowledge so I continued to
do a little coding and truned it into a more generalized function which I
have include below if it can be of use to you feel free to use it.

'---------------------------------------------------------------------------------------
' Procedure : DelWrkSht
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Delete a worksheet from an Excel workbook
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strWrkBk Workbook to delete the worksheet in/from (full path and
filename)
' strWrkSht Worksheet to be deleted
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function DelWrkSht(strWrkBk As String, strWrkSht As String) As Boolean
'Uses late binding so no references are required to Excel
Dim xlApp As Object
Dim xlWrkBk As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo DelWrkSht_Error
Set xlApp = CreateObject("excel.application")
Else
On Error GoTo DelWrkSht_Error
End If

Set xlWrkBk = xlApp.workbooks.Open(strWrkBk)

xlApp.DisplayAlerts = False 'surpress user confirmation prompt
xlApp.worksheets(strWrkSht).Delete
xlApp.DisplayAlerts = True 'reengage user confirmation prompt
xlApp.Visible = True

Set xlApp = Nothing
Set xlWrkBk = Nothing

DelWrkSht = True
If Err.Number = 0 Then Exit Function

DelWrkSht_Error:
DelWrkSht = False
If Err.Number = 9 Then
'Worksheet not found
MsgBox "Worksheet '" & strWrkSht & "' not found in Workbook '" &
strWrkBk & "'", vbCritical
Exit Function
ElseIf Err.Number = 1004 Then
'Workbook not found
MsgBox "Unable to locate Workbook '" & strWrkBk & "'", vbCritical
Exit Function
Else
'Othere Errors
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: basExcel / DelWrkSht" & vbCrLf
& _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Function
End If
End Function
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

 

[neerak via AccessMonster.com]

Thank you for the detail descriptions and the thorough error handling
mechanisms added to the code. I get it to work now. I must add the suppress
confirmation prompt before deleting the sheet, or else it will just ignore my
request without giving any error. Anyway, thanks for your help.

You're question interested me for my own personal knowledge so I continued to
do a little coding and truned it into a more generalized function which I
have include below if it can be of use to you feel free to use it.

'---------------------------------------------------------------------------------------
' Procedure : DelWrkSht
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Delete a worksheet from an Excel workbook
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strWrkBk Workbook to delete the worksheet in/from (full path and
filename)
' strWrkSht Worksheet to be deleted
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function DelWrkSht(strWrkBk As String, strWrkSht As String) As Boolean
'Uses late binding so no references are required to Excel
Dim xlApp As Object
Dim xlWrkBk As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo DelWrkSht_Error
Set xlApp = CreateObject("excel.application")
Else
On Error GoTo DelWrkSht_Error
End If

Set xlWrkBk = xlApp.workbooks.Open(strWrkBk)

xlApp.DisplayAlerts = False 'surpress user confirmation prompt
xlApp.worksheets(strWrkSht).Delete
xlApp.DisplayAlerts = True 'reengage user confirmation prompt
xlApp.Visible = True

Set xlApp = Nothing
Set xlWrkBk = Nothing

DelWrkSht = True
If Err.Number = 0 Then Exit Function

DelWrkSht_Error:
DelWrkSht = False
If Err.Number = 9 Then
'Worksheet not found
MsgBox "Worksheet '" & strWrkSht & "' not found in Workbook '" &
strWrkBk & "'", vbCritical
Exit Function
ElseIf Err.Number = 1004 Then
'Workbook not found
MsgBox "Unable to locate Workbook '" & strWrkBk & "'", vbCritical
Exit Function
Else
'Othere Errors
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: basExcel / DelWrkSht" & vbCrLf
& _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Function
End If
End Function

i'm having trouble deleting excel worksheets from access. I've tried all
four methods below but none of them seem to work.

[quoted text clipped - 20 lines]

xlApp.Visible = True