Get column names from a specific sheet
In continuation from here…
We created a file earlier here.
To retrieve the column names, we store the data from the relevant sheet in a Datatable and then loop though it to get the column names. One thing to note here is that we are assuming that Row 1 is not empty and has column headers else we can’t use this.
This is how our code will look like
Imports System.Data.OleDb Public Class Form1 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim olecon As OleDbConnection = New OleDbConnection Dim olecmd As OleDbCommand = New OleDbCommand Dim FilePath As String = "C:\Temp\" Dim FileName As String = "EmployeeDatabase.xlsx" '~~> Construct your connection string Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FilePath & FileName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES;""" olecon.ConnectionString = connstring olecon.Open() '~~> Add data from Sheet1 to a Datatable Dim adapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", olecon) Dim ds = New DataSet() adapter.Fill(ds, "myTable") Dim dt As DataTable = ds.Tables("myTable") '~~> Loop through the data table to get the column names For Each column As DataColumn In dt.Columns MessageBox.Show(column.ColumnName.ToString) Next '~~> Close the connection olecon.Close() End Sub End Class