export to multiple sheets

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String
Dim cnn1 As SqlConnection
Dim connectionString1 As String
Dim sql1 As String
Dim filename As String
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim misValue As Object = System.Reflection.Missing.Value

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)

Dim Sheet1 As New Excel.Worksheet
Dim Sheet2 As New Excel.Worksheet
Dim Sheet3 As New Excel.Worksheet



xlApp.Workbooks.Add()


'=====================================================
' it creates the sheet1 containing Error Report Per File Type
'=====================================================

connectionString = "data source='" & ComboServers.Text & "'; Initial catalog=master;Integrated Security = True;"
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = "Select [File Type],[Task],count(*) as [Error Count] from master.dbo.temp_Filter_by_error group by [File Type],[task] order by [File Type],[task]"
Dim dscmd As New SqlDataAdapter(sql, cnn)
Dim ds As New DataSet
dscmd.Fill(ds)


Dim Excel As Object = CreateObject("Excel.Application")
If Excel Is Nothing Then
MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
Return
End If


'Export to Excel process
Try
With Excel
.SheetsInNewWorkbook = 5
.Workbooks.Add()
.Worksheets(1).Select()


CType(Excel.Workbooks(1).Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet).Name = "Error Report Per File Type"

Dim i As Integer = 1
For col = 0 To ds.Tables(0).Columns.Count - 1
.cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
.cells(1, i).EntireRow.Font.Bold = True
i += 1
Next
i = 2
Dim k As Integer = 1
For col = 0 To ds.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ds.Tables(0).Rows.Count - 1
.Cells(i, k).Value = ds.Tables(0).Rows(row).ItemArray(col)
i += 1
Next
k += 1
Next
'filename = "c:\Exception Report " & Now().Year & "-" & Now().Month & "-" & Now().Day & " " & Now().Hour & " " & Now().Minute & " " & Now().Second & ".xls"
'.ActiveCell.Worksheet.SaveAs(filename)
End With
'System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
'Excel = Nothing
' MsgBox("Exception errors have been exported succesfully to Excel. The report can be found at '" & filename & "'", MsgBoxStyle.Information)

Catch ex As Exception
MsgBox(ex.Message)
End Try



'=====================================================
' it creates the sheet2 containing Detailed Error Report
'=====================================================

connectionString1 = "data source='" & ComboServers.Text & "'; Initial catalog=master;Integrated Security = True;"
cnn1 = New SqlConnection(connectionString1)
cnn1.Open()
sql1 = "Select * from master.dbo.temp_Filter_by_error order by [Fileset ID],[Doc ID]"
Dim dscmd1 As New SqlDataAdapter(sql1, cnn1)
Dim ds1 As New DataSet
dscmd1.Fill(ds1)



'Export to Excel process
Try
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()


CType(Excel.Workbooks(1).Worksheets("Sheet2"), Microsoft.Office.Interop.Excel.Worksheet).Name = "Detailed Error Report"

Dim i As Integer = 1
For col = 0 To ds1.Tables(0).Columns.Count - 1
.cells(1, i).value = ds1.Tables(0).Columns(col).ColumnName
.cells(1, i).EntireRow.Font.Bold = True
i += 1
Next
i = 2
Dim k As Integer = 1
For col = 0 To ds1.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ds1.Tables(0).Rows.Count - 1
.Cells(i, k).Value = ds1.Tables(0).Rows(row).ItemArray(col)
i += 1
Next
k += 1
Next
'filename = "c:\Exception Report " & Now().Year & "-" & Now().Month & "-" & Now().Day & " " & Now().Hour & " " & Now().Minute & " " & Now().Second & ".xls"
'.ActiveCell.Worksheet.SaveAs(filename)
End With
'System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
'Excel = Nothing
' MsgBox("Exception errors have been exported succesfully to Excel. The report can be found at '" & filename & "'", MsgBoxStyle.Information)

Catch ex As Exception
MsgBox(ex.Message)
End Try


' The excel is created and opened for insert value. We most close this excel using this system
'Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
'For Each i As Process In pro
'i.Kill()
'Next


'=====================================================
' it creates the sheet3 containing Index Errors Report (if the user checks the checkbox
'=====================================================


If CheckBox1.CheckState = True Then

Dim cnn2 As SqlConnection
Dim connectionString2 As String
Dim sql2 As String

connectionString2 = "data source='" & ComboServers.Text & "'; Initial catalog=master;Integrated Security = True;"
cnn2 = New SqlConnection(connectionString2)
cnn2.Open()
sql2 = "Select * from master.dbo.temp_Filter_by_error where Task = 'Index Files' order by [Fileset ID],[Doc ID]"
Dim dscmd2 As New SqlDataAdapter(sql2, cnn2)
Dim ds2 As New DataSet
dscmd2.Fill(ds2)

Try
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()


CType(Excel.Workbooks(1).Worksheets("Sheet3"), Microsoft.Office.Interop.Excel.Worksheet).Name = "Index Errors Report"

Dim i As Integer = 1
For col = 0 To ds2.Tables(0).Columns.Count - 1
.cells(1, i).value = ds2.Tables(0).Columns(col).ColumnName
.cells(1, i).EntireRow.Font.Bold = True
i += 1
Next
i = 2
Dim k As Integer = 1
For col = 0 To ds2.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ds2.Tables(0).Rows.Count - 1
.Cells(i, k).Value = ds2.Tables(0).Rows(row).ItemArray(col)
i += 1
Next
k += 1
Next
'filename = "c:\Exception Report " & Now().Year & "-" & Now().Month & "-" & Now().Day & " " & Now().Hour & " " & Now().Minute & " " & Now().Second & ".xls"
'.ActiveCell.Worksheet.SaveAs(filename)
End With
'System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
'Excel = Nothing
' MsgBox("Exception errors have been exported succesfully to Excel. The report can be found at '" & filename & "'", MsgBoxStyle.Information)

Catch ex As Exception
MsgBox(ex.Message)
End Try


End If

filename = "c:\Exception Report " & Now().Year & "-" & Now().Month & "-" & Now().Day & " " & Now().Hour & " " & Now().Minute & " " & Now().Second & ".xls"
Excel.ActiveCell.Worksheet.SaveAs(filename)

System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing
MsgBox("Exception errors have been exported succesfully to Excel. The report can be found at '" & filename & "'", MsgBoxStyle.Information)


' The excel is created and opened for insert value. We most close this excel using this system
Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
For Each i As Process In pro
i.Kill()
Next


End Sub



Your help will be greatly appreciated.

Thanks!

TammyStart Free Trial