-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: shitalpdesai
Message 9 in Discussion
hi twinklekumar, i am sending u code for convering Excel file to SQL Server.
Public Sub prcGetDetailsOfExcelFile(ByVal strProvider As String, ByVal strPath
As String, ByVal strWorkSheetName As String)
Dim strSql As String
Dim connectExcel As OleDb.OleDbConnection
Dim cmdOleDBCommand As OleDb.OleDbCommand
'Dim schTable As DataTable
Dim DtOleDBAdp As OleDb.OleDbDataAdapter
Dim dtExcelSet As DataSet
Dim intTableColCount As Integer
connectExcel = New OleDb.OleDbConnection()
connectExcel = New OleDbConnection("Provider=" & strProvider & ";Data Source="
& strPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
connectExcel.Open()
strTableName = strWorkSheetName.Trim
strSql = "select * from [" & strWorkSheetName & "]"
cmdOleDBCommand = New OleDbCommand(strSql, connectExcel)
DtOleDBAdp = New OleDbDataAdapter(cmdOleDBCommand)
dtExcelSet = New DataSet()
DtOleDBAdp.Fill(dtExcelSet, "Excel")
dblImportRecordCount = dtExcelSet.Tables(0).Rows.Count
MsgBox(dtExcelSet.Tables(0).Rows.Count)
prcFieldFillListBoxWithFieldName(uiImportedFiledsLst, dtExcelSet)
End Sub
this way you can get the data into access table.
Following is to get WorkSheet name of Excel workbook.
Dim intWorkSheetCount As Integer
Dim dtXlsSchema As DataTable
Dim ConnectExcel As New OleDbConnection()
Dim XlsConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
ConnectExcel.ConnectionString = XlsConn
ConnectExcel.Open()
' Get a list of tables (worksheets) in the XLS file.
dtXlsSchema = ConnectExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
For intWorkSheetCount = 0 To dtXlsSchema.Rows.Count - 1
If UCase(dtXlsSchema.Rows(intWorkSheetCount).Item("Table_Name").ToString) <>
"DATABASE" Then
uiTableNamecmb.Items.Add(Replace(dtXlsSchema.Rows(intWorkSheetCount).Item("Table_Name"),
"'", ""))
End If
Next
uiTableNamecmb.SelectedValue = 1
ConnectExcel.Close()
ConnectExcel.Dispose()
please if u have any dount then please let me know.
regards
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDOTNET/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
