Delete a worksheet from an EXCEL workbook

[Jacob Mathai]

I use WindowsXP and ACCESS2007.

Using VBA (in ACCESS) how can I delete any one worksheet from an EXCEL file?

I have an EXCEL file called STAFFING.xls. It has several worksheets like ENG, FIN, IT, HR etc. How can I delete just one worksheet (FIN) using VBA in ACCESS?

Any help will be appreciated.

 

[boblarson]

Function delWS(strFileName As String, strSheetName As String)

    Dim objXL As Object
 
    Set objXL = CreateObject("Excel.Application")
 
    With objXL
        .Workbooks.Open (strFileName)
        .ActiveWorkbook.Worksheets(strSheetName).Delete
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    End With

    Set objXL = Nothing

End Function

 

[Jacob Mathai]

Thanks Bob. I was not able to make it to work. No error messages. I was using .xls file. I will play with it more.

I do have references to Microsoft Excel 12.0 object Library.

Jacob

 

[boblarson]

Thanks Bob. I was not able to make it to work. No error messages. I was using .xls file. I will play with it more.

I do have references to Microsoft Excel 12.0 object Library.

Jacob

A reference isn't required because I used late binding. How did you use it? Did you place that function into a STANDARD MODULE and not a form or report module and then how did you call it? It should be like this:

DelWS "C:\Temp\MyXlFile.xls", "SheetNameInQuotes"

just like that.

 

[Jacob Mathai]

Bob, I looked at your objXL code in other threads and made it to work. This is what I used. For simplicity, I used a Form.
-----------------------------------------
Dim objXL As Object

Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")

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
End Sub

----------------------------------
Thanks again for your kind and wonderful support. You had been very helpful.