הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Wednesday, July 11, 2012 8:32 PM
I want to import Data from Column A to Column H and from Row 14 to the end.
I use this code but it's not working :
Dim SourceBookPath As String = Nothing
Me.OpenFileDialog1.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx"
Me.OpenFileDialog1.ShowDialog()
If Not Me.OpenFileDialog1.FileName Is Nothing Then
SourceBookPath = Me.OpenFileDialog1.FileName
End If
'=================================================
Dim MsExcel = CreateObject("Excel.Application")
Dim ExcelBook = MsExcel.Workbooks.Open(SourceBookPath)
Dim con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceBookPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1"
Dim str As String = "select * from Sheet1"
Dim ds As New DataSet
Dim I, J As Integer
Dim lastRow As Long = 0
lastRow = MsExcel.Range("A15").Find("*", MsExcel.Range("A15"), XlFindLookIn.xlValues, XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).row
For I = 8 To lastRow
Dim r As DataRow = ds.Tables("Sheet1").NewRow
For J = 1 To 8
r(J - 1) = MsExcel.Cells(I, J).Value
Next J
ds.Tables("Sheet1").Rows.Add(r)
Next I
DataGridView1.DataSource = ds.Tables("Sheet1")
Any one can help me Please..
All replies (11)
Wednesday, July 11, 2012 9:10 PM ✅Answered | 1 vote
Hello,
The following shows how to use an OleDb connection object to open an Excel file, read from a specific range in sheet 1. Note that in this example since we specify no header in the connection each column name is Fn i.e. three columns returns F1, F2, F3. So instead I show aliasing each column with Col1, Col2 etc (you can name them whatever you like too).
So we read Book1_2003.xls in the same folder as the app. Get range A16:F17 from sheet1.
VS2008
Imports System.Data.OleDbPublic Class frmMainForm Private Sub frmMainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim FileName As String = "Book1_2003.xls" If IO.File.Exists(FileName) Then Dim cb As New OleDbConnectionStringBuilder With {.DataSource = FileName, .Provider = "Microsoft.Jet.OLEDB.4.0"} cb.Add("Extended Properties", "Excel 8.0; IMEX=1; HDR=No;") Dim cn As New System.Data.OleDb.OleDbConnection With {.ConnectionString = cb.ConnectionString} cn.Open() Dim cmd As OleDbCommand = New OleDbCommand("SELECT F1 as Col1, F2 as Col2, F3 As Col3, F4 As Col4, F5 As Col5 FROM [Sheet1$A16:F17]", cn) Dim dt As New DataTable dt.Load(cmd.ExecuteReader) cn.Close() DataGridView1.DataSource = dt End If End SubEnd Class
VS2010
Imports System.Data.OleDbPublic Class frmMainForm Private Sub frmMainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim FileName As String = "Book1_2003.xls" If IO.File.Exists(FileName) Then Dim cb As New OleDbConnectionStringBuilder With { .DataSource = FileName, .Provider = "Microsoft.Jet.OLEDB.4.0" } cb.Add("Extended Properties", "Excel 8.0; IMEX=1; HDR=No;") Dim cn As New System.Data.OleDb.OleDbConnection With { .ConnectionString = cb.ConnectionString } cn.Open() Dim cmd As OleDbCommand = New OleDbCommand( _ <Statement> SELECT F1 as Col1, F2 as Col2, F3 As Col3, F4 As Col4, F5 As Col5 FROM [Sheet1$A16:F17] </Statement>.Value, cn) Dim dt As New DataTable dt.Load(cmd.ExecuteReader) cn.Close() DataGridView1.DataSource = dt End If End SubEnd Class
KSG
Wednesday, July 11, 2012 10:29 PM ✅Answered | 1 vote
One more thing, if you want to get the last row on a sheet OleDb can not do this but by using the OleDb example I gave you along with the following code you can get the last row using early binding automation. The row returned is the lasted used row. So let's say your data started on A10 and the code below returns 15, this means simply subtract 15 from 10 and now you have five used rows which can be used in the range from my first example.
Please note there is a good deal of code involved and need to explain why. I like to clean up and dispose of all automation objects at the end of the code. One sure fire way to prevent this is to use two "." in any line using early binding. Of course calling the garbage collector can prevent this but it is not a good idea to call the GC, let the run time engine do this instead as needed.
Usage
Dim RowsUsed As Integer = UsedRows(IO.Path.Combine(Application.StartupPath, "KSG1.xlsx"), "Sheet1")If RowsUsed > -1 Then MessageBox.Show(String.Format("There are [{0}] rows in sheet1", RowsUsed))Else Console.WriteLine("Sheet1 not found.")End If
Import statements for code below (meaning you must add a reference to Microsoft.Office.Interop.Excel)
Imports Excel = Microsoft.Office.Interop.ExcelImports Microsoft.OfficeImports System.Runtime.InteropServices
Function
Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer Dim RowsUsed As Integer = -1 If IO.File.Exists(FileName) Then Dim xlApp As Excel.Application = Nothing Dim xlWorkBooks As Excel.Workbooks = Nothing Dim xlWorkBook As Excel.Workbook = Nothing Dim xlWorkSheet As Excel.Worksheet = Nothing Dim xlWorkSheets As Excel.Sheets = Nothing xlApp = New Excel.Application xlApp.DisplayAlerts = False xlWorkBooks = xlApp.Workbooks xlWorkBook = xlWorkBooks.Open(FileName) xlApp.Visible = False xlWorkSheets = xlWorkBook.Sheets For x As Integer = 1 To xlWorkSheets.Count xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet) If xlWorkSheet.Name = SheetName Then Dim xlCells As Excel.Range = Nothing xlCells = xlWorkSheet.Cells RowsUsed = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells) xlCells = Nothing Exit For End If Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet) xlWorkSheet = Nothing Next xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() ReleaseComObject(xlWorkSheets) ReleaseComObject(xlWorkSheet) ReleaseComObject(xlWorkBook) ReleaseComObject(xlWorkBooks) ReleaseComObject(xlApp) Else Throw New Exception("'" & FileName & "' not found.") End If Return RowsUsed End Function
KSG
Thursday, July 12, 2012 12:09 PM ✅Answered
Could you please help me with this code :
Dim SourceBookPath As String = Nothing Me.OpenFileDialog1.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx" Me.OpenFileDialog1.ShowDialog() If Not Me.OpenFileDialog1.FileName Is Nothing Then SourceBookPath = Me.OpenFileDialog1.FileName End If ' Dim i As Integer Dim j As Integer Dim MsExcel1 = CreateObject("Excel.Application") Dim ExcelBook1 = MsExcel1.Workbooks.Open(SourceBookPath) Dim xlws = ExcelBook1.worksheets(1) '====================================================== Dim Tblsheet As New Data.DataTable Tblsheet.TableName = "Sheet1" For c As Integer = 0 To 7 Dim col As New Data.DataColumn col.ColumnName = xlws.Cells(14, c + 1).Value.ToString Tblsheet.Columns.Add(col) ds.Tables.Add(Tblsheet) Next '====================================================== Dim lastRow As Long = 0 lastRow = MsExcel1.Range("A14").Find("*", MsExcel1.Range("A14"), XlFindLookIn.xlValues, , XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).row For i = 15 To lastRow Dim r As DataRow = ds.Tables("Sheet1").NewRow For j = 1 To 8 r(j - 1) = xlws.Cells(i, j).Value Next j ds.Tables("Sheet1").Rows.Add(r) Next i DataGridView1.DataSource = ds.Tables("Sheet1") '====================================================== Try TextBox1.Text = MsExcel1.Cells(6, 2).Value TextBox2.Text = MsExcel1.Cells(6, 4).Value DateTimePicker1.Text = MsExcel1.Cells(6, 6).Value TextBox4.Text = MsExcel1.Cells(6, 8).Value TextBox5.Text = MsExcel1.Cells(8, 2).Value DateTimePicker2.Text = MsExcel1.Cells(8, 4).Value TextBox7.Text = MsExcel1.Cells(8, 6).Value TextBox8.Text = MsExcel1.Cells(8, 8).Value DateTimePicker3.Text = MsExcel1.Cells(11, 2).Value Catch ex As Exception MsgBox(ex.Message) End Try ExcelBook1.Close(False) MsExcel1.Quit() xlws = Nothing ExcelBook1 = Nothing MsExcel1 = Nothing
Hello,
From reviewing what you have now my suggestion is to work with what I gave you. Use the automation code to get the last used row then use that value in the OleDb code to return a DataTable. In regards to the DataTable, if you want column headers which I did not do this is simple to change.
No header (first row is data)
cb.Add("Extended Properties", "Excel 8.0; IMEX=1; HDR=No;")
Header (gives column names based on the first row)
cb.Add("Extended Properties", "Excel 8.0; IMEX=1; HDR=Yes;")
For assigning values to controls you can use DataBinding i.e.
TextBox1.DataBindings.Add("Column name", DataTableName,"Text")
In the above example for DataBinding replace "Column Name" with the column name in the DataTable to bind too. DataTableName is the resulting DataTable (in my example it is "dt") that contains the Excel data using DataTable.Load.
KSG
Wednesday, July 11, 2012 9:35 PM
Could you please write this code in VB.NET because i tried to convert it using :
http://www.developerfusion.com/tools/convert/csharp-to-vb/
and i got an error.
Wednesday, July 11, 2012 9:39 PM
Could you please write this code in VB.NET because i tried to convert it using :
http://www.developerfusion.com/tools/convert/csharp-to-vb/
and i got an error.
If you could please see this link :
http://www.codeproject.com/Questions/419754/How-to-Import-Specific-Data-from-Excel-Sheet-to-Da
Wednesday, July 11, 2012 9:49 PM
Kevin,
first, thanks for the example. Though, could you please insert the " _" line continuation (next time)? I know there are different VB versions out there, so it's normal that code examples might require modifications. I have no problem to do this also (using VB 2008), but regarding the line continuation, the reader has to read the whole source code line by line to find out where a logical line starts and ends in order to insert the " _" at the right places.
Thx.
Armin
Wednesday, July 11, 2012 10:14 PM
Kevin,
first, thanks for the example. Though, could you please insert the " _" line continuation (next time)? I know there are different VB versions out there, so it's normal that code examples might require modifications. I have no problem to do this also (using VB 2008), but regarding the line continuation, the reader has to read the whole source code line by line to find out where a logical line starts and ends in order to insert the " _" at the right places.
Thx.
Armin
Armin, My apologies, today I have been immersed in VS2010 and was not thinking about lower versions. Will need to monitor myself on this.
KSG
Wednesday, July 11, 2012 10:20 PM
Could you please write this code in VB.NET because i tried to convert it using :
http://www.developerfusion.com/tools/convert/csharp-to-vb/
and i got an error.
Sorry, I was not thinking and gave you VS2010 syntax, just added VS2008 syntax.
KSG
Wednesday, July 11, 2012 11:15 PM
Armin, My apologies, today I have been immersed in VS2010 and was not thinking about lower versions. Will need to monitor myself on this.
Kevin, really no need to apologize! Different versions are a general issue, but I think we all can handle it well. I also write my VB 2008 code and, first, do not care about previous versions. Well, I admit that < 2008 is rarely used. (maybe VB6... ;-) ) I wouldn't have replied if I hadn't been a little annoyed about this new feature in 2010 (but that may be a matter of taste), so it's actually not anything you are responsible for. Your contributions are always welcome anyway.
Armin
Thursday, July 12, 2012 11:07 AM
Could you please help me with this code :
Dim SourceBookPath As String = Nothing
Me.OpenFileDialog1.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx"
Me.OpenFileDialog1.ShowDialog()
If Not Me.OpenFileDialog1.FileName Is Nothing Then
SourceBookPath = Me.OpenFileDialog1.FileName
End If
'
Dim i As Integer
Dim j As Integer
Dim MsExcel1 = CreateObject("Excel.Application")
Dim ExcelBook1 = MsExcel1.Workbooks.Open(SourceBookPath)
Dim xlws = ExcelBook1.worksheets(1)
'======================================================
Dim Tblsheet As New Data.DataTable
Tblsheet.TableName = "Sheet1"
For c As Integer = 0 To 7
Dim col As New Data.DataColumn
col.ColumnName = xlws.Cells(14, c + 1).Value.ToString
Tblsheet.Columns.Add(col)
ds.Tables.Add(Tblsheet)
Next
'======================================================
Dim lastRow As Long = 0
lastRow = MsExcel1.Range("A14").Find("*", MsExcel1.Range("A14"), XlFindLookIn.xlValues, , XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).row
For i = 15 To lastRow
Dim r As DataRow = ds.Tables("Sheet1").NewRow
For j = 1 To 8
r(j - 1) = xlws.Cells(i, j).Value
Next j
ds.Tables("Sheet1").Rows.Add(r)
Next i
DataGridView1.DataSource = ds.Tables("Sheet1")
'======================================================
Try
TextBox1.Text = MsExcel1.Cells(6, 2).Value
TextBox2.Text = MsExcel1.Cells(6, 4).Value
DateTimePicker1.Text = MsExcel1.Cells(6, 6).Value
TextBox4.Text = MsExcel1.Cells(6, 8).Value
TextBox5.Text = MsExcel1.Cells(8, 2).Value
DateTimePicker2.Text = MsExcel1.Cells(8, 4).Value
TextBox7.Text = MsExcel1.Cells(8, 6).Value
TextBox8.Text = MsExcel1.Cells(8, 8).Value
DateTimePicker3.Text = MsExcel1.Cells(11, 2).Value
Catch ex As Exception
MsgBox(ex.Message)
End Try
ExcelBook1.Close(False)
MsExcel1.Quit()
xlws = Nothing
ExcelBook1 = Nothing
MsExcel1 = Nothing
Thursday, July 12, 2012 12:13 PM
Could you please help me with this code :
Dim SourceBookPath As String = Nothing Me.OpenFileDialog1.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx" Me.OpenFileDialog1.ShowDialog() If Not Me.OpenFileDialog1.FileName Is Nothing Then SourceBookPath = Me.OpenFileDialog1.FileName End If ' Dim i As Integer Dim j As Integer Dim MsExcel1 = CreateObject("Excel.Application") Dim ExcelBook1 = MsExcel1.Workbooks.Open(SourceBookPath) Dim xlws = ExcelBook1.worksheets(1) '====================================================== Dim Tblsheet As New Data.DataTable Tblsheet.TableName = "Sheet1" For c As Integer = 0 To 7 Dim col As New Data.DataColumn col.ColumnName = xlws.Cells(14, c + 1).Value.ToString Tblsheet.Columns.Add(col) ds.Tables.Add(Tblsheet) Next '====================================================== Dim lastRow As Long = 0 lastRow = MsExcel1.Range("A14").Find("*", MsExcel1.Range("A14"), XlFindLookIn.xlValues, , XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).row For i = 15 To lastRow Dim r As DataRow = ds.Tables("Sheet1").NewRow For j = 1 To 8 r(j - 1) = xlws.Cells(i, j).Value Next j ds.Tables("Sheet1").Rows.Add(r) Next i DataGridView1.DataSource = ds.Tables("Sheet1") '====================================================== Try TextBox1.Text = MsExcel1.Cells(6, 2).Value TextBox2.Text = MsExcel1.Cells(6, 4).Value DateTimePicker1.Text = MsExcel1.Cells(6, 6).Value TextBox4.Text = MsExcel1.Cells(6, 8).Value TextBox5.Text = MsExcel1.Cells(8, 2).Value DateTimePicker2.Text = MsExcel1.Cells(8, 4).Value TextBox7.Text = MsExcel1.Cells(8, 6).Value TextBox8.Text = MsExcel1.Cells(8, 8).Value DateTimePicker3.Text = MsExcel1.Cells(11, 2).Value Catch ex As Exception MsgBox(ex.Message) End Try ExcelBook1.Close(False) MsExcel1.Quit() xlws = Nothing ExcelBook1 = Nothing MsExcel1 = Nothing
I would also like to point out that your current code will keep MsExcel1 object in memory until you close the application. Also let's say you run this code 20 times there will be 20 instances of Excel in memory until the app closes. The code I provided will release objects when the are finished thus not causing any issues with performance of the machine this code runs on unlike yours which may very well cause performance issues.
KSG