Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    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
    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) --

    Comment


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

      Great thanks!

      Comment


      • #4
        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) --

        Comment


        • #5
          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
          Cheers,

          S M C

          Click To Read: How To Use Tags In Your Threads/Posts
          Please take time to read Forum Rules before posting
          Message To Cross Posters

          Comment


          • #6
            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) --

            Comment


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

              Alright Wigi. Cheers mate.
              Cheers,

              S M C

              Click To Read: How To Use Tags In Your Threads/Posts
              Please take time to read Forum Rules before posting
              Message To Cross Posters

              Comment


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

                Great, I answered your PM. Cheers.

                Comment


                • #9
                  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
                  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) --

                  Comment


                  • #10


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

                    Thank you for your help. Everything is working.

                    Cheers

                    Comment

                    Working...
                    X