-
Nov 9th, 2017, 10:06 PM
#1
Thread Starter
New Member
problem creating summary reports in excel
i have here my form and when i click create summary report it opens excel apps and loads the necessary data i need and it those shows perfectly the only problem is it takes 30 - 40 minutes on loading and may apps says not responding but if i wait to finished it pops up with the data i need.. so in short my problem is why does it takes times to load before finishing the the work load?
i hope some one here can help me..
TIA
Code:
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Sub cmdGenerateReport_Click()
On Error Resume Next
Me.MousePointer = 13
DB_Connect
Dim JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, TOTAL As Double
Set xlwbook = xl.Workbooks.Open(App.Path & "\book1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
qry = "SELECT DISTINCT (CUSTOMERNAME) AS CUSTOMER, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 1 AND YEAR(Date) = " & cmbYear.Text & " ) AS JAN, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 2 AND YEAR(Date) = " & cmbYear.Text & " ) AS FEB, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 3 AND YEAR(Date) = " & cmbYear.Text & " ) AS MAR, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 4 AND YEAR(Date) = " & cmbYear.Text & " ) AS APR, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 5 AND YEAR(Date) = " & cmbYear.Text & " ) AS MAY, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 6 AND YEAR(Date) = " & cmbYear.Text & " ) AS JUN, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 7 AND YEAR(Date) = " & cmbYear.Text & " ) AS JUL, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 8 AND YEAR(Date) = " & cmbYear.Text & " ) AS AUG, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 9 AND YEAR(Date) = " & cmbYear.Text & " ) AS SEP, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 10 AND YEAR(Date) = " & cmbYear.Text & " ) AS OCT, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 11 AND YEAR(Date) = " & cmbYear.Text & " ) AS NOV, " & _
"(SELECT SUM(AMOUNT) FROM Invoice WHERE CUSTOMERNAME = A.CUSTOMERNAME AND MONTH(DATE) = 12 AND YEAR(Date) = " & cmbYear.Text & " ) AS [DEC] " & _
" FROM Invoice AS A " & _
" Where Year(Date) = " & cmbYear.Text & _
" GROUP BY CUSTOMERNAME " & _
" ORDER BY CUSTOMERNAME "
Set rs = New ADODB.Recordset
rs.Open qry, DB_Connection, adOpenKeyset, adLockReadOnly
'EXCEL HEADERS
xlsheet.Cells(1, 1) = "CUSTOMER NAME"
xlsheet.Cells(1, 2) = "JAN"
xlsheet.Cells(1, 3) = "FEB"
xlsheet.Cells(1, 4) = "MAR"
xlsheet.Cells(1, 5) = "APR"
xlsheet.Cells(1, 6) = "MAY"
xlsheet.Cells(1, 7) = "JUN"
xlsheet.Cells(1, 8) = "JUL"
xlsheet.Cells(1, 9) = "AUG"
xlsheet.Cells(1, 10) = "SEP"
xlsheet.Cells(1, 11) = "OCT"
xlsheet.Cells(1, 12) = "NOV"
xlsheet.Cells(1, 13) = "DEC"
xlsheet.Cells(1, 14) = "TOTAL"
'INITIALIZE VALUES
Row = 2
JAN = 0
FEB = 0
MAR = 0
APR = 0
MAY = 0
JUN = 0
JUL = 0
AUG = 0
SEP = 0
OCT = 0
NOV = 0
DEC = 0
TOTAL = 0
If Not rs.EOF And Not rs.BOF Then
While Not rs.EOF
Label1.Caption = "Processing : " & Trim(rs!CUSTOMER)
xlsheet.Cells(Row, 1) = Trim(rs!CUSTOMER)
If IsNull(rs!JAN) Then JAN = 0 Else JAN = Val(rs!JAN)
If IsNull(rs!FEB) Then FEB = 0 Else FEB = Val(rs!FEB)
If IsNull(rs!MAR) Then MAR = 0 Else MAR = Val(rs!MAR)
If IsNull(rs!APR) Then APR = 0 Else APR = Val(rs!APR)
If IsNull(rs!MAY) Then MAY = 0 Else MAY = Val(rs!MAY)
If IsNull(rs!JUN) Then JUN = 0 Else JUN = Val(rs!JUN)
If IsNull(rs!JUL) Then JUL = 0 Else JUL = Val(rs!JUL)
If IsNull(rs!AUG) Then AUG = 0 Else AUG = Val(rs!AUG)
If IsNull(rs!SEP) Then SEP = 0 Else SEP = Val(rs!SEP)
If IsNull(rs!OCT) Then OCT = 0 Else OCT = Val(rs!OCT)
If IsNull(rs!NOV) Then NOV = 0 Else NOV = Val(rs!NOV)
If IsNull(rs!DEC) Then DEC = 0 Else DEC = Val(rs!DEC)
TOTAL = JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT + NOV + DEC
xlsheet.Cells(Row, 2) = JAN
xlsheet.Cells(Row, 3) = FEB
xlsheet.Cells(Row, 4) = MAR
xlsheet.Cells(Row, 5) = APR
xlsheet.Cells(Row, 6) = MAY
xlsheet.Cells(Row, 7) = JUN
xlsheet.Cells(Row, 8) = JUL
xlsheet.Cells(Row, 9) = AUG
xlsheet.Cells(Row, 10) = SEP
xlsheet.Cells(Row, 11) = OCT
xlsheet.Cells(Row, 12) = NOV
xlsheet.Cells(Row, 13) = DEC
xlsheet.Cells(Row, 14) = TOTAL
Label1.Caption = "Processed : " & Trim(rs!CUSTOMER)
Row = Row + 1
rs.MoveNext
Wend
End If
rs.Close
xlwbook.Save
xl.ActiveWorkbook.Close False, App.Path & "\book1.xls"
xl.Quit
ShellExecute Me.hwnd, "open", App.Path & "\book1.xls", vbNullString, vbNullString, SW_SHOW
Me.MousePointer = 1
End Sub
Private Sub Form_Load()
Set xlwbook = xl.Workbooks.Open(App.Path & "\book1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
cmbYear.Text = Year(Now)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set xlwbook = Nothing
Set xl = Nothing
End Sub
-
Nov 10th, 2017, 03:55 AM
#2
Re: problem creating summary reports in excel
Hi Freddy,
some strange select stament you have there.
take a look at PIVOT.
this is done in Access with the Northwind Database
create a new Query in Access and copy this to it..
Code:
TRANSFORM Sum(CCur([Order Details].[Unitprice]*[Quantity]*(1-[Discount])/100)*100) AS Total
SELECT Format([OrderDate],"yyyy") AS [in Year], Customers.CompanyName
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Format([OrderDate],"yyyy"), Customers.CompanyName
PIVOT Format([OrderDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Nov 10th, 2017, 04:14 PM
#3
Re: problem creating summary reports in excel
which part of the process is creating the delay? creating the recordset or writing to excel?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 10th, 2017, 07:43 PM
#4
Re: problem creating summary reports in excel
How about creating an Excel file (without opening Excel) -
Excel Class - write to an XLS file without DLL's or Excel automation
http://www.planet-source-code.com/vb...11898&lngWId=1
HTH,
Rob
-
Nov 10th, 2017, 11:03 PM
#5
Thread Starter
New Member
Re: problem creating summary reports in excel
Originally Posted by westconn1
which part of the process is creating the delay? creating the recordset or writing to excel?
writing to excel
TIA
-
Nov 11th, 2017, 12:28 AM
#6
Re: problem creating summary reports in excel
if you can change the database table to prevent nulls in the monthly figures, you could do away with all the if statements
you could put a formula in column 14 to calculate the totals, then use excels copyfromrecordset method instead of looping all the rows
if you can not change the database table, you can probably change nulls to 0 in your sql,
edit: see http://www.vbforums.com/showthread.p...85#post5232085 about dissallowing nulls in table
here is some example for converting nulls to 0 in the sql, depending on database type
https://www.w3schools.com/sql/sql_isnull.asp
Last edited by westconn1; Nov 11th, 2017 at 01:22 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|