Weird Excel Formatting
- by Gage
Recently a new co-op was hired at our company and has been tasked to run a report. The report queries the database and returns a resultset and from there procedes to create the spreadsheets. Depending on the number of days selected a different number of reports are generated but I do not believe that is relavent to the question. Basically it runs the reports and loops through the resultset but at some point continues to loop through until tow 65536 at which it stops. For Example if the resultset contained 74 records then the first 74 rows would appear normally (formatted yellow) while everything after that would also be formatted yellow although it should be left alone. I am inheriting this code as I to am a new co-op. Apparently this only happens when a "change of guards" happens (New co-op has to run the report).`
DoCmd.SetWarnings False
DoCmd.OpenQuery ("DailySummaryQueryMain")
strSQL = "SELECT * FROM DailySummaryMain"
Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.Echo True, "Running first Report"
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF And Not rs.BOF
xlapp.Range("A" & i).Value = rs.Fields(0).Value
xlapp.Range("B" & i).Value = rs.Fields(1).Value
xlapp.Range("C" & i).Value = rs.Fields(2).Value
Set rs2 = CurrentDb.OpenRecordset("SELECT dbo_StatusType.StatusTypeID, dbo_StatusType.Name FROM dbo_StatusType WHERE (((dbo_StatusType.StatusTypeID)=" & rs.Fields(3) & "))")
rs2.MoveFirst
xlapp.Range("D" & i).Value = rs2.Fields(1).Value
xlapp.Range("E" & i).Value = rs.Fields(4).Value
xlapp.Range("F" & i).Value = rs.Fields(5).Value
xlapp.Range("G" & i).Value = rs.Fields(6).Value
'count number of outages that start and end on same day
If Format(xlapp.Range("F" & i).Value, "mm/dd/yyyy") = Format(xlapp.Range("G" & i).Value, "mm/dd/yyyy") Then
dayCount = dayCount + 1
End If
xlapp.Range("H" & i).Value = rs.Fields(7).Value
xlapp.Range("I" & i).Value = rs.Fields(8).Value
xlapp.Range("J" & i).Value = rs.Fields(9).Value
xlapp.Range("K" & i).Value = rs.Fields(10).Value
xlapp.Range("L" & i).Value = rs.Fields(11).Value
xlapp.Range("M" & i).Value = rs.Fields(12).Value
xlapp.Range("N" & i).Value = rs.Fields(13).Value
'highlite recently modified rows
If rs.Fields(14).Value = "Yes" Then
xlapp.Range("A" & i & ":N" & i).Select
With xlapp.Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
'break apart by sector
If CInt(rs.Fields(2).Value) = 1 Then
row = row1
ElseIf CInt(rs.Fields(2).Value) = 2 Then
row = row2
ElseIf CInt(rs.Fields(2).Value) = 3 Then
row = row3
Else
row = row4
End If
xlapp.Worksheets(CInt(rs.Fields(2).Value) + 1).Activate
xlapp.Range("A" & row).Value = rs.Fields(0).Value
xlapp.Range("B" & row).Value = rs.Fields(1).Value
xlapp.Range("C" & row).Value = rs.Fields(13).Value
xlapp.Range("D" & row).Value = rs.Fields(4).Value
xlapp.Range("E" & row).Value = rs.Fields(5).Value
xlapp.Range("F" & row).Value = rs.Fields(6).Value
xlapp.Range("G" & row).Value = rs.Fields(7).Value
xlapp.Range("H" & row).Value = rs.Fields(8).Value
xlapp.Range("I" & row).Value = rs.Fields(9).Value
xlapp.Range("J" & row).Value = rs.Fields(10).Value
xlapp.Range("K" & row).Value = ""
xlapp.Range("L" & row).Value = rs.Fields(11).Value
xlapp.Range("M" & row).Value = rs.Fields(13).Value
If CInt(rs.Fields(2).Value) = 1 Then
row1 = row1 + 1
ElseIf CInt(rs.Fields(2).Value) = 2 Then
row2 = row2 + 1
ElseIf CInt(rs.Fields(2).Value) = 3 Then
row3 = row3 + 1
Else
row4 = row4 + 1
End If
'activate main summary sheet for next outage
xlapp.Worksheets(1).Activate
i = i + 1
rs.MoveNext
Loop`
Also I should note that this is all happening within an access database which has its tables linked from SQL. The query is extremely slow to run from which I believe is the use of views but thats neither here nor there. All you have to know is attempting to debug takes an enormous amount of time due to having to wait for the recordset to return. My guess is that its not checking to see if the resultset is empty correctly. Is there a way I could check to see if theres a value is rs.Fields(0) and base it off that maybe? That is the ID column and there should always be a value. I am wondering why rs.EOF isn't catching this though.
