4 Replies Latest reply: May 15, 2014 9:56 AM by Nick Osdale-Popa RSS

    VBA Code - Export Multipls Models to MS Access

    osuapril _

      I have the code to automatically export a summary to MS Access.  However now I want to open a model, export summary then open another model, export that summary then close monarch.  The second model is not exporting, I belive because I need to add code to "close model" before opening next.  See code below.

       

      CODE

       

      Private Sub Form_Open(Cancel As Integer)

          SendData

          SendCorrected

      End Sub

       

       

      Function SendData()

      ' Import from open Monarch window summary into MS Access table

       

      On Error GoTo Err_cmd_JetExportTable_Click

       

      Dim CtrlKey As String

      Dim monarchobj As Object

      Dim openfile As Boolean

      Dim openmod As Boolean

      Dim t As Boolean

      Dim expfile As Boolean

      Dim stringTableName As String

       

      MsgBox "You will now send data to the Table: AMR91442_Data & AMR91442_CorrectedData in the AMR91442 Database."

       

      IsMonarchActive

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

       

      t = monarchobj.setlogfile("C:TempAMR91442.log", False)

       

      'open Monarch module

      openmod = monarchobj.SetModelFile("pathAMR91442.mod")

       

      monarchobj.CurrentSummary = "Data"

       

      If monarchobj.CurrentSummary <> "Data" Then

      End If

       

      'Overwrite Table

      expfile = monarchobj.JetExportSummary("pathImportData.mdb", "AMR91442_Data", 0)

       

      Exit_cmd_JetExportTable_Click:

      Exit Function

       

      Err_cmd_JetExportTable_Click:

      'MsgBox Err.Description

       

      End Function

       

       

      Function SendCorrected()

       

      On Error GoTo Err_cmd_JetExportTable_Click

       

      Dim CtrlKey As String

      Dim monarch_obj As Object

      Dim openfile As Boolean

      Dim openmod As Boolean

      Dim a As Boolean

      Dim expfile As Boolean

      Dim stringTableName As String

       

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

       

      a = monarch_obj.setlogfile("C:TempAMR91442_Corrected.log", False)

       

      'open Monarch module

      openmod = monarch_obj.SetModelFile("pathAMR91442_CorrectedData.mod")

       

      monarch_obj.CurrentSummary = "Corrected"

       

      If monarch_obj.CurrentSummary <> "Corrected" Then

      End If

       

      'Overwrite Table

      expfile = monarch_obj.JetExportSummary("pathImportData.mdb", "AMR91442_CorrectedData", 0)

       

      monarch_obj.CloseAllDocuments

      monarch_obj.Exit

       

      MsgBox "You're done."

       

      Exit_cmd_JetExportTable_Click:

      Exit Function

       

      Err_cmd_JetExportTable_Click:

      'MsgBox Err.Description

       

      DoCmd.Maximize

      DoCmd.Quit

      End Function

       

      [size="1"][ July 23, 2007, 04:59 PM: Message edited by: osuapril ][/size]

        • VBA Code - Export Multipls Models to MS Access
          RalphB _

          Hi,

           

          If I'm reading your code right and I'm assuming you have previously have the report opened already in Monarch, you have a "CloseAllDocuments" command in the first function. 

           

          When you call the second function, Monarch cannot see the report/document so it cannot apply a model or export anything out.

           

          The CloseAllDocuments command closes all open reports/databases that are open in Monarch and once closed, Monarch can no longer work with it until it is opened again with an OpenDatabase or SetReportFile command.

           

          Try removing that line in the first function and things should work properly or open the file again.

           

          HTH

           

          Ralph

          • VBA Code - Export Multipls Models to MS Access
            osuapril _

            I noticed that too and removed from the 1st function that runs,

             

            monarchobj.CloseAllDocuments

            monarchobj.Exit

             

            however it is still closing the monarch report.

            • VBA Code - Export Multipls Models to MS Access
              Nick Osdale-Popa

              In both routines, you have the following steps:

              creating a monarch object

              applying a model

              exporting summary

               

              The first routine grabs a hold of Monarch and as soon as it finishes, it will automatically close Monarch as it is no longer in scope.

               

              Thus the second routine you are now creating a new instance of Monarch with no report, thus no output.

               

              Solution: In the first routine, after the export, just instruct monarch to open a new model (no need to close the other one as only 1 model can be opened at once) and export that summary.

               

              Post back with any more problems/questions.

               

              [size="1"][ July 23, 2007, 05:45 PM: Message edited by: Nick Osdale-Popa ][/size]

              • VBA Code - Export Multipls Models to MS Access
                Nick Osdale-Popa

                osuapril - Has your problem been resolved?