Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199

    Slowness in Exporting to excel

    I have set up a buttonwhich exprots a join query result in the Excel File...

    1) There is a template in data is getting recorded line by line.
    2) The edited todays record get yello in color, the whole record row.


    3) Particular set of data gets Red and strike through for row data.

    the export button is pressed everyday for new report, Problem is i have 400 records currently and Access takes 10 mins to write the data in excel with color coding. Can there be way for the reducing in time consumption as future estimation will be thousands of records, which will be much time cosuming?

    thanks for help in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    So it isn't the export that is slow, it is the formatting of the spreadsheet for cell color and strike-through. Are you exporting to existing sheet or a new sheet each time?

    Why export to Excel?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    It is the new sheet all the time, it is for the report purpose, it have to export to excel.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    Still doesn't answer WHY export to Excel. What happens with the exported data that makes Excel required? Why not just print Access report?

    The only thing I can imagine would make the formatting faster is to programmatically apply conditional format rule to a range of cells all at once.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    Quote Originally Posted by June7 View Post
    Still doesn't answer WHY export to Excel. What happens with the exported data that makes Excel required? Why not just print Access report?

    The only thing I can imagine would make the formatting faster is to programmatically apply conditional format rule to a range of cells all at once.
    From excel they do it for post process, only requirment given theyneed in xlsx format and as the reports are reviewed by all and excel is the eqsy availabitly for all.

    The formatting is done programitically only, in the code i have searched the condition and applied the color and strike, etc...

    Is this the roghtway of formatimg or need to do some else logic

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    There is nothing wrong with code moving to each cell and setting format, except that you say it is very slow, although 10 minutes seems a long time for only 400 records. Post your code or provide db. Follow instructions at bottom of my post.

    Selecting an entire range and setting conditional format rule would probably be very fast. Of course, this assumes the same rule can be applied to all cells, such as: > 100
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    Attached is the Code I am having:

    Code:
    Private Sub bt_Export_Click()
    Dim dt As String
    Dim n As Integer
    
    Dim rspart As Dao.Recordset
       Dim xlApp As Excel.Application
       Dim strqry1 As String
      Dim wb As Workbook
       Dim ws As Worksheet
       Dim i As Integer
       Dim temp_path As String
        
    dt = Format(Now, "dd.mm.yyyy")
        
       
       
    strqry1 = "SELECT orderDetails.internalNo AS [Internal No], orderDetails.transactionDate AS [Transaction Date], orderNumber.adminSiteOrderId AS [Order No],   " & _
    "orderNumber.newMemberSiteOrderId AS [Site Order ID], product.rewardTitle AS [Reward Title], product.variationSKU AS [Variation SKU], product.variationTitle AS  " & _
    "[Variation Title], partnerDetails.Name AS [Redemption Partner], category.name AS [Redemption Category], orderDetails.unitPointCost AS Point,  " & _
    "orderDetails.redemptionQuantity AS Qty, orderDetails.pointsRedeemedonReward AS [Points Redeemed], mailMethod.service AS [Fulfilment Method], orderStatus.Status  " & _
    "AS [Redemption Status], orderDetails.additionalField AS [Additional Field], buisness.reference AS [Business ID], buisness.name AS [Business Name], distributor.firstName  " & _
    "AS [Contact First Name], distributor.LastName AS [Contact Last Name], distributor.address1 AS [Address 1], distributor.address2 AS [Address 2], distributor.address3 AS  " & _
    "[Address 3], distributor.state AS [County/State], distributor.postCode AS [Postcode/Zip], country.name AS Country, distributor.contactLogin AS [Contact Login],  " & _
    "distributor.emailAddress AS [Contact Email Address], distributor.phoneNumber AS contact_phone_number, orderDetails.numberIM AS [Number for IM],  " & _
    "orderInvoice.dateRecieved AS [Date received], partnerBiWeekly.Description AS [Order List], orderInvoice.orderSentdate AS [Order sent date], orderInvoice.poNo AS [PO No],  " & _
    "orderInvoice.totalpoamt AS [Total PO Amt], orderInvoice.poSentDate AS [PO Sent Date], orderInvoice.invoiceRecieved AS [Invoice received], orderInvoice.invoiceNo  " & _
    "AS [Invoice No], orderInvoice.invoiceAmt AS [Invoice Amt], orderInvoice.ttDate AS [TT date (wed/Fri)], orderInvoice.paymentNoticeSentDate AS [Payment Notice Sent Date],  " & _
    "orderList.unitPrice AS [Unit Price], orderList.totalCost AS [Total Cost], orderList.adminCost AS [Admin cost], orderList.shippingCost AS [Shipping/ Handling Cost],  " & _
    "orderList.vatTax AS [VAT / GST TAX], orderList.grandTotal AS [Grand Total], orderList.deliveredQty AS [Delivered Qty], orderList.deliveredDate AS [Delivery Date],  " & _
    "orderList.trackingNo AS [Tracking #], orderList.notes AS Notes, orderList.distiInvoice AS [Disti invoice#], orderInvoice.finalStatus AS [Final Status Pending],   " & _
    "orderInvoice.flipStatusDueDate AS [Flip status date], orderDetails.lastUpdate " & _
    "FROM (partnerBiWeekly INNER JOIN partnerDetails ON partnerBiWeekly.Id = partnerDetails.Id) INNER JOIN (orderStatus INNER JOIN (product INNER JOIN ((country  " & _
    "INNER JOIN distributor ON country.Id = distributor.Id) INNER JOIN (category INNER JOIN (buisness INNER JOIN ((((orderDetails INNER JOIN mailMethod ON  " & _
    "orderDetails.methodId = mailMethod.methodId) INNER JOIN orderInvoice ON orderDetails.orderInvoiceId = orderInvoice.orderInvoiceId) INNER JOIN orderList ON  " & _
    "orderDetails.orderListId = orderList.orderListId) INNER JOIN orderNumber ON orderDetails.orderId = orderNumber.orderId) ON buisness.buisnessDetailId =  " & _
    "orderDetails.buisnessDetailId) ON category.categoryId = orderDetails.categoryId) ON distributor.distributorId = orderDetails.distributorId) ON product.productId =  " & _
    "orderDetails.productId) ON orderStatus.orderStatusId = orderDetails.orderStatusId) ON partnerDetails.partnerDetailsId = orderDetails.partnerDetailsId " & _
    "ORDER BY orderDetails.internalNo;"
    
       
       'opening recordset and assiging the field values to variables
       Set rspart = CurrentDb.OpenRecordset(strqry1, dbOpenDynaset)
       rspart.MoveLast
       n = rspart.RecordCount
       
       'Defining invoice report format and storing creation path to a variable
        
       'creating Excel App object and opening the Invoice Template file from specified path
       Set xlApp = CreateObject("Excel.Application")
       Set wb = xlApp.Workbooks.Open(temp_path)
       'inserting variables values to Excel
       Set ws = wb.Worksheets("Sheet1")  'Specify your worksheet name
       rspart.MoveFirst
       For i = 2 To n + 1
    ws.Cells.Font.Name = "Cambria"
    ws.Cells.Font.Size = "11"
    If (rspart![Final Status Pending] = "Cancelled") Then
    ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Interior.ColorIndex = 3 'For Red color
    ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Font.Strikethrough = True
    ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
    ws.Cells(i, 2) = rspart![Transaction Date]
    ws.Cells(i, 3) = rspart![Order No]
    ws.Cells(i, 4) = rspart![Site Order ID]
    ws.Cells(i, 5) = rspart![Reward Title]
    ws.Cells(i, 6) = rspart![Variation SKU]
    ws.Cells(i, 7) = rspart![Variation Title]
    ws.Cells(i, 8) = rspart![Redemption Partner]
    ws.Cells(i, 9) = rspart![Redemption Category]
    ws.Cells(i, 10) = rspart![Point]
    ws.Cells(i, 11) = rspart![Qty]
    ws.Cells(i, 12) = rspart![Points Redeemed]
    ws.Cells(i, 13) = rspart![Fulfilment Method]
    ws.Cells(i, 14) = rspart![Redemption Status]
    ws.Cells(i, 15) = rspart![Additional Field]
    ws.Cells(i, 16) = rspart![Business ID]
    ws.Cells(i, 17) = rspart![Business Name]
    ws.Cells(i, 18) = rspart![Contact First Name]
    ws.Cells(i, 19) = rspart![Contact Last Name]
    ws.Cells(i, 20) = rspart![Address 1]
    ws.Cells(i, 21) = rspart![Address 2]
    ws.Cells(i, 22) = rspart![Address 3]
    ws.Cells(i, 23) = rspart![County/State]
    ws.Cells(i, 24) = rspart![Postcode/Zip]
    ws.Cells(i, 25) = rspart![Country]
    ws.Cells(i, 26) = rspart![Contact Login]
    ws.Cells(i, 27) = rspart![Contact Email Address]
    ws.Cells(i, 28) = rspart![contact_phone_number]
    ws.Cells(i, 29) = rspart![Number for IM]
    ws.Cells(i, 30) = rspart![Date received]
    ws.Cells(i, 31) = rspart![Order List]
    ws.Cells(i, 32) = "Cancelled"
    ws.Cells(i, 33) = "Cancelled"
    ws.Cells(i, 34) = "Cancelled"
    ws.Cells(i, 35) = "Cancelled"
    ws.Cells(i, 36) = "Cancelled"
    ws.Cells(i, 37) = "Cancelled"
    ws.Cells(i, 38) = "Cancelled"
    ws.Cells(i, 39) = "Cancelled"
    ws.Cells(i, 40) = "Cancelled"
    ws.Cells(i, 41) = ""
    ws.Cells(i, 42) = ""
    ws.Cells(i, 43) = ""
    ws.Cells(i, 44) = ""
    ws.Cells(i, 45) = ""
    ws.Cells(i, 46) = ""
    ws.Cells(i, 47) = ""
    ws.Cells(i, 48) = "Cancelled"
    ws.Cells(i, 49) = "Cancelled"
    ws.Cells(i, 50) = ""
    ws.Cells(i, 51) = "Cancelled"
    ws.Cells(i, 52) = "Cancelled"
    ws.Cells(i, 53) = rspart![Flip status date]
    ws.Cells(i, 54) = rspart![lastUpdate]
    Else
    If (Format(rspart![lastUpdate], "mm/dd/yyyy") = Format(Now(), "mm/dd/yyyy")) Then
    ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Interior.ColorIndex = 6 'For Red color
    ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
    ws.Cells(i, 2) = rspart![Transaction Date]
    ws.Cells(i, 3) = rspart![Order No]
    ws.Cells(i, 4) = rspart![Site Order ID]
    ws.Cells(i, 5) = rspart![Reward Title]
    ws.Cells(i, 6) = rspart![Variation SKU]
    ws.Cells(i, 7) = rspart![Variation Title]
    ws.Cells(i, 8) = rspart![Redemption Partner]
    ws.Cells(i, 9) = rspart![Redemption Category]
    ws.Cells(i, 10) = rspart![Point]
    ws.Cells(i, 11) = rspart![Qty]
    ws.Cells(i, 12) = rspart![Points Redeemed]
    ws.Cells(i, 13) = rspart![Fulfilment Method]
    ws.Cells(i, 14) = rspart![Redemption Status]
    ws.Cells(i, 15) = rspart![Additional Field]
    ws.Cells(i, 16) = rspart![Business ID]
    ws.Cells(i, 17) = rspart![Business Name]
    ws.Cells(i, 18) = rspart![Contact First Name]
    ws.Cells(i, 19) = rspart![Contact Last Name]
    ws.Cells(i, 20) = rspart![Address 1]
    ws.Cells(i, 21) = rspart![Address 2]
    ws.Cells(i, 22) = rspart![Address 3]
    ws.Cells(i, 23) = rspart![County/State]
    ws.Cells(i, 24) = rspart![Postcode/Zip]
    ws.Cells(i, 25) = rspart![Country]
    ws.Cells(i, 26) = rspart![Contact Login]
    ws.Cells(i, 27) = rspart![Contact Email Address]
    ws.Cells(i, 28) = rspart![contact_phone_number]
    ws.Cells(i, 29) = rspart![Number for IM]
    ws.Cells(i, 30) = rspart![Date received]
    ws.Cells(i, 31) = rspart![Order List]
    ws.Cells(i, 32) = rspart![Order sent date]
    ws.Cells(i, 33) = rspart![PO No]
    ws.Cells(i, 34) = rspart![Total PO Amt]
    ws.Cells(i, 35) = rspart![PO Sent Date]
    ws.Cells(i, 36) = rspart![Invoice received]
    ws.Cells(i, 37) = rspart![Invoice No]
    ws.Cells(i, 38) = rspart![Invoice Amt]
    ws.Cells(i, 39) = rspart![TT date (wed/Fri)]
    ws.Cells(i, 40) = rspart![Payment Notice Sent Date]
    ws.Cells(i, 41) = rspart![Unit Price]
    ws.Cells(i, 42) = rspart![Total Cost]
    ws.Cells(i, 43) = rspart![Admin cost]
    ws.Cells(i, 44) = rspart![Shipping/ Handling Cost]
    ws.Cells(i, 45) = rspart![VAT / GST TAX]
    ws.Cells(i, 46) = rspart![Grand Total]
    ws.Cells(i, 47) = rspart![Delivered Qty]
    ws.Cells(i, 48) = rspart![Delivery Date]
    ws.Cells(i, 49) = rspart![Tracking #]
    ws.Cells(i, 50) = rspart![notes]
    ws.Cells(i, 51) = rspart![Disti invoice#]
    ws.Cells(i, 52) = rspart![Final Status Pending]
    ws.Cells(i, 53) = rspart![Flip status date]
    ws.Cells(i, 54) = rspart![lastUpdate]
    Else
    ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
    ws.Cells(i, 2) = rspart![Transaction Date]
    ws.Cells(i, 3) = rspart![Order No]
    ws.Cells(i, 4) = rspart![Site Order ID]
    ws.Cells(i, 5) = rspart![Reward Title]
    ws.Cells(i, 6) = rspart![Variation SKU]
    ws.Cells(i, 7) = rspart![Variation Title]
    ws.Cells(i, 8) = rspart![Redemption Partner]
    ws.Cells(i, 9) = rspart![Redemption Category]
    ws.Cells(i, 10) = rspart![Point]
    ws.Cells(i, 11) = rspart![Qty]
    ws.Cells(i, 12) = rspart![Points Redeemed]
    ws.Cells(i, 13) = rspart![Fulfilment Method]
    ws.Cells(i, 14) = rspart![Redemption Status]
    ws.Cells(i, 15) = rspart![Additional Field]
    ws.Cells(i, 16) = rspart![Business ID]
    ws.Cells(i, 17) = rspart![Business Name]
    ws.Cells(i, 18) = rspart![Contact First Name]
    ws.Cells(i, 19) = rspart![Contact Last Name]
    ws.Cells(i, 20) = rspart![Address 1]
    ws.Cells(i, 21) = rspart![Address 2]
    ws.Cells(i, 22) = rspart![Address 3]
    ws.Cells(i, 23) = rspart![County/State]
    ws.Cells(i, 24) = rspart![Postcode/Zip]
    ws.Cells(i, 25) = rspart![Country]
    ws.Cells(i, 26) = rspart![Contact Login]
    ws.Cells(i, 27) = rspart![Contact Email Address]
    ws.Cells(i, 28) = rspart![contact_phone_number]
    ws.Cells(i, 29) = rspart![Number for IM]
    ws.Cells(i, 30) = rspart![Date received]
    ws.Cells(i, 31) = rspart![Order List]
    ws.Cells(i, 32) = rspart![Order sent date]
    ws.Cells(i, 33) = rspart![PO No]
    ws.Cells(i, 34) = rspart![Total PO Amt]
    ws.Cells(i, 35) = rspart![PO Sent Date]
    ws.Cells(i, 36) = rspart![Invoice received]
    ws.Cells(i, 37) = rspart![Invoice No]
    ws.Cells(i, 38) = rspart![Invoice Amt]
    ws.Cells(i, 39) = rspart![TT date (wed/Fri)]
    ws.Cells(i, 40) = rspart![Payment Notice Sent Date]
    ws.Cells(i, 41) = rspart![Unit Price]
    ws.Cells(i, 42) = rspart![Total Cost]
    ws.Cells(i, 43) = rspart![Admin cost]
    ws.Cells(i, 44) = rspart![Shipping/ Handling Cost]
    ws.Cells(i, 45) = rspart![VAT / GST TAX]
    ws.Cells(i, 46) = rspart![Grand Total]
    ws.Cells(i, 47) = rspart![Delivered Qty]
    ws.Cells(i, 48) = rspart![Delivery Date]
    ws.Cells(i, 49) = rspart![Tracking #]
    ws.Cells(i, 50) = rspart![notes]
    ws.Cells(i, 51) = rspart![Disti invoice#]
    ws.Cells(i, 52) = rspart![Final Status Pending]
    ws.Cells(i, 53) = rspart![Flip status date]
    ws.Cells(i, 54) = rspart![lastUpdate]
    End If
    End If
    rspart.MoveNext
    Next i
    xlApp.Application.DisplayAlerts = False
            'xlApp.Application.ActiveWorkbook.CheckCompatibility = False
            'xlApp.Application.ActiveWorkbook.SaveAs FileName:="E:\XYZ\Redemptions\APAC\Order Processing\MDB\Database Files\MasterLog\Master Log_Mumbai_" & dt & ".xlsx", FileFormat:=-4143
            xlApp.Application.ActiveWorkbook.SaveAs FileName:="E:\XYZ\Redemptions\APAC\Order Processing\MDB\Database Files\MasterLog\Master Log_Mumbai_" & dt & ".xlsx"
            xlApp.Application.ActiveWorkbook.Close
            xlApp.DisplayAlerts = True
    'wb.SaveAs inv_rptpath 'Saves as Excel
       
       
       
     
       xlApp.Quit
    
       Set ws = Nothing
       Set wb = Nothing
       Set xlApp = Nothing
       
       rspart.Close
     
    
    
    MsgBox "Master Log data successfully Exported to D:\XYZ\MasterLog_" & dt & ".xls", vbInformation
    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    I have code very much like this and doesn't take 10 minutes to write a few hundred records, maybe 30 seconds, but I don't format the cells. The code isn't actually moving cursor to each cell and formatting is applied to a range. Exactly what I suggested and would have coded.

    How fast is the procedure if no formatting?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    approx 7 mins for 450 records, how would i go with the formating code

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    Don't understand the question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    Quote Originally Posted by June7 View Post
    Don't understand the question.
    The code isn't actually moving cursor to each cell and formatting is applied to a range. Exactly what I suggested and would have coded.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    Quoting my own comment doesn't help me understand what you are asking.

    I said the code is already what I would have done.

    Did you test with the formatting code commented out?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    It took 7 min w/o formatting

    The quote is to ask if i format cellwise not rangwise, will it differ?

    Moment i think this w/o formatting it took 7 mins, formatting will go higher OR may be its issue with networking, because it is placed in Server.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,929
    My files are also on server. However, in my case it is an existing spreadsheet that has the code and is pulling the data from Access.

    In your case Access is creating a new spreadsheet and pushing the data. Is this a split design? Does user open a frontend on their computer? The spreadsheet is saved on the server? Yes, issue could be network.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    drunkenneo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    199
    It is soon will b split design, and they will use it from front end, yes spreadsheet is onthe server.

    Could you provide some more information in case like split database.

    Andi will try out with the code of formating in the excel sheet?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  2. Exporting to excel
    By Ray67 in forum Import/Export Data
    Replies: 8
    Last Post: 07-26-2012, 10:24 AM
  3. Exporting to Excel
    By RAMSES955i in forum Import/Export Data
    Replies: 1
    Last Post: 07-04-2012, 02:06 PM
  4. Exporting to Excel
    By Andrias14 in forum Access
    Replies: 3
    Last Post: 03-20-2012, 06:04 AM
  5. Exporting to Excel
    By TheDeceived in forum Programming
    Replies: 0
    Last Post: 11-15-2010, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums