-
Apr 19th, 2013, 10:03 AM
#1
Thread Starter
Addicted Member
read sheet 2 in an excel file
hi have this code that reads an excel file
Code:
Public Class ReadFromExcel
Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
If ofdOpenFile.ShowDialog() = Windows.Forms.DialogResult.OK Then
txtFileName.Text = ofdOpenFile.FileName
End If
End Sub
Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click
If Not String.IsNullOrEmpty(txtFileName.Text) Then
Try
btnClose.Enabled = False
Dim OExcelHandler As New ExcelHandler()
Dim ds As DataSet = OExcelHandler.GetDataFromExcel(txtFileName.Text.Trim())
If ds IsNot Nothing Then
dgvExcelData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
dgvExcelData.EditMode = DataGridViewEditMode.EditProgrammatically
dgvExcelData.DataSource = ds.Tables(0)
End If
Catch ex As Exception
Finally
btnClose.Enabled = True
End Try
End If
End Sub
how can i get it to read sheet2 in an excel file instead of sheet 1
-
Apr 19th, 2013, 10:37 AM
#2
Re: read sheet 2 in an excel file
It looks like you got your code from here. If you scroll down that page and take a look at the GetDataFromExcel method in the ExcelHandler class it looks like the method returns a data set and each table in the data set represent a worksheet in the table (I have only read the code, not run it). If this is indeed the case, the worksheet 2 would be the second data table in the data set's tables collection.
kevin
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Apr 19th, 2013, 10:57 AM
#3
Thread Starter
Addicted Member
Re: read sheet 2 in an excel file
what would i have to do to change it to ick the one shhet i want.
its not displaying it the sheet i want at all
-
Apr 19th, 2013, 10:59 AM
#4
Re: read sheet 2 in an excel file
take a look at your code.... what table are your binding the data grid view? Then take a read of post #2
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Apr 25th, 2013, 08:48 AM
#5
Thread Starter
Addicted Member
Re: read sheet 2 in an excel file
hi
going back to this
how can i look for the word say cash in sheet one take the balance in the next cell and store that till i need it. and then go to sheet 2 look for broker take the balance in the next cell and store that till i need it and keep going till i have all the info from all the sheets i need
-
Apr 25th, 2013, 09:22 AM
#6
Thread Starter
Addicted Member
Re: read sheet 2 in an excel file
its something along thses lines
Code:
Dim App1 As Object
Dim a As String
App1 = CreateObject("Excel.Application")
' load excel document
App1.Workbooks.Open(txtFileName.Text)
Dim Sheet1 As Worksheet
Sheet1 = App1.Workbooks(1).Worksheets(5)
a = Sheet1.Range("E8").Value.ToString
but instead of naming the cell as i wont no what number is it usually i want it to find a word i make it look for then take the figure
-
Apr 26th, 2013, 12:07 AM
#7
Re: read sheet 2 in an excel file
Here is an example for searching for data in a cell within a specific column
Code:
''' <summary>
''' Shows how to locate text in a specific column of a specfic sheet
''' </summary>
''' <param name="FileName">File to search</param>
''' <param name="SheetName">Sheet to search</param>
''' <param name="SearchItem">Text to locate</param>
''' <param name="Column">Column to search i.e. A</param>
''' <remarks>
''' * We start the search a Column1, meaning first row of the column
''' * All objects are properly disposed w/o calling the GC
''' </remarks>
Private Sub SearchExcelWorkSheet(
ByVal FileName As String,
ByVal SheetName As String,
ByVal SearchItem As String,
ByVal Column As String)
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
Dim xlTargetRange As Excel.Range = Nothing
Dim Result As Excel.Range = Nothing
xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
Dim Proceed As Boolean = False
xlWorkSheets = xlWorkBook.Sheets
' Get the WorkSheet to search on
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Proceed = True
Exit For
End If
Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
' WorkSheet was located so search for text.
If Proceed Then
xlTargetRange = xlWorkSheet.Range(Column & "1")
Result = xlTargetRange.Find(
SearchItem,
,
Excel.XlFindLookIn.xlValues,
Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows,
Excel.XlSearchDirection.xlNext,
False
)
If Result IsNot Nothing Then
MessageBox.Show(String.Format("Found [{0}] on row [{1}]", SearchItem, Result.Row))
Else
MessageBox.Show(String.Format("Did not find [{0}]", SearchItem))
End If
Else
' --- Sheet not located in Excel file
MessageBox.Show(String.Format("{0} was not located in {1}", SheetName, FileName))
End If
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
If Not Result Is Nothing Then
Marshal.FinalReleaseComObject(Result)
Result = Nothing
End If
If Not xlTargetRange Is Nothing Then
Marshal.FinalReleaseComObject(xlTargetRange)
xlTargetRange = Nothing
End If
If Not xlWorkSheets Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkSheets)
xlWorkSheets = Nothing
End If
If Not xlWorkSheet Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
End If
If Not xlWorkBook Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBook)
xlWorkBook = Nothing
End If
If Not xlWorkBooks Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBooks)
xlWorkBooks = Nothing
End If
If Not xlApp Is Nothing Then
Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing
End If
End Sub
Resources for working with Excel
http://www.siddharthrout.com/vb-dot-net-and-excel/
http://code.msdn.microsoft.com/Basic...Excel-4453945d
http://code.msdn.microsoft.com/Worki...Excel-cdd73a85
http://support.microsoft.com/kb/316934
-
Apr 29th, 2013, 07:48 AM
#8
Thread Starter
Addicted Member
Re: read sheet 2 in an excel file
hi
would this work
Code:
Dim App1 As Object
App1 = CreateObject("Excel.Application")
' load excel document
App1.Workbooks.Open(txtFileName.Text)
Dim Sheet1 As Worksheet
' Dim Sheet2 As Worksheet
Sheet1 = App1.Workbooks(1).Worksheets(5)
Dim r As Excel.Range
r = Sheet1.Cells.Find("Total Cash Funds", , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
' a = Sheet1.("Total Cash Funds").Value.ToString
' Sheet2 = App1.Workbooks(1).Worksheets(6)
' b = Sheet2.Range("E3").Value.ToString
End Sub
-
Apr 29th, 2013, 09:22 AM
#9
Re: read sheet 2 in an excel file
Originally Posted by rjhe22
hi
would this work
Code:
Dim App1 As Object
App1 = CreateObject("Excel.Application")
' load excel document
App1.Workbooks.Open(txtFileName.Text)
Dim Sheet1 As Worksheet
' Dim Sheet2 As Worksheet
Sheet1 = App1.Workbooks(1).Worksheets(5)
Dim r As Excel.Range
r = Sheet1.Cells.Find("Total Cash Funds", , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
' a = Sheet1.("Total Cash Funds").Value.ToString
' Sheet2 = App1.Workbooks(1).Worksheets(6)
' b = Sheet2.Range("E3").Value.ToString
End Sub
Try it, this will tell you if it works.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|