Sub RMHistoricalAging()
Dim strAsOfDate As String
strAsOfDate = Range("B1").Value
If Not IsDate(strAsOfDate) Then
MsgBox ("Invalid Date")
Exit Sub
End If
Dim strAccountNumbers As String
strAccountNumbers = Range("b2")
'clear the sheet
'Dim deleteRange As Range
'deleteRange = ("a3:aa10")
'deleteRange.Rows.delete
Range("a6:aa10000").Rows.delete
' Create a connection object.
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=vmGP11;INITIAL CATALOG=two;INTEGRATED SECURITY=sspi;"
'Now open the connection.
oConn.Open strConn
' Create a recordset object.
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
Dim oCMD As ADODB.Command
Set oCMD = New ADODB.Command
oCMD.ActiveConnection = oConn
oCMD.CommandType = adCmdStoredProc
oCMD.CommandText = "FP_RMHistoricalAgingSummary"
oCMD.Parameters.Append oCMD.CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 0, strAsOfDate)
oCMD.Parameters.Append oCMD.CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 0, strAccountNumbers)
Set oRS = oCMD.Execute
If Not oRS.EOF Then
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A6").CopyFromRecordset oRS
Sheet1.Cells.Columns.AutoFit
Else
MsgBox ("No data returned")
End If
'format the TOTAL rows
Dim intRow As Integer
'our data starts on row 6
intRow = 6
'while the third column has values (in our case this will have a value until we get to the bottom)
While Sheet1.Range("C" & intRow) > ""
'if the first column has an empty cell (indicates that we're on a 'total' row
If Sheet1.Range("A" & intRow) = "" Then
'format the cells bold, and to have a top border
Sheet1.Range("C" & intRow & ":E" & intRow).Font.Bold = True
Sheet1.Range("C" & intRow & ":E" & intRow).Borders(xlEdgeTop).LineStyle = xlContinuous
End If
'increment the row counter
intRow = intRow + 1
Wend
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Sub