Delete Excel sheet from Access vba editor

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
May I know why follwing vba code does not work.

I am trying delete an excel sheet from Access vba editor.

Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
objxl.visible=False
Set xlWB = objXL.Workbooks.Open("d:\data\excel\Test1.xls")

With xlWB
.Sheets("sheet2").Delete
End With

xlWB.Save
xlWB.Close
objXL.Quit

Set xlWB = Nothing
Set objXL = Nothing

Thanks in advance.
K
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try
With xlWB
.worksheets("sheet2").Delete
End With

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Why don't you use the code you just have been provided:

https://www.experts-exchange.com/questions/29117236/Access-VBA-delete-a-particular-Worksheet-and-replace-with-new.html

Public Function ReplaceWorksheet( _
    ByVal Filename As String, _
    ByVal WorksheetName As String)

    Dim Excel       As Excel.Application
    Dim Workbook    As Excel.Workbook
    Dim Worksheet   As Excel.Worksheet
    
    Dim Index       As Integer
    Dim Result      As Boolean
    
    Set Excel = New Excel.Application
    Set Workbook = Excel.Workbooks.Open(Filename)
    Excel.Application.DisplayAlerts = False

    ' Locate existing worksheet.
    Index = WorksheetIndex(Workbook, WorksheetName)
    ' Delete existing worksheet.
    Result = Workbook.Worksheets(Index).Delete
    
    If Result = True Then
        ' Create new worksheet with same index and name.
        Set Worksheet = Workbook.Worksheets.Add(Workbook.Worksheets(Index))
        Worksheet.Name = WorksheetName
        
        ' (Re)build content of object Worksheet.
    
        ' Save revised workbook.
        Workbook.Save
    Else
        ' Worksheet couldn't be deleted.
    End If
    
    ' Close and clean up.
    Excel.Application.DisplayAlerts = True
    Set Worksheet = Nothing
    Workbook.Close
    Set Workbook = Nothing
    Set Excel = Nothing
    
    ReplaceWorksheet = Result
    
End Function


' Searches in the collection of worksheets of a workbook for
' a worksheet named Name or with a name starting with Name.
' Returns the index of the worksheet if found.
' Returns zero (0) if the worksheet name is empty or not found.
'
' To lookup the index of a worksheet with an exact name, use:
'
'   Index = ThisWorkbook.Worksheets("Exact Name").Index
'
' 2017-09-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function WorksheetIndex( _
    ByRef Workbook As Workbook, _
    ByVal Name As String) _
    As Integer

    Dim Worksheet   As Excel.Worksheet
    Dim Index       As Integer
    
    If Workbook Is Nothing Then
        ' Nothing to do.
        Exit Function
    ElseIf Name <> "" Then
        ' Loop worksheets.
        For Each Worksheet In Workbook.Worksheets
            If InStr(1, Worksheet.Name, Name, vbTextCompare) = 1 Then
                Index = Worksheet.Index
                Exit For
            End If
        Next
    End If
    
    Set Worksheet = Nothing
    
    WorksheetIndex = Index

End Function

Open in new window

All you need is to exclude the part that creates the new worksheet.

Author

Commented:
With xlWB
.WorkSheets("sheet2").Delete
End With

is not working
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
If you study my code above, you will notice:

    Excel.Application.DisplayAlerts = False

Open in new window

Author

Commented:
Hi All,

This is something I need to achieve, I need to delete skyA sheet and replace it with new set of data using a DoCmd

Public Sub zzzz()
       
Dim objXL As Object
Dim xlWB As Object

Excel.Application.DisplayAlerts = False
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
Set xlWB = objXL.Workbooks.Open("C:\abcz\A.xlsx")
With objXL
.Worksheets("SkyA").Delete
End With
xlWB.Save
xlWB.Close
objXL.Quit


DoCmd.TransferSpreadsheet acExport, 10, "SkyA", "C:\abcz\A.xlsx", True, "SkyA"


Set xlWB = Nothing
Set objXL = Nothing
End sub

Thank you
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That code was posted above as well as in your previous question.

And you keep asking while providing zero feedback.

Author

Commented:
Sorry Gustav,
The code is not working , that is the reason for re posting

Cheers
Instead of saying "Code is not working", describe it. Are you getting an error message? If so, what is it? Is something happening? Is nothing happening?

Make sure you use the right sheet name, not the code name for the worksheet you want to delete.
NorieAnalyst Assistant

Commented:
How is the code not working?

Are you getting errors?

Is the sheet not being deleted/replaced?

Author

Commented:
Hi Guys,

I do not get any errors, but the sheet is not being deleted. DoCmd does its job, so I get a new sheet with sheet name followed by 1.

Cheers
NorieAnalyst Assistant

Commented:
Try making the Excel instance visible and checking what's actually happening there as you step through the code.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
My code, as is, is tested and works.
So, if you modify it, it is your changes that make it not to work.

You keep talking about DoCmd. We don't know why - your question is about deleting (or - in your previous question - replacing) a worksheet.

If you wish to reexport data to the worksheet, do it before or after calling my code. You can't - or rather: it doesn't make sense - to call this within this code.

Author

Commented:
Thanks Gustav. You are a star. Going forward, just give me what i ask for please, sorry for all hassle.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial