s.grainger

VB.NET Delete Sheet from Open workbook

Discussion created by s.grainger on Feb 23, 2012
Latest reply on Mar 13, 2012 by Ahmad Fattahi

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

 

Outcomes