Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about 60

  1. #1
    Join Date
    3rd September 2017
    Posts
    4

    CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about 60

    Hello all,

    (10% payment sent, in CAD)

    I'm trying to export approximately 150 pdf files with a macro I created. However, after about 60 files, Excel crashes.

    I have tried adding a few doEvents to not avail. The problem doesn't seem to be with the data itself as I have changed the data from the database attached and the same bug occurs.

    The button to export is on the "Rapports" sheet. When you click on it:

    First function
    - It asks you the year you want to export. Only the year 2017 has more than 60 files, so it only occurs when you write "2017" if you want to replicate (without the quotes)
    - It creates a folder in the same folder as your the Excel file if one with the year entered does not exist, otherwise doesn,t do anything
    - Go to export_avis

    Export_avis
    - Iterates through all lines of the "DATA" sheet
    - If the value is not current year, row = row + 1
    - If the value IS current year, go to populate_avis

    Populate_avis
    - Clear the "Avis" form from previous data
    - Populates the document in the sheet "Avis" using the data in the "DATA" sheet
    - Creates a file name for the pdf that will be exported later
    - After populating, go to "Export_pdf"

    Export_pdf
    - Export pdf
    - Go back to Export_avis and repopulate / export until all files have been exported

    Here's the export_pdf function:
    VB:
    Private Sub export_PDF(nomDuPDF As String, anneeCotisation As Variant) 
        NomExcel = ThisWorkbook.Name 
        NomPdf = nomDuPDF & ".pdf" 
        Dim sNomFichierPDF As String 
         
        Application.ScreenUpdating = False 
         
         
        sNomFichierPDF = ThisWorkbook.Path & "\Avis " & CStr(anneeCotisation) & "\" & NomPdf 
         
        DoEvents 
         
         
         
         'ThisWorkbook.Sheets("Avis")
        ThisWorkbook.Sheets("Avis").ExportAsFixedFormat Type:=xlTypePDF, Filename:=sNomFichierPDF _ 
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ 
        :=False, OpenAfterPublish:=False 
         
         
        Application.ScreenUpdating = True 
         
         
        DoEvents 
    End Sub 
    
    

    Test database - Base de données.xlsm

  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,329

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Hello,

    Thank you for posting.
    I will have a look at it and get back to you.

    Wifi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    3rd September 2017
    Posts
    4

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Great thanks!

  4. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,329

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Hello

    I reviewed the full code.
    I can reproduce the error (crash) after about 55 files.
    When I delete the ActiveX objects (checkboxes) and I comment out the associated code, it works fine and exports 149 files.
    See: https://stackoverflow.com/questions/...les-from-excel
    Obviously, this is not what you want to hear I assume.
    You can try checkboxes from the Forms toolbar (untested by myself).

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    4,000

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    I'll give a try if this is not resolved yet

  6. #6
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,329

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Hi Sam,

    The cause of the crashes is found, it's the ActiveX objects. Removing them from the file (and adjusting the code accordingly) lets us create PDF's but without checkboxes.
    In the meantime I tested that using checkboxes from the Forms toolbar, it works flawlessly. The code creates the 149 PDF files for 2017.
    I am now awaiting the feedback of jszine.
    I also improved the current coding and made it more efficient.

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  7. #7
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    4,000

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Alright Wigi. Cheers mate.

  8. #8
    Join Date
    3rd September 2017
    Posts
    4

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Great, I answered your PM. Cheers.

  9. #9
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,329

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Hello,

    Here is the file with the other type of checkboxes.
    Can you run the export process please and validate the outcome ?

    Thank you for the payment, it's correct (10% or 2,5 to Ozgrid and it could be deducted)

    Thanks,

    Wigi
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  10. #10
    Join Date
    3rd September 2017
    Posts
    4

    Re: CAD 25$ (About 20USD) - Auto-exporting about 150 PDFs causes a crash after about

    Thank you for your help. Everything is working.

    Cheers

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 20
    Last Post: October 6th, 2016, 07:56
  2. Replies: 11
    Last Post: July 22nd, 2015, 11:48
  3. Replies: 10
    Last Post: July 18th, 2013, 00:29
  4. 20USD - Shared Value
    By ivandgreat in forum HIRE HELP
    Replies: 16
    Last Post: April 29th, 2013, 02:13
  5. 20USD Transfer Table from Multiple Sheets
    By ivandgreat in forum HIRE HELP
    Replies: 20
    Last Post: September 7th, 2012, 15:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno