Cherryex
asked on
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.Applic ation")
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
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.Applic
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
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
'******************&&~##~&
oSheet.Cells(RecordNo, 1) = dsIME.Tables(x).Rows(iRow)
oSheet.Cells(RecordNo, 2) = dsIME.Tables(x).Rows(iRow)
oSheet.Cells(RecordNo, 3) = dsIME.Tables(x).Rows(iRow)
oSheet.Cells(RecordNo, 4) = dsIME.Tables(x).Rows(iRow)
oSheet.Cells(RecordNo, 5) = dsIME.Tables(x).Rows(iRow)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I was able to help. :=)
ASKER