Richard_U

I have a function which populates an excell spreadsheet, but I am wondering if there is an easier way to do this....

Is there a way to do this line by line instead of cell by cell?

Better yet, is there a way I can drop the entire datagridview into Excel?

Sub kickitout(ByVal dg As DataGridView)
Dim dtr As DataRow

Form2.Show()
Form2.Update()
Dim strExcelFile As String = "C:\temp\Error_Report.xls"

Dim OuputObject As OutputVariables
OuputObject = New OutputVariables
Dim i As Integer

Dim SheetNumber As Integer = 1
With OuputObject.excel

For Each OuputObject.o In dg.Rows
For OuputObject.iItms = 0 To dg.ColumnCount - 1 '-3
.Sheets(SheetNumber).Cells(OuputObject.ictr, OuputObject.iItms + 1) = OuputObject.o.cells.item(OuputObject.iItms).value
Next
OuputObject.ictr += 1
If OuputObject.ictr > 65000 Then

.Worksheets.Add(After:=.Worksheets(.Worksheets.Count))

Dim a As Integer = .Worksheets.Count
OuputObject.ictr = 6
SheetNumber += 1
End If
Next
For Each dtr In dg.Rows
.Sheets(SheetNumber).rows(OuputObject.ictr).select()


Next
.Sheets(1).activate()
.Sheets(1).Range("A5:E5").Select()
With .Selection.Font
.Name = "Arial"
.Size = 14
End With
With .Selection.Interior
.ColorIndex = 34
End With
.Columns("A:I").Select()
.Range("I1").Activate()
OuputObject.ictr = .Worksheets(1).UsedRange.Rows.Count + 3
For i = 0 To dg.Columns.Count - 1
.Cells(5, i + 1) = dg.Columns(i).HeaderText
Next
.Cells(OuputObject.ictr, 1).Select()
.Selection.font.size = 14
.Cells(OuputObject.ictr, 1) = "REPORT SUMMARY"
.Cells(OuputObject.ictr + 1, 1) = "Total Instruments with Error "
.Cells(OuputObject.ictr + 1, 2) = OuputObject.sTotal
.Cells(OuputObject.ictr + 2, 1) = "Total Instruments "
.Cells(OuputObject.ictr + 2, 2) = OuputObject.Number_of_Instruments
.Cells(OuputObject.ictr + 3, 1) = "Percentage of Instruments with Error "
.Cells(OuputObject.ictr + 3, 2) = OuputObject.PercentWithError
.Range(.Cells(OuputObject.ictr + 1, 1), .Cells(OuputObject.ictr + 3, 2)).Select()
.Selection.font.size = 12
.Columns("A:I").EntireColumn.AutoFit()
.Cells(1, 1) = OuputObject.sRTS
.Range(.Cells(2, 1), .Cells(3, 1)).Select()
.Selection.font.size = 12
.Cells(2, 1) = OuputObject.sPar
.Cells(3, 1) = OuputObject.sNumOfErr & " For " & OuputObject.Inst & " With the Error Code of: " & OuputObject.sPrimary
If OuputObject.Location <> "" Then OuputObject.sHold = OuputObject.sHold & "Located in" & OuputObject.Location
OuputObject.sHold = OuputObject.sHold & " That occurred "
OuputObject.sHold = OuputObject.sHold & "between " & OuputObject.sStart & " and " & OuputObject.sEnd
If OuputObject.Member_ID <> "" Then OuputObject.sHold = OuputObject.sHold & " With the Member Name " & OuputObject.Member_ID
.Cells(4, 1) = Trim(OuputObject.sHold)
Try
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close(SaveChanges:=True)
Catch
Form2.Close()
Exit Sub
End Try

End With
Try
System.Diagnostics.Process.Start(strExcelFile)
Catch

End Try
OuputObject = Nothing
Form2.Close()

End Sub