hwired hwired - 7 months ago 48
Vb.net Question

List all open excel sheets in a combobox

I want to have a combobox that lists all the sheet names that are currently open in excel.

I found some code that would list all open workbooks and edited this to to do the sheets. It seems to be correctly cycling through all workbooks correctly but it's only listing the worksheets on the current active excel workbook.

Current code below. This sub is called when the form is loaded.

dt.Columns.Add("sheets")
Try
If IsNothing(app) Then
app = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
End If
Dim bks = app.Workbooks
Dim dks = app.Worksheets
dt.Clear()
For Each bk As Excel.Workbook In bks
Debug.WriteLine(bk.Name)
For Each dk As Excel.Worksheet In dks
Debug.WriteLine(dk.Name)
dt.Rows.Add(dk.Name.ToString())
Next
Next
ComboBox1.DataSource = Nothing
ComboBox1.Items.Clear()
ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "sheets"
Catch ex As Exception
MsgBox(ex.ToString())
End Try

Answer

You are reading this before the loop:

Dim dks = app.Worksheets

Application.Worksheets returns the sheets of the active workbook.

When using two nested loops, something must be wrong when you don't make any use of the outer loop's variable.
Try it like this:

    Dim bks = app.Workbooks
    'Dim dks = app.Worksheets
    dt.Clear()
    For Each bk As Excel.Workbook In bks
        For Each ws As Excel.Worksheet In bk.Worksheets
            dt.Rows.Add(bk.Name & ":" & ws.Name)
        Next
    Next
Comments