2 Replies Latest reply: May 15, 2014 9:54 AM by JJames _ RSS

    VBA Script Stops at 16000 lines

    JJames _

      I am running Monarch v 6.01 and Excel 2000. If I change the ExportTable function to the JetExportTable, I will get an "Syntax error message." The Jet Script is:  

       

      MonarchObj.JetExportTable ("G:Accounts_Receivable eportsclientmastermasterclientlist.xls","ClientList",1)

       

      So I have enabled Excel 8.0, and changed my export back to the old ExportTable (which works)but my script stops short.

       

      Do I need to add something from the Excel spreadsheet that calls up Monarch? Thanks...Here is my script:

       

      Private Sub CommandButton11_Click()

      'Private Sub Workbook_Open()

      'Create LookupList Worksheet from JmeclntText file from $mainfrmacctjmeclnt.001.txt

       

          Dim MonarchObj As Object

       

          Dim openfile, openmod, t As Boolean

       

      'If Monarch is currently active GetObject will use Monarch. If it is not use the CreateObject() to 'open another copy of Monarch.

       

          Set MonarchObj = GetObject("", "monarch32")

       

          If MonarchObj Is Nothing Then

       

          Set MonarchObj = CreateObject("Monarch32")

           

      End If

       

          t = MonarchObj.SetLogFile("C:MonTempMprg_g5.log", False)

       

      'Open jmeclnt text file and open mod file and create Excel file

       

          openfile = MonarchObj.setreportfile("G:$G_mainfrmacctjmeclnt.001.txt", False)

       

              If openfile = True Then

           

          openmod = MonarchObj.setmodelfile("c:program filesmonarchmodelsjmeclnt.mod")

       

              If openmod = True Then

           

      'Set filter for each frame and export to Excel

       

              MonarchObj.currentfilter = "no filter"

                   

              MonarchObj.ExportTable ("G:Accounts_Receivable eportsclientmastermasterclientlist.xls")

               

          End If

       

      End If

       

      MonarchObj.closealldocuments

       

      MonarchObj.Exit

       

      End Sub

       

      [size="1"][ December 06, 2002, 11:26 AM: Message edited by: JJames ][/size]

        • VBA Script Stops at 16000 lines
          Mark Huston

          Try writing the output file as a .CSV file or .DBF file instead of .XLS. It looks the same when you open the output file in Excel, but bypasses the 16,384 row maximum for early versions of Excel. This is a limitation of versions of Monarch before 6.1, not the version of Excel installed on your machine.

          Some Excel trivia: Versions of Excel before 7.0 had 16,384 rows because that's 2 to the fourteenth power. Current versions of Excel have 65,536 rows which is 2 to the sixteenth power.

          • VBA Script Stops at 16000 lines
            JJames _

            Mark,

            I changed my post and added that I was running 6.01 as you were responding to my post. I still have a problem with the syntax error with the JetExport, but your suggestion transended the error.

             

            Thanks for the csv suggestion, it worked like a charm!