1 Reply Latest reply: May 15, 2014 10:02 AM by RalphB _ RSS

    Exporting Tables With VB Script Commands

    RRLR _

      Hello I have been Writing scripts in Visual Basic to Export .txt files to .xls files.

      The Script I Use works it opens the file , opens the Model, and export it to the location I have chosen. 

       

      Now My Problem is that When I Export the file Manually the Format is Different, the excel file has a Greyed out Headers and the Columns are sized accordingly to the Data and Headers.

       

      Is there any bit of Code that I can add to my Script to allow for the same Look as when I Manually Export it.

       

      The Code I'm Using is based off the Programmer Guide for Monarch. Just with out the Filters.

       

      If anyone can help that Would be great.

       

      Private Sub Form_Load()

       

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

       

      openfile = MonarchObj.SetReportFile("
      BLAH\Blah\Blah\Blah.txt", False)

       

      If openfile = True Then

       

          openmod = MonarchObj.SetModelFile("
      BLAH\Blah\Blah\Blah.xmod")

           

           

               

              If openmod = True Then

      'Set filter for each frame and export to Excel

                                                                                      MonarchObj.ExportTable ("
      BLAH\Blah\Blah\Blah.xls")

       

                   

               

              End If

       

      End If

       

      MonarchObj.CloseAllDocuments

       

      t = MonarchObj.SetLogFile("C:\MonTemp\MPrg_G5.log", False)

       

      openfile = MonarchObj.SetReportFile("
      BLAH\Blah\Blah\Blah.txt", False)

       

      If openfile = True Then

       

          openmod = MonarchObj.SetModelFile("
      BLAH\Blah\Blah\Blah.xmod")

           

           

               

              If openmod = True Then

      'Set filter for each frame and export to Excel

                                                                                      MonarchObj.ExportTable ("
      BLAH\Blah\Blah\Blah.xls")

       

                   

               

              End If

       

      End If

       

      MonarchObj.CloseAllDocuments

       

      MonarchObj.Exit

       

      End Sub[/CODE]

        • Exporting Tables With VB Script Commands
          RalphB _

          Hi and welcome to the forum.

           

          You don't say which version of Monarch you are using or if it is the Standard or Pro Version.  If you are using a Pro Version, use the JetExportTable or the JetExportSummary instead of the ExportTable command.  The JetExport command is limited to the Pro versions only.

           

          the JetExportTable command applies excel formatting to your spreadsheet and also you can append to an existing sheet and you can add sheets.

           

          Try that and let us know how you make out.