Table creation in excel
System.Data.OleDb is the namespace which contains all the functions required to interact with an excel file.
OleDbConnection is a class used to create and open a connection with an excel file.
OleDbCommand is a class used to take query as an input and for the execution of command(Query).
ExecuteNonQuery() is a function which is used to execute the command(Query).
Before the execution of ExecuteNonQuery(), connection should be open by using:
Conn.Open()
And after the execution of ExecuteNonQuery(), connection should be closed by using:
Conn.Close()
filePath is the location of the file in your computer.
Code:
1. Sub createTable()
2. Dim Conn As New System.Data.OleDb.OleDbConnection
3. Dim Comm As New System.Data.OleDb.OleDbCommand
4. Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
5. " '" & filePath & "';Extended Properties=Excel 8.0;"
6. Comm.Connection = Conn
7. Conn.Open()
8. Comm.CommandText = "CREATE TABLE BasicInfo(Firstname varchar(255),Lastname varchar(255),Age double)"
9. Comm.ExecuteNonQuery()
10. Conn.Close()
11. End Sub
Or we can have a function which takes the path of the excel file as an input parameter and returns a connection string.
Public Function CreateConnString(ByVal _filePath As String) As String
Return "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
" '" & _filePath & "';Extended Properties=Excel 8.0;"
End Function
In this case line number 4 will be replaced as depicted below:
Conn.ConnectionString = CreateConnString(filePath)
0 comments:
Post a Comment