itterate through Custom Views Excel object

[vinnie881]

I need to interact with Excel via the Microsoft Excel 9.0 Oject Library.

My issue is I can not itterate through custom views I have created in excel for the life of me

Imports EXCEL

Private sub test()
       Dim ExcelApp As Excel.Application
        ExcelApp = New Excel.Application
        ExcelApp.Visible = True
        Dim wb As Excel.Workbook = ExcelApp.Workbooks.Open("C:\test.xls")
       

         'I can see the count of my custom views by doing
        
         debug.write(wb.CustomViews.Count())

        'I can get my custom view by doing

        Dim cv As Excel.CustomView = wb.CustomViews.Item("MyView1")

        'My issue is I need to itterate through the custom view collection for each view to get the name.
        'I can not hard code each one in.  The below code does not work:

        Dim c As CustomView
        For Each c In wb.CustomViews
            Debug.Write(c.Name())
        Next

        'Any Sugestions?
end sub

 

[Rocksteady]

What error do you get? Is CustomViews enumerable?

Have you tried

For x as integer = 0 to wb.CustomViews.Count() - 1
wb.CustomViews.Item(x)
Next

Or.

For Each c In wb.CustomViews.GetEnumerator
Debug.Write(c.Name())
Next

 

[vinnie881]

I tried both of those and neither worked.

 

[vinnie881]

I have tried everything I can think of. This is REALLY important to get this seemingly simple feature to work. Can someone who is more skilled than I am add the Excel object reference and try on their end. I am stumped, and truly appreciate any help.

All you need to test is a excel file, add a few custom views by just clicking Views>Custom Views >Add

From that point it's just using the code I posted above to interact with the excel.CustomViews class

Thanks!!

 

[Rocksteady]

Again, what error do you get?

 

[vinnie881]

invalid member error

 

[Rocksteady]

On which line?

Try this also.

For Each c as Excel.CustomView In wb.CustomViews
Debug.Write(c.Name())
Next

 

[vinnie881]

This is the exact error using my code and yours

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

Additional information: Member not found.

 

[vinnie881]

I get the same error in this command

Dim x As Object = wb.CustomViews.GetEnumerator

 

[Rocksteady]

GetEnumerator is stuffed, probably not implemented properly.

You cant "For Each" CustomViews.

 

[Rocksteady]

You can't get the Views by index so there is no way to do this.

 

[Rocksteady]

I got it. Stupid thing isn't a 0 based collection. Have to start at 1.

For x As Integer = 1 To wb.CustomViews.Count
Debug.WriteLine(wb.CustomViews(x).Name)
Next

 

[vinnie881]

HELL YA!!!.... Way to go..

 

[vinnie881]

I can now stop trying to write code to parse the hex data in the XLS file itself for the names....

THANKS AGAIN!

 

[vinnie881]

Also, just curious. How did you find that out? Trial and error?