Hi Mudassar i wrote this code which works only for excel sheet which is named as "Sheet1" what should i do that i works for any excel sheet with any sheet name.
'Dim excelConnectionString As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (path + ";Extended Properties=Excel 12.0;Persist Security Info=False"))
Dim excelConnectionString As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (path + ";Extended Properties=Excel 12.0;Persist Security Info=False"))
Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
conn.Open()
excelConnection.Open()
Dim cmd As OleDbCommand = New OleDbCommand("Select * from [Sheet1$]", excelConnection)
'Clears any previous data
Dim sClearSQL = "DELETE FROM Desktop "
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, conn)
SqlCmd.ExecuteNonQuery()
Dim sClearSQL1 = "DELETE FROM HostNameDB "
Dim SqlCmd1 As SqlCommand = New SqlCommand(sClearSQL1, conn)
SqlCmd1.ExecuteNonQuery()
Dim dReader As OleDbDataReader = cmd.ExecuteReader
Dim sqlBulk As SqlBulkCopy = New SqlBulkCopy(conn)
Dim table As New DataTable()
table.Columns.Add(New DataColumn("HostName"))
table.Columns.Add(New DataColumn("SerialNumber"))
table.Columns.Add(New DataColumn("UserID"))
I tried this way but its not working out.
Dim sheets As List(Of String) = New List(Of String)
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim connection As DbConnection = factory.CreateConnection
connection.ConnectionString = excelConnectionString
connection.Open()
Dim tbl As DataTable = connection.GetSchema()
connection.Close()
Dim sheetname As String = ""
'For Each row As DataRow In tbl.Rows
' sheetname = CType(row("TABLE_NAME"), String)
If sheetname.EndsWith("$") Then
sheetname = sheetname.Substring(0, sheetname.Length - 1)
If sheetname <> "Sheet1" Then
Label1.Text = "Please Name the excel sheet as Sheet1"
End If
End If
Please Help.
