How to move to a different worksheet when exporting a dataset to Excel

Hi,

I have an export that sends data from a dataset to Microsoft Excel.  It works fine but in my dataset I have 2 tables, I would like to write the data from each table to a different Worksheet in the same Excel workbook.  

I will be looping through, the first time writing the data from the first data table the second time from the second datatable.

Can anyone tell me how I can move between the worksheets please?

This is the main bit of the code I have already:

        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim iRow As Integer

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oExcel.WorkSheets.Add
        oSheet = oBook.Worksheets(2)


        'SYNTAX: .Cells(Row,Column)= cellvalue
        Do Until ReportNo = 3

            'If after running the first report no records are found in the second, then exit.
            If dsIME.Tables(2).Rows.Count < 1 Then
                Exit Do
            End If

            With oSheet

                '____  TITLE INFO  ____
                .Cells(1, 1) = "INCOMPLETE TIMESHEETS: "
                .Range("A1:A2").Select()
                With oExcel.Selection.Font
                    .Name = "Verdana"
                    .ColorIndex = 5
                    .FontStyle = "Bold"
                    .Size = "12"
                End With
                '_____________________________________________________________________

            End With

            iRow = 1

            '############################################################################################################################
            'MAIN REPORT
            Dim RecordNo As Integer = 1
            Try
                Do Until RecordNo = dsIME.Tables(2).Rows.Count + 1 ' Do until there are no rows left
                    '******************&&~##~&& WRITE POINT &&~##~&&***************************
                    oSheet.Cells(RecordNo, 1) = dsIME.Tables(x).Rows(iRow).Item(0)
                    oSheet.Cells(RecordNo, 2) = dsIME.Tables(x).Rows(iRow).Item(1)
                    oSheet.Cells(RecordNo, 3) = dsIME.Tables(x).Rows(iRow).Item(2)
                    oSheet.Cells(RecordNo, 4) = dsIME.Tables(x).Rows(iRow).Item(3)
                    oSheet.Cells(RecordNo, 5) = dsIME.Tables(x).Rows(iRow).Item(4)

                    iRow = iRow + 1
                    RecordNo += 1
                Loop
                '____ end of loop

            Catch ex As Exception
                MessageBox.Show("Error from Excel report " & ex.Message)
                oExcel.Quit()
                oExcel = Nothing
            End Try

            ReportNo += 1

        Loop
CherryexAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi Cherryex;

Try this to seltect a worksheet:

    Excel.Application.Worksheets(WorkSheetNumber).Select

So to select worksheet 2 it would be.

    Excel.Application.Worksheets(2).Select


Fernando
0
 
CherryexAuthor Commented:
Thank you that worked perfectly.
0
 
Fernando SotoRetiredCommented:
Glad I was able to help. :=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.