Sub loadoutputaging()
'On Error Resume Next
Dim oExcelApp   As Excel.Application
Dim oWs         As Excel.Worksheet
Dim oWb         As Excel.Workbook
Dim rowcounter, rowcountersum As Integer
Dim loopcounter, loopcountersum As Integer
Dim monthenddate As Date

Dim totalsumof, col10sum, col13sum, col14sum, col15sum, col16sum, col17sum, col18sum, col19sum, col20sum As Long

loopcounter = 0
totalsumof = 0: col10sum = 0: col13sum = 0: col14sum = 0: col15sum = 0: col16sum = 0: col17sum = 0: col18sum = 0: col19sum = 0: col20sum = 0
rowcountersum = 7
rowcounter = 7

monthenddate = CDate(DateAdd("d", -1, DateAdd("m", 1, Month(Now()) & "/1/" & Year(Now()))))

'Screen.MousePointer = vbHourglass

'VARIABLE USE TO ACCESS EXCEL MACRO SUB CODES
'Dim xlmodule As Object

objCommand.ActiveConnection = strConnection

objCommand.CommandText = "Select COUNT(*) AS custname from finaaccnttbl"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

totalrowinaging = objRS.fields("custname") 'RETURNS TOTAL RECORDS IN FINAACCNTTBL
'MsgBox totalrowinaging

Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible = True

oExcelApp.Workbooks.Open FileName:="E:\AGING Automation with Quickbooks\Aging\Aging\Aging .xls", ReadOnly:=False, ignoreReadOnlyRecommended:=True

' ========================== FOR MAIN WORKSHEET ============================

oExcelApp.Sheets("Main").Select
Set oWb = oExcelApp.ActiveWorkbook
Set oWs = oExcelApp.ActiveSheet

oWs.Cells(4, 2).Value = monthenddate

' ========================== FOR Dtls WORKSHEET ============================

oExcelApp.Sheets("Dtls").Select
Set oWb = oExcelApp.ActiveWorkbook
Set oWs = oExcelApp.ActiveSheet

'PASSING PARAMETERS TO AGING.XLS MACRO AND RUN IT

oExcelApp.Run "SetupMenus"
oExcelApp.Run "Ins_10R", totalrowinaging - 1

objCommand.CommandText = "Select * from finaaccnttbl"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

objRS.MoveFirst

'PASSING DATA TO AGING.XLS FROM SQL SERVER

For loopcounter = 1 To totalrowinaging
    oWs.Cells(rowcounter, 3).Value = objRS.fields("loantype")
    oWs.Cells(rowcounter, 4).Value = objRS.fields("custname")
    oWs.Cells(rowcounter, 5).Value = CDate(Format$(objRS.fields("dategranted"), "mm/dd/yyyy"))
    oWs.Cells(rowcounter, 6).Value = CDate(Format$(objRS.fields("datedue"), "mm/dd/yyyy"))
        If objRS.fields("termtype") <> "Month(s) Lumpsum" Then
            oWs.Cells(rowcounter, 7).Value = 12
            oWs.Cells(rowcounter, 8).Value = "24.00%"
        Else
            oWs.Cells(rowcounter, 7).Value = 9
            oWs.Cells(rowcounter, 8).Value = "36.00%"
        End If
    oWs.Cells(rowcounter, 9).Value = toMoney(objRS.fields("amountgranted"))
    oWs.Cells(rowcounter, 10).Value = toMoney(objRS.fields("runningbalace"))
    rowcounter = rowcounter + 1
    objRS.MoveNext
Next

    'oExcelApp.Run "delrow7" 'USE TO DELETE THE ORIGINAL CELL BEING COPIED WHEN THE ROWS ARE POPULATED FROM SQL SERVER
    oExcelApp.Run "Sort_Rows" 'USE TO SORT AGING BY LOANTYPE(CAN BE CHANGE TO CUSNAME ANYTIME)
    
    'SUMMING UP ALL COLUMS IN AGING(Dtls)
    
     For loopcountersum = 1 To totalrowinaging
     
        totalsumof = totalsumof + oWs.Cells(rowcountersum, 9).Value
        col10sum = col10sum + oWs.Cells(rowcountersum, 10).Value
        col13sum = col13sum + oWs.Cells(rowcountersum, 13).Value
        col14sum = col14sum + oWs.Cells(rowcountersum, 14).Value
        col15sum = col15sum + oWs.Cells(rowcountersum, 15).Value
        col16sum = col16sum + oWs.Cells(rowcountersum, 16).Value
        col17sum = col17sum + oWs.Cells(rowcountersum, 17).Value
        col18sum = col18sum + oWs.Cells(rowcountersum, 18).Value
        col19sum = col19sum + oWs.Cells(rowcountersum, 19).Value
        col20sum = col20sum + oWs.Cells(rowcountersum, 20).Value
        
         
        rowcountersum = rowcountersum + 1
       
     Next
        
        'PASSING SUM TOTAL TO EACH CELL IN AGING(Dtls)
        
        oWs.Cells(7 + Val(totalrowinaging), 9).Value = totalsumof
        oWs.Cells(7 + Val(totalrowinaging), 10).Value = col10sum
        oWs.Cells(7 + Val(totalrowinaging), 13).Value = col13sum
        oWs.Cells(7 + Val(totalrowinaging), 14).Value = col14sum
        oWs.Cells(7 + Val(totalrowinaging), 15).Value = col15sum
        oWs.Cells(7 + Val(totalrowinaging), 16).Value = col16sum
        oWs.Cells(7 + Val(totalrowinaging), 17).Value = col17sum
        oWs.Cells(7 + Val(totalrowinaging), 18).Value = col18sum
        oWs.Cells(7 + Val(totalrowinaging), 19).Value = col19sum
        oWs.Cells(7 + Val(totalrowinaging), 20).Value = col20sum
    
    oWs.Cells(1, 1).Select

    'oExcelApp.ActiveWorkbook.save
    'oExcelApp.quit
    
    Set oWs = Nothing
    Set oWb = Nothing
    Set oExcelApp = Nothing

    Set objCommand = Nothing
    Set objRS = Nothing
    
End Sub