Deleting an Excel worksheet in Access

[Kraig Kuusinen]

I am not able to delete worksheets after I am done with them. I copy lines
from "Sheet2" and "Sheet3" to "Sheet1" thoughout the code. I want to delete
them before saving the template to another Excel file for production.
Here is the declarations and code:
Dim objExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Dim xlSheet3 As Excel.Worksheet

Set objExcel = New Excel.Application
Set xlBook = objExcel.Workbooks.Open(sTemplatePath & sTemplateName)
Set xlSheet1 = xlBook.Worksheets("Sheet1")
Set xlSheet2 = xlBook.Worksheets("Sheet2")
Set xlSheet3 = xlBook.Worksheets("Sheet3")

xlBook.Worksheets("Sheet2").Delete
xlBook.Worksheets("Sheet3").Delete

sInvName = sContractNum & " Centralized Bill " & sMonth & " " & sYear & "
Bill.xls"
xlBook.SaveAs (sCentralPath & sInvName)

Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
Set xlSheet3 = Nothing
Set xlBook = Nothing
objExcel.Quit
Set objExcel = Nothing

Thanks for any help.
Kraig

 

[aaron.kempf]

i would reccomend deleting all access files on every machine on every
network; and uninstalling excel

excel is a complete disease; and anyone that isn't writing shit in
access should be fired on the spot.

excel-- do you really need to pay someone $40k/year TO MAKE THE SAME
DAMN XLS EVERY DAY?

 

[Beth F.]

Kraig,
I have had success deleting sheets by doing something like so:

Dim objExcel As Excel.Application
<snip>
Set objExcel = New Excel.Application
<snip>
objExcel.Sheets("Sheet2").Delete
objExcel.Sheets("Sheet3").Delete
<snip>

HTH,
Beth F.

 

[Kraig Kuusinen]

Thanks Beth, but that did not work. I even tried removing the last worksheet
by itself and that did not work.

Kraig

 

[Ron2005]

I use the following and it seems to work fine:

reportfilename = ReportDirectory & reportfilename

Set es = CreateObject("Excel.Application")

es.Visible = False

es.Workbooks.Open FileName:=reportfilename

es.Sheets("IBRMonthlyExport").Select
es.ActiveWindow.SelectedSheets.Delete
es.Sheets("MonthlySummary").Select
es.ActiveWindow.SelectedSheets.Delete
es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)

es.Application.Quit

Ron