4 Replies Latest reply: May 15, 2014 9:53 AM by Nick Osdale-Popa RSS

    Exporting tables >16000 lines via VBA

    Wizard _

      Working with Monarch v5.0 under XP Pro with Office 2000.

      I have the Options->Files & Directories->XLS files set to Excel 8.0.

      Using Monarch to export tables to Excel files via VBA - part of a macro that integrates Excel, Monarch, Access and Outlook to send reports nationwide.

      Everything works fine till I get to the file with 18K records, where it chokes.

       

         MonarchObj.ExportTable ("C:ExportFile.xls")  produces no file.

       

      Then VBA stops, I switch to Monarch, export the table to the Excel file manually (manually always works just fine), then tell VBA to continue on its merry way.

       

      Is there a setting or something I'm missing?

       

      I've managed a workaround by exporting it to a CSV file, opening it in Excel, fixing the now-missing leading zeros in the Account number column (permanently lost in other columns) then saving it as Excel and closing it - where I rejoin my regularly scheduled program and re-open it immediately. It works, but it is a slow PITA.

       

      Suggestions?  Anyone?  Beuhler?

        • Exporting tables >16000 lines via VBA
          Gareth Horton

          Hi

           

          It's a limitation of that version.  If you use Monarch 7 Pro, there are new methods JetExportTable/JetExportSummary, which allow exporting 65536 rows (Excel 8.0)

           

          V5 and standard 7 versions only export 16K records (Excel 2.1) with the ExportTable/ExportSummary methods.

           

          Gareth

           

          Originally posted by M Hegge:

          Working with Monarch v5.0 under XP Pro with Office 2000.

          I have the Options->Files & Directories->XLS files set to Excel 8.0.

          Using Monarch to export tables to Excel files via VBA - part of a macro that integrates Excel, Monarch, Access and Outlook to send reports nationwide.

          Everything works fine till I get to the file with 18K records, where it chokes.

           

             MonarchObj.ExportTable ("C:ExportFile.xls")  produces no file.

           

          Then VBA stops, I switch to Monarch, export the table to the Excel file manually (manually always works just fine), then tell VBA to continue on its merry way.

           

          Is there a setting or something I'm missing?

           

          I've managed a workaround by exporting it to a CSV file, opening it in Excel, fixing the now-missing leading zeros in the Account number column (permanently lost in other columns) then saving it as Excel and closing it - where I rejoin my regularly scheduled program and re-open it immediately. It works, but it is a slow PITA.

           

          Suggestions?  Anyone?  Beuhler? /b[/quote]

          • Exporting tables >16000 lines via VBA
            Wizard _

            Gareth -

            Thanks... I was afraid of that.

             

            I came up with a workaround so if the export fails ('ExportFile.xls' does not exist), it re-exports it as a CSV file (no line limitations), opens that, adds the leading zeros back into the Account column, saves it as XLS, erases the CSV and continues on its merry way with a song in its heart and s*** on its shoes.

             

            At least it's backward- and forward-compatible so other users in my company don't run into a version conflict.