I've written a script to alter images in a number of Excel workbooks. In order to properly access the image information, I need to iterate through all Sheet.Shapes on a particular worksheet. I'm having trouble with grouped worksheets.
How do I ungroup worksheets from VB.NET?
I've tried both activating and selecting a sheet in order to ungroup them (suggested elsewhere) but when I try to do this I receive a COMException with HRESULT 0x800A03EC (thrown on the line on which I try to select the worksheet). I've had issues with this particular HRESULT code before and managed to reduce the number of times this exception was thrown with the various workarounds suggested by Microsoft themselves.
As I'm iterating through the worksheets with a For loop, I wondered if perhaps the exception was being raised because because I'm trying to select a particular sheet on that iteration, but the sheet on that iteration is actually the group. I use code like:
For Each xlsSheet As Excel.Worksheet In xlsBook.Worksheets
xlsSheet.Select()
...
Next
Instead of xlsSheet.Select(), I've also tried:
xlsSheet.Activate()xlsBook.Sheets.Select(xlsSheet.Name)xlsBook.Sheets.Select("Manufacturing Instruction")(i.e., explicitly entering the name of the first sheet)
I've also tried putting this whole method in a Try... Catch block with the latter two attempts at activating the sheet in the Catch block (catching System.Runtime.InteropServices.COMException).
At the moment I'm having to stop the program every time this exception is raised, open the Excel document manually, and ungroup the worksheets. I'd prefer not to have to do this!