Hi all,
I've spent countless hours trying to get this silly EXCEL process to
close once I have used it. If anyone can help it would really REALLy
be appreciated! Below is my code-
Dim t As Integer
Dim testCount As Boolean
testCount = False
For t = 0 To UserListView.It ems.Count - 1
If UserListView.It ems(t).Checked = True Then
testCount = True
End If
Next
If testCount = True Then
Dim j As Integer
Dim k As Integer
Dim strSelectedStar tDate As String
Dim strSelectedEndD ate As String
strSelectedStar tDate = CStr(Format(Dat eTimePickerStar t.Value,
"dd-MM-yyyy"))
strSelectedEndD ate = CStr(Format(Dat eTimePickerEnd. Value,
"dd-MM-yyyy"))
Dim fiPath As IO.FileInfo
Dim strPath As String
fiPath = New IO.FileInfo
System.Reflecti on.Assembly.Get ExecutingAssemb ly.Location)
strPath = fiPath.Director y.FullName
Dim PathStr As String
PathStr = "" & strPath & "\ClientTemplat e.xls"
For j = 0 To UserListView.It ems.Count - 1
If UserListView.It ems(j).Checked = True Then
ConnectToDataba se()
DoSummary(UserL istView.Items(j ).Text,
DateTimePickerS tart.Value, _
DateTimePickerE nd.Value)
Dim EXL As Excel.Applicati on = New
Excel.Applicati on
Dim oWorkbooks As Excel.Workbooks = EXL.Workbooks
Dim oWorkbook As Excel.Workbook =
oWorkbooks.Open (PathStr)
Dim SummaryWorkShee t As Excel.Worksheet
Dim DetailWorksheet As Excel.Worksheet
EXL.Visible = False
SummaryWorkShee t = EXL.Workbooks(1 ).Worksheets(1)
SummaryWorkShee t.Cells(1, 2) =
UserListView.It ems(j).Text
SummaryWorkShee t.Cells(2, 2) =
strSelectedStar tDate
SummaryWorkShee t.Cells(3, 2) = strSelectedEndD ate
If SummaryTable.Ro ws.Count > 0 Then
Dim i As Integer
For i = 0 To SummaryTable.Ro ws.Count - 1
Dim EXLAppName As String
SummaryWorkShee t.Activate()
SummaryWorkShee t.Cells(6 + i, 1) =
SummaryTable.Ro ws(i).Item(0)
SummaryWorkShee t.Cells(6 + i, 2) =
SummaryTable.Ro ws(i).Item(1)
SummaryWorkShee t.Cells(6 + i, 3) =
SummaryTable.Ro ws(i).Item(2)
DetailWorksheet =
EXL.Workbooks(1 ).Worksheets.Ad d(after:=EXL.Wo rkbooks(1).Work sheets(1 +
i))
DetailWorksheet =
EXL.Workbooks(1 ).Worksheets(i + 2)
DetailWorksheet .Name =
SummaryTable.Ro ws(i).Item(0)
DetailWorksheet .Cells(6, 1) =
SummaryTable.Ro ws(i).Item(0)
DetailWorksheet .Cells(6, 2) =
SummaryTable.Ro ws(i).Item(1)
DetailWorksheet .Cells(6, 3) =
SummaryTable.Ro ws(i).Item(2)
DetailWorksheet .Cells(1, 2) =
UserListView.It ems(j).Text
DetailWorksheet .Cells(2, 2) =
strSelectedStar tDate
DetailWorksheet .Cells(3, 2) =
strSelectedEndD ate
DetailWorksheet .Columns("A").C olumnWidth =
16.71
DetailWorksheet .Columns("B").C olumnWidth =
25
DetailWorksheet .Columns("C").C olumnWidth =
50
DetailWorksheet .Columns("D").C olumnWidth =
18
DetailWorksheet .Columns("A:A") .select()
EXL.Selection.H orizontalAlignm ent =
Excel.XlHAlign. xlHAlignLeft
DetailWorksheet .Columns("B:B") .select()
EXL.Selection.H orizontalAlignm ent =
Excel.XlHAlign. xlHAlignLeft
DetailWorksheet .Columns("C:C") .select()
EXL.Selection.H orizontalAlignm ent =
Excel.XlHAlign. xlHAlignLeft
DetailWorksheet .Columns("D:D") .select()
EXL.Selection.H orizontalAlignm ent =
Excel.XlHAlign. xlHAlignLeft
DetailWorksheet .Cells(1, 1).select()
DetailWorksheet .Range("A1:A3") .Font.Bold =
True
DetailWorksheet .Range("A5:C5") .Font.Bold =
True
DetailWorksheet .Range("A8:D8") .Font.Bold =
True
DetailWorksheet .Cells(1, 1) = "UserName:"
DetailWorksheet .Cells(2, 1) = "Start
Date:"
DetailWorksheet .Cells(3, 1) = "End Date:"
DetailWorksheet .Cells(5, 1) = "Applicatio n
Name"
DetailWorksheet .Cells(5, 2) = "Total
Hours"
DetailWorksheet .Cells(5, 3) = "Condition"
DetailWorksheet .Cells(8, 1) = "Log
Details"
DetailWorksheet .Cells(8, 2) = "Start Time"
DetailWorksheet .Cells(8, 3) = "End Time"
DetailWorksheet .Cells(8, 4) = "Chargeable
Hours"
EXLAppName = SummaryGrid.Ite m(i, 0)
DoDetails(UserL istView.Items(j ).Text,
EXLAppName, DateTimePickerS tart.Value, _
DateTimePickerE nd.Value)
For k = 0 To DetailLogTable. Rows.Count - 1
DetailWorksheet .Cells(k + 9, 1) =
DetailLogTable. Rows(k).Item(0)
DetailWorksheet .Cells(k + 9, 2) =
DetailLogTable. Rows(k).Item(1)
DetailWorksheet .Cells(k + 9, 3) =
DetailLogTable. Rows(k).Item(2)
DetailWorksheet .Cells(k + 9, 4) =
DetailLogTable. Rows(k).Item(3)
Next
Next
SummaryWorkShee t.Activate()
End If
oWorkbook.SaveA s("" & strPath & "\" &
UserListView.It ems(j).Text & " from " & strSelectedStar tDate & " to "
& strSelectedEndD ate & ".xls")
oWorkbooks.Clos e()
SummaryWorkShee t = Nothing
DetailWorksheet = Nothing
oWorkbooks = Nothing
oWorkbook = Nothing
EXL.Quit()
EXL = Nothing
DisconDatabase( )
GC.Collect()
End If
Next
I know its alot of code to look at but I'm not quite sure what to
leave out incase that is what is creating the problem!
I'm really running out of ideas..
Thanks
PowaGuy
