Open save file has old xml sheet names in it and causes a warning?

Question

Open save file has old xml sheet names in it and causes a warning?

Jonathan Brotto 1,071

I used a code to add sheets and then I rename them, but it does not work. It skips the delete part I wrote.

The part that adds and rename sheets

 Dim addSheets As Excel.Sheets = Globals.ThisAddIn.Application.Sheets

        addSheets.Add()
        addSheets.Add()
        addSheets.Add()

        Dim sheetRename As Excel.Worksheet = Globals.ThisAddIn.Application.Sheets("Sheet1")
        sheetRename.Name = "Raw SAP"
        sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet2")
        sheetRename.Name = "Territory Summary"
        sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet3")
        sheetRename.Name = "Customer Summary"
        sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet4")
        sheetRename.Name = "Product Summary"

        'Sort sheets in correct order
        sheetRename = Globals.ThisAddIn.Application.Sheets("Raw SAP")
        sheetRename.Move(Before:=Globals.ThisAddIn.Application.Worksheets(1))

        sheetRename = Globals.ThisAddIn.Application.Sheets("Customer Summary")
        sheetRename.Move(After:=Globals.ThisAddIn.Application.Worksheets(3))

        sheetRename = Globals.ThisAddIn.Application.Sheets("Product Summary")
        sheetRename.Move(After:=Globals.ThisAddIn.Application.Worksheets(4))

        sheetRename = Globals.ThisAddIn.Application.Sheets("Territory Summary")
        sheetRename.Move(Before:=Globals.ThisAddIn.Application.Worksheets(2))

Delete portion that does not work it seems.

        Try
            sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet2")
            sheetRename.Delete()
            sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet3")
            sheetRename.Delete()
            sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet4")
            sheetRename.Delete()

        Catch ex As Exception

        End Try

Answer 1

Michael Taylor 41,431

In your code you have a try-catch around the delete. Most likely it is failing to find the sheets and therefore not deleting them. Put some logic into the catch block to report the error so you can see why it is failing. Ultimately you should debug this by putting a breakpoint on that code and step through it to see exactly what is going wrong.

Jonathan Brotto 1,071 Reputation points

2022-01-24T16:35:05.67+00:00

Got this error

System.Runtime.InteropServices.COMException
HResult=0x8002000B
Message=Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>

For this line of code
sheetRename = Globals.ThisAddIn.Application.Sheets("Sheet2")
Michael Taylor 41,431 Reputation points

2022-01-24T16:59:49.567+00:00
That means it couldn't find Sheet2 so it failed. Since it failed nothing got deleted.

Given your original code then that is correct. Your original code finds the sheets by their original name (e.g. Sheet2) and then renames them. Hence when you try to find the original named sheet you won't find it because it was renamed.

Note that if you need to find a worksheet that may or may not exist then the existing API doesn't seem to support that. Therefore you should create a helper function that tries to access the sheet wrapped in a try-catch. This will make it easier to use elsewhere. (not tested...)

Public Function TryGetWorksheet ( worksheets As Sheets, name As String ) As Worksheet Try TryGetWorksheet = worksheets(name) Catch TryGetWorksheet = Nothing End Try End Function Dim sheet As Worksheet = TryGetWorksheet(Globals.ThisAddin.Application.Worksheets)
Jonathan Brotto 1,071 Reputation points

2022-01-24T17:06:47.2+00:00

So basically what I did before is the best solution?
Michael Taylor 41,431 Reputation points

2022-01-24T19:25:47.007+00:00

Your original post isn't completely correct in my opinion. It adds 3 new sheets then renames the existing sheets. Hence if you then try to delete the original sheets the calls will fail, because you renamed them.

If your goal is to simply rename existing sheets then the find/rename is correct. If you want to add new sheets and then delete old sheets then calling Delete will be necessary. In either case the TryGetWorksheet function would be useful because the sheet you're looking for may not exist.

Open save file has old xml sheet names in it and causes a warning?

1 answer

Activity