List all open excel sheets in a combobox

Question

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 1

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

Answer 2

I just tested the script below and it seems to work fine. Give it a try and see what you think.

Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim filePath As String = "C:\Users\xxx\YourFile.xlsm"

        Dim connString As String = String.Empty
        If filePath.EndsWith(".xlsx") Then            '2007 Format            
            connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath)
        Else            '2003 Format            
            connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath)
        End If         'Get the Sheets in Excel WorkBook        
        Dim connExcel As New OleDbConnection(connString)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        cmdExcel.Connection = connExcel
        connExcel.Open()
        ComboBox1.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        ComboBox1.DisplayMember = "TABLE_NAME"
        ComboBox1.ValueMember = "TABLE_NAME"
        connExcel.Close()
    End Sub

End Class