WPF: How to retrieve excel sheet names without opening excel file in vb.net?

Question

xaml

<Window x:Class="MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
    <Button x:Name="Button1" Height="35" Width="100" Content="Click Me" Margin="50,0,0,0"/>
    <ComboBox x:Name="Combobox1" Height="35" Width="100" Margin="50,100,0,0"/>
</Grid>
</Window>

vb.net

Imports System.Data
Class MainWindow
Private Sub Button1_Click(sender As Object, e As RoutedEventArgs) Handles Button1.Click
    Dim myOleDbConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
    myOleDbConnection.Open()
    Dim myDataTable As System.Data.DataTable = myOleDbConnection.GetOleDbSchemaTable(schema:=System.Data.OleDb.OleDbSchemaGuid.Tables, restrictions:=Nothing)
    Combobox1.ItemsSource = (myOleDbConnection.GetSchema("Tables", New String() {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable().Select(Function(d) d("TABLE_NAME").ToString.Replace("$", "")).Distinct().ToArray)
    myOleDbConnection.Close()
End Sub
End Class

When I run the codes above I am getting this error: https://prnt.sc/jtiupv

Answer 1

Below code possibly helpful for you. here fname referred as excel file location.

 Try
                Dim StrConn As [String] = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fname & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
                Dim Conn As New OleDbConnection(StrConn)
                Conn.Open()
                'Dim dtSheets As DataTable =
                '    Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
                'Dim listSheet As New List(Of String)
                Location.Text = fname  'Label Control
                SheetName.Items.Clear() ' Combobox clear
                SheetName.Items.AddRange(Conn.GetSchema("Tables", New String() {Nothing, Nothing, Nothing, "TABLE"}
                                                 ).AsEnumerable().Select(Function(d) d("TABLE_NAME").ToString.Replace("$", "")).Distinct().ToArray)   'Binding All sheetname to combobox
            Catch ex As Exception
                Log.ErrorMessage(ex.Message, Me.Name) 'local log creation manual code
            End Try