2 Replies Latest reply: May 15, 2014 10:11 AM by jslatt _ RSS

    Need help to keep Monarch from closing via VBA

    jslatt _

      I use an Access database to send user selected files and Models to Monarch with the below VBA function.  Since Monarch is located on a remote server there is a lengthy delay to launch each instance of Monarch.  Some of the options allow the user to send an entire folder of files and a model to Monarch.  My goal was then to have a counter and only exit the object when all the files had been processed in order to reduce lag time.  I can post the code that calls this function if that would be helpful, but mostly I could use any advice to make this work better and keep Monarch from closing.

       

      Public Function OpenMon(strFile As String, blType As Boolean, Optional strMod As String, Optional strFileResult As String, Optional strTable As String, Optional lngCount As Long)

      On Error GoTo Err_OpenMon

       

       

      Dim MonarchObj As Object

       

      Dim OpenFile, openmod, t As Boolean

       

           

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

       

          If MonarchObj Is Nothing Then

       

              Set MonarchObj = CreateObject("Monarch32")

           

          End If

           

          MonarchObj.Visible = False

       

      t = MonarchObj.SetLogFile(CurrentProject.path & "\Log\Monarch.log", False)

       

      OpenFile = MonarchObj.SetReportFile(strFile, False)

       

      If OpenFile = True Then

       

          openmod = MonarchObj.SetModelFile(strMod)

           

              If openmod = True Then

               

               

                  If blType = True Then

                   

                   

                      MonarchObj.JetExportTable strFileResult, strTable, 0

                       

                  Else

                   

                      MonarchObj.ExportTable strFileResult

                  End If

               

               

              End If

               

      End If

           

           

          MonarchObj.CloseAllDocuments

       

      If lngCount = 1 Or IsEmpty(lngCount) Then

          MonarchObj.Exit

      End If

       

      Exit_OpenMon:

          Exit Function

       

      Err_ OpenMon:

          Call LogError(Err.Number, Err.Description, "OpenMon")

          Resume Exit_OpenMon

      End Function

      /CODE

       

      Thanks in advance.

       

      Justin

        • Need help to keep Monarch from closing via VBA
          Nick Osdale-Popa

          In your current routine, you are creating a Monarch object, using it and then destroying it. In more general terms, you could create a separate routine to create a locally scoped Monarch object (thus opening it only once) and then create a routine that would use this object to open your files and models, whether it's a singe file/model or multiple. Once the files are processed, you would then close and destroy the Monarch object. This is more efficient as you only need to create the object once for all processes and close it when no longer needed, rather than opening/closing with each process.

           

          Hope that makes sense.

            • Need help to keep Monarch from closing via VBA
              jslatt _

              Thanks for your advice.  I believe I mostly understood.  I was having trouble with the locally scoped object.  I ended up using my same function but made the MonarchObj Static instead of Dim.  I then set it to Nothing at the end of my loop counter and it seems to work pretty well.  I thought I would post my final code in case it might help somebody else.

               

              Thanks again!

               

              Justin

               

              Public Function OpenMon(strFile As String, blType As Boolean, Optional strMod As String, Optional strFileResult As String, Optional strTable As String, Optional lngCount As Long)

              On Error GoTo Err_OpenMon

               

               

              Static MonarchObj As Object

               

              Dim OpenFile, openmod, t As Boolean

               

                   

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

               

                  If MonarchObj Is Nothing Then

               

                      Set MonarchObj = CreateObject("Monarch32")

                   

                  End If

                   

                  MonarchObj.Visible = True

               

               

               

              t = MonarchObj.SetLogFile(CurrentProject.path & "\Log\Monarch.log", False)

               

              OpenFile = MonarchObj.SetReportFile(strFile, False)

               

              If OpenFile = True Then

               

                  openmod = MonarchObj.SetModelFile(strMod)

                   

                      If openmod = True Then

                       

                       

                          If blType = True Then

                           

                           

                              MonarchObj.JetExportTable strFileResult, strTable, 0

                               

                          Else

                           

                              MonarchObj.ExportTable strFileResult

                          End If

                       

                       

                      End If

                       

              End If

                   

                 

                  MonarchObj.CloseAllDocuments

                  MonarchObj.Visible = False

               

               

              If lngCount = 1 Or IsEmpty(lngCount) Then

                  MonarchObj.Exit

                  Set MonarchObj = Nothing

              End If

               

              Exit_OpenMon:

                  Exit Function

               

              Err_OpenMon:

                  MsgBox Err.Number & " - " & Err.Description & vbCrLf & vbCrLf & "Error occurred during OpenMon function.", vbCritical, "Error!"

                  Resume Exit_OpenMon

              End Function[/CODE]