Delete Excel sheet from Access vba editor

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
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Try
With xlWB
.worksheets("sheet2").Delete
End With

Open in new window

0
Gustav BrockCIOCommented:
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.
0
Asatoma SadgamayaAnalystAuthor Commented:
With xlWB
.WorkSheets("sheet2").Delete
End With

is not working
0
Determine the Perfect Price for Your IT Services

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

Gustav BrockCIOCommented:
If you study my code above, you will notice:

    Excel.Application.DisplayAlerts = False

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Asatoma SadgamayaAnalystAuthor 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
0
Gustav BrockCIOCommented:
That code was posted above as well as in your previous question.

And you keep asking while providing zero feedback.
1
Asatoma SadgamayaAnalystAuthor Commented:
Sorry Gustav,
The code is not working , that is the reason for re posting

Cheers
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
NorieAnalyst Assistant Commented:
How is the code not working?

Are you getting errors?

Is the sheet not being deleted/replaced?
0
Asatoma SadgamayaAnalystAuthor 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
0
NorieAnalyst Assistant Commented:
Try making the Excel instance visible and checking what's actually happening there as you step through the code.
0
Gustav BrockCIOCommented:
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.
0
Asatoma SadgamayaAnalystAuthor Commented:
Thanks Gustav. You are a star. Going forward, just give me what i ask for please, sorry for all hassle.
1
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.