Results 1 to 9 of 9

Thread: read sheet 2 in an excel file

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    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

  2. #2
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    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

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    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

  4. #4
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    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

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    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

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    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

  7. #7
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    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

  9. #9
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: read sheet 2 in an excel file

    Quote Originally Posted by rjhe22 View Post
    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
  •  



Click Here to Expand Forum to Full Width