hi guys.
I have succesfully loaded all the data needed to an excel worksheet.this works fine.But i want to show the excel on my own form like in form1 rather that displaying it automatically like a normal app. my code(this works for me) below pls help.!
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 SubThank you for your time.!
God bless.!