4 Replies Latest reply: May 15, 2014 9:59 AM by Tom Whiteside RSS

    Keeping Monarch Persistent  (OLE Automation)

    Nick Osdale-Popa

      Using Excel 97, I'm trying to open up some report files, applying a model, and then having Monarch display the Table view.  After which, I want the macro to exit, leaving me in Monarch. 


      Monarch opens the files and model without problem, however, after that it gives me this error:

      [font="courier"]Monarch found a problem exporting the file


      Automation error.

      The server threw an exception.[/font][/quote]Not sure why it's giving me this error.

      Here is the code I'm using:

      [font="courier"]Option Explicit

      Global colFiles As Collection

      Private ReportPath As String

      Private ModelPath As String

      Private objMonarch As Object

      Private Const IsOn = 1

      Private Const IsOff = 0

      Private Const wdwMaximize = 0

      Private Const wdwMinimize = 2




      Function ProcessMonarch() As Boolean

      On Error Resume Next

          Set objMonarch = CreateObject("Monarch32")

          On Error GoTo 0

          If Err.Number <> 0 Then ProcessMonarch = False


      End Function


      Function OpenMonarch()

          Const fOverwrite As Byte = 0

          Const fNewTable As Byte = 1

          Const fAppend As Byte = 2

          Const ReportErr As Byte = 1

          Const ModelErr As Byte = 2

          Const TableErr As Byte = 3

          Const NoSummaries As Byte = 4

          Const SummaryErr As Byte = 5

          Const sFirstView As String * 1 = "R"

          Const DefaultFilter As String * 3 = "ALL"

          Const WarningTitle As String * 46 = "Monarch Found a problem in Exporting The File."


          Dim OpenFile As Boolean

          Dim OpenModel As Boolean

          Dim IsExported As Boolean

          Dim SummaryCount As Integer

          Dim SummaryIdx As Integer

          Dim FilterCount As Integer

          Dim Filter As Integer

          Dim FileCount As Integer

          Dim FileIdx As Integer

          Dim sExportFile As String

          Dim sProblem As String

          Dim sReportFile As String

          Dim sModelFile As String

          Dim FilterName As String

          Dim SummaryName As String

          Dim FileExt As String

          Dim strSheet As String

          ReportPath = SOURCEDIR

          ModelPath = MODELDIR

          sModelFile = ModelPath & "" & ModelFile


      On Error GoTo Monarch_Error

          IsExported = True

          With objMonarch

              .Visible False

      '        .SetLogFile msExportPath & "" & "LOG.TXT", True

              .SetFirstView (sFirstView)


              'Open Report

              If Not colFiles Is Nothing Then


                  FileCount = colFiles.Count

                  For FileIdx = 1 To FileCount

                      sReportFile = colFiles(FileIdx)

                      sReportFile = ReportPath & "" & sReportFile

                      OpenFile = .SetReportFile(sReportFile, True)

                      'Raise Error if File Open failed

                      If OpenFile = False Then

                          IsExported = False

                          sProblem = "Error Opening File: " & sReportFile

                          Err.Raise vbObject + ReportErr, "Monarch", sProblem

                      End If

                  Next FileIdx

              End If


              'Raise Error if File Open failed

              If OpenFile = False Then

                  IsExported = False

                  sProblem = "Error Opening File: " & sReportFile

                  Err.Raise vbObject + ReportErr, "Monarch", sProblem

              End If


              'Open Model

              OpenModel = .SetModelFile(sModelFile)


              'Raise Error if Model Open failed

              If OpenModel = False Then

                  IsExported = False

                  sProblem = "Error with model: " & ModelFile

                  Err.Raise vbObject + ModelErr, "Monarch", sProblem

              End If

              .SetFirstView ("T")

          End With


          OpenMonarch = IsExported

          Exit Function



          With objMonarch

              .DisplayWindow (wdwMinimize)

              .Visible = False

          End With


          MsgBox Err.Description, vbCritical, WarningTitle

          Resume Exit_Open

      End Function[/font][/quote]

        • Keeping Monarch Persistent  (OLE Automation)
          Nick Osdale-Popa

          OK, I've corrected the error I was getting by turning the visible property to True in every instance.


          However, I still can't keep Monarch persistent in memory.  Once I exit Excel, Monarch quits also - which does make sense, since Excel is the calling app to Monarch instance, if it goes away, so does the instance of Monarch. 


          How do I circumvent that?

          • Keeping Monarch Persistent  (OLE Automation)
            Data Kruncher



            One solution may be to start Monarch via a Shell command, then using SendKeys to control it. Tedious and nowhere near as elegant as the object route, and you'll lose some functionality like error checking, but Monarch will stay open after Excel is closed.


            There may be a way to leave an object running after the calling app closes, but I haven't found it either. I would think that resource management issues would preclude leaving created objects open, so in all likelihood, it's impossible.


            What do you think?



            • Keeping Monarch Persistent  (OLE Automation)
              Gareth Horton



              This code should give you some pointers:


              [font="courier"] Private Sub Ex7_Click()


              'Example 7

              ' This example has 3 parts.  This is the first part which will call the

              ' Monarch_Launch sub.  The Monarch_Launch sub will call IsServerActive to

              ' see if the server is active.  If not, the Monarch_Launch will create the

              ' Monarch object, open the Report, and model.  These will stay open so the

              ' user can work within the program.  When the user closes Monarch the program

              ' will terminate.



                  Monarch_Launch                   'Call the Monarch_Launch sub

                  Do While IsServerActive()        'Keep checking to see if server is active

                      DoEvents                     'Allows you to do other tasks



                  'Clean up by closing all open documents


                  'Send Monarch the exit command


                  'Destroy the object - completing a nice clean exit procedure

                  Set Monarchobj = Nothing

              End Sub

              Sub Monarch_Launch()

              Dim openfile, openmod As Boolean

              Dim Setwin As Boolean

              Dim serveron, Winsize As Integer



                   'Check to see if the server is active (Monarch is open)

                   serveron = IsServerActive()


                   'If server is not active then create it

                   If serveron = 0 Then

                        Set Monarchobj = CreateObject("Monarch32")

                   End If


                   'Set Monarch to open in the table window

                   Setwin = Monarchobj.SetFirstView("T")


                  'Open the report and model file

                   openfile = Monarchobj.setreportfile("C:Program FilesMonarchReportsclassic.prn", False)


                   If openfile = True Then


                      openmod = Monarchobj.setmodelfile("C:Program FilesMonarchModelsclassicplus.mod")


                   End If





              End Sub

              Function IsServerActive()

                  On Error GoTo NoServer

                  If Monarchobj.IsActive > 0 Then     'Check to see if server is active

                      IsServerActive = 1

                  End If

                  Exit Function



                  IsServerActive = 0                  'Trap the error when the server is inactive

                  Exit Function

              End Function /font[/quote]Gareth

              • Keeping Monarch Persistent  (OLE Automation)
                Tom Whiteside

                (This is for Data Kruncher)




                Would you either open up your private messaging function, or, e-mail me at my address below?  I have a question about your posting.