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

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

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

take a look at your code.... what table are your binding the data grid view? Then take a read of post #2

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

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

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

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

---

Quote:

---

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.