Problems Deleting Worksheets in Excel from Access

[Swatkins]

Hi,

I'm exporting some data from an Access Database into an Excel Spreadsheet to create a report in Excel, and I'm running into some problems with that.

The idea is I have an Excel Template with a tab to take raw data from an Access Query, and then a couple tabs that manipulate that data and a final tab to format it for reporting. Basically, the formatted tab uses lookups to find data that's been exported from the query and display them in appropriate columns and rows (producing a result sort of like a cross-tab query but where I've got better control over the presentation of the results). My end-goal is to have this data formatted, to replace all the lookups with hard-coded values data, and then to remove the raw data tab and other tabs with lookup data to have a clean report ready for printing.

It's that last step - deleting all the extraneous tabs that are no longer feeding any equations because I've copy/pasted values over the lookup equations - that's proving vexing. I'm just not sure what's wrong with my code.

Everything through this point is in the same On_Click event code:

Private Sub Command0_Click()


'Note: I'm using early binding right now to take advantage of Intellisense; 
'My plan is to go back and replace this with late binding once I have code that works.

Dim objXLApp as Excel.Application
Dim objXLbook as Excel.Workbook
Dim objXLWS as Excel.Worksheet
Dim strPath as String

'*********************************
'A whole bunch of code - all of which appears to work - goes here,
'Create a new Excel instantiation
'Creating the workbook I want based on the pre-formatted template
'Exporting the Access query data, etc.
'At this point, I have a "Report" tab (Sheet 1) that has the hard-coded data in
'a print-ready format, a "Lookup" tab (Sheet 2) that has a few lookups that
'are not used after the data has been paste-special-valued
'a "DATA" tab (Sheet 3) which has a copy-paste of the raw Access query
'data plus some additional calculations that  make looking up the data easier in
'Excel - this is the sheet that "Report" referred to before I copy/pasted the 
'values in that sheet, and finally a "raw_query" tab (Sheet 4) which has the 
'raw data from the query.
'*****************************************

'The following is where things start going weird:

Dim i as Integer
Set objXLWS = Nothing

For i = 2 to objXLbook.Worksheets.Count

Set objXLWS = objXLbook.Sheets(i)
objXLWS.Delete
Set objXLWS = Nothing
objXLbook.Save 'I don't think this is necessary, but I threw an extra "Save" in
'as a test to make abso-frickin-lutely sure I had captured the deletion...

Next i

objXLbook.Save
objXLbook.Close

End Sub

I don't get any error messages. I actually have a messagebox thrown up in the code to inform me that I'm getting to the right worksheets selected. (The loop is selecting the worksheets as expected.) Nothing hints at the code not running.

But when I open the file in Excel... all of the worksheets are all still there, including the ones that should've been deleted.

What am I missing? Is there something I should be doing to make sure the worksheet deletion goes through? Any ideas why the tabs aren't actually deleting? :banghead:

Thanks,

Stephen

 

[Rx_]

' don't have time to run this, try it out.
For i = 2 to objXLbook.Worksheets.Count
objXLbook.Sheets(i).Select
objXLbook.ActiveWindow.SelectedSheets.Delete
next i
debug.Print "The Worksheets count is: " & objXLbook.Worksheets.Count


You might find this web site useful:
http://www.btabdevelopment.com/ts/tq2xlspecwspath

 

[Swatkins]

I don't think I can use the ActiveWindow method - at any rate I get an error if I try - since I'm not running Excel in an open window, but invisibly from within Access.

I tried replacing the "objXLbook.ActiveWindow.SelectedSheets.Delete" line with this:

objXLbook.ActiveSheets.Delete

Which I thought should have a similar effects.

The results were the same as my previous results: no actual deletion of worksheets.

The added debug line was nice. I put it in the loop to see what happened after each iteration. I get three lines reading "The worksheet count is 4".