I have an application that pulls PI data, pastes special values all the sheets, deletes unnecessary sheets and e-mails.
I have everything working except deleteing the unnecessary sheets. When I debug it goes through everything with no problems but the sheet doesn't delete. In the log file it says "Deleted: Sheet1" but in the output file Sheet1 is still there. There are no errors being thrown and no Excel Alerts that it is trying to delete the Sheet (I turned on Excel Alerts to be sure). It just seems to go through the line of code as though it's not even there. I have used .Delete() before and it typically triggers an Excel message "Data may exist in the sheet(s) selected for deletion. To permanently delete the data press Delete."
Here is my code and the line that I am having issues with is xlSheet_Write.Delete(). Also if there are any suggestions on how to better compare the sheets in the Output file to the arrOutputSheets array please let me know. (My brain was having an off day and this was all I could come up with.)
'add strOutputSheets to arrOutputSheets arrOutputSheets = strSheetName.Split(";") 'do File Save as... to Read to retain macros and close xlWB_Read.SaveAs(strWriteFile & strWriteFileExt, FileFormat:=intWriteFileFormat) xlWB_Read.Close() 'open the file FreeCOMObject(xlWB_Write) xlWB_Write = xlBooks.Open(strWriteFile & strWriteFileExt) 'set to the first sheet xlSheet_Write = xlWB_Write.Worksheets(1) 'loop through each sheet in the workbook For Each xlSheet_Write In xlWB_Write.Worksheets 'set a variable with the range of cells to be processed strCopyRange = "A1:" & strCopyColEnd & strCopyRowEnd Try 'try to copy the entire range, all at once FreeCOMObject(xlReadRange) xlReadRange = xlSheet_Write.Range(strCopyRange) FreeCOMObject(xlWriteRange) xlWriteRange = xlSheet_Write.Range(strCopyRange) xlReadRange.Copy(Destination:=xlWriteRange) xlWriteRange.Value = xlReadRange.Value Catch ex2 As Exception 'if the above fails then do it one cell at a time For I = 1 To CLng(strCopyRowEnd) For J = 1 To Utilities.ExcelUtils.ColumnLettersToNumber(strCopyColEnd) System.Windows.Forms.Application.DoEvents() FreeCOMObject(xlReadRange) xlReadRange = xlSheet_Write.Cells(I, J) FreeCOMObject(xlReadRange) xlWriteRange = xlSheet_Write.Cells(I, J) Try xlReadRange.Copy(Destination:=xlWriteRange) xlWriteRange.Value = xlReadRange.Value Catch ex3 As Exception xlWriteRange.Value = "Invalid Value" End Try Next Next End Try s = 0
For t = 0 To arrOutputSheets.Length - 1 If xlSheet_Write.Name = arrOutputSheets(t) Then s = s + 1 End If Next If s = 0 Then xlSheet_Write.Delete() objLog.WriteDebugLine("Deleted: " & xlSheet_Read.Name) End If Next 'save objLog.WriteDebugLine("Saving the new report workbook (" & strWriteFile & strWriteFileExt & ")") xlWB_Read.Close(SaveChanges:=True) 'Clean up xlApp1.Quit() blnExcelOpen = False