problem creating summary reports in excel-VBForums
Results 1 to 6 of 6

Thread: problem creating summary reports in excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    14

    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

  2. #2
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    661

    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.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,962

    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

  4. #4
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,053

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    14

    Re: problem creating summary reports in excel

    Quote Originally Posted by westconn1 View Post
    which part of the process is creating the delay? creating the recordset or writing to excel?

    writing to excel

    TIA

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,962

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.