2 Replies Latest reply: May 15, 2014 10:08 AM by starcraftbud _ RSS

    Export table as .xls for old version Monarch

    starcraftbud _

      Hello,

       

      I'm new to this forum. At the firm I work at we use monarch only occaisonally so I know little about the program. I searched around this forum and google for an answer to my question but could not find one, but sorry if this is a repeat.

       

      Hold back the snickers, but I use version 6.01. I left this out of the subject line for fear of ridicule.

       

      I'm having an issue export tables larger than 16,384 rows through excel VBA macros. Let me clarify further. Version 6.01 has the option to export the table as a certain version of excel - the latest being 8.0. I have this as my setting. I use excel 11.0 (I think). I am able to manually export the table just fine. But when my excel macro tries to do it, its like it does not recognize that I have altered the option to export at a higher excel. It still defaults to lower versions of excel and does not allow greater than 16,384 lines.

       

      I realize this is a monarch forum and not excel, but here is my excel VBA code in case it is of any use:

       

      Sub Monarch()

      '----


      'Monarchs the report

      '----


      Application.DisplayAlerts = False

      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:\Temp\MPrg_G5.log", False)

      openfile = MonarchObj.SetReportFile("c:\TEMP\report.dat", False)

      If openfile = True Then

      openmod = MonarchObj.SetModelFile("c:\report.MOD")

      If openmod = True Then

      MonarchObj.ExportTable ("C:\TEMP\Report.xls")

      End If

      End If

      MonarchObj.Exit

      End Sub

      /code

       

      The particular line that falters is towards the bottom "MonarchObj.ExportTable ("C:\TEMP\Report.xls")". I look forward to any ideas or input. Thank you very much for taking the time to help me out.

        • Export table as .xls for old version Monarch
          Data Kruncher

          Hello and welcome to the forum starcraftbud! Great first post.

           

          According to the [URL="http://www.datawatch.com/downloads/Monarch_8_Programmers_Guide.pdf"]Monarch Programmer's Guide[/URL], the problem isn't your models, or your code, per se, but in what the .ExportTable method actually does.

           

          ExportTable performs an export from the Table window using the Monarch V3 engine.

           

          In order to use newer file formats supported by later versions of Monarch, use JetExportTable (Professional version only, V6 or greater).[/QUOTE]

           

          If you have v6 Pro, then great, change to use .JetExportTable and you're in business.

           

          Otherwise a change in direction might be necessary.

           

          I ran into similar issues when using Monarch v5 Standard with Excel 2003 when I first became a member of this forum almost eight years ago (holy moley... :eek:).

           

          IIRC, my workaround involved exporting to CSV files and then importing those files into Excel, steps that I incorporated into my VBA code such as you're doing.