11 Replies Latest reply: May 15, 2014 9:59 AM by Soylent Green RSS

    Automating with reports already open?

    Soylent Green

      I am almost totally new to scripting and batch files so go easy on me...  All of the examples I have seen so far seem to start by opening Monarch and then doing something.  What if I already have Monarch open and the reports I want to manipulate open?  Here is what I want to do:

       

      AFTER simultaneously opening between 1 and 256 reports in the same instance of Monarch Pro 7, I want to automate the opening of 10 different models and export each summary view, one at a time, to 10 different excel files.  What would a .BAT file look like for this?  Is this relatively easy and something that someone could post?

        • Automating with reports already open?
          Data Kruncher

          Hi Soylent and welcome!

           

          You've picked quite the topic for your first post and first attempt at scripting!    

           

          If I've understood your requirements properly, here's some of the code you need:

           

          [font="courier"]set objMonarch = GetObject("","Monarch32")

          if objMonarch is Nothing then

             msgbox "Monarch not found! Can't control Monarch."

          else

             OpenModelFile = objMonarch.SetModelFile("Z:MyFolderModel One.xmod")

             objMonarch.CurrentSummary = "First Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderFirst Summary Export - First Model.xls","First",0)

             objMonarch.CurrentSummary = "Second Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderSecond Summary Export - First Model.xls","Second",0)

             objMonarch.CurrentSummary = "Third Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderThird Summary Exports - First Model.xls","Third",0)

             'continues on for 10 summaries

           

             'use the second model  

             OpenModelFile = objMonarch.SetModelFile("Z:MyFolderModel Two.xmod")

             objMonarch.CurrentSummary = "First Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderFirst Summary Export - Second Model.xls","First",0)

             objMonarch.CurrentSummary = "Second Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderSecond Summary Export - Second Model.xls","Second",0)

             objMonarch.CurrentSummary = "Third Summary Name"

             objMonarch.JetExportSummary ("Z:MyFolderThird Summary Exports - Second Model.xls","Third",0)

             'continues on for 10 summaries

           

             'continues on for 10 models

           

          end if

           

          objMonarch.CloseAllDocuments

          objMonarch.Exit[/font][/quote]Clearly you'll need to replace my examples with your filenames and summary names.

           

          You'll need to duplicate the code for all of the work you want to perform.

           

          Copy the code to Notepad, do the edits and save the file to your desktop with a ".vbs" extenstion in the filename. Then you can just double-click the icon on the desktop to run the exports.

           

          Be sure to download and review  [url="http://www.datawatch.com/pdf/products/monarch/Monarch_8_Programmers_Guide.pdf"]the programmers guide[/url] to understand what the different parameters are for the JetExportSummary command, and the commands in general.

           

          Does this point you in the right direction?

           

          Kruncher

          • Automating with reports already open?
            Soylent Green

            Aha!  Thank you!  Ok, I think I understand, but I will have to change what you wrote a little bit.  I probably worded my question awkwardly, but I want to:

            open the 1st model, export the summary to file 1

            open the 2nd model, export the summary to file 2

            open the 3rd model, export the summary to file 3

            ...and so on through model 10 and export 10.

             

            Would it look like this?

            [font="courier"]set objMonarch = GetObject("","Monarch32")

            if objMonarch is Nothing then

               msgbox "Monarch not found! Can't control Monarch."

            else

               OpenModelFile = objMonarch.SetModelFile("Z:MyFolderModel One.xmod")

               objMonarch.CurrentSummary = "First Summary Name"

               objMonarch.JetExportSummary ("Z:MyFolderFirst Summary Export - First Model.xls","First",0)

               OpenModelFile = objMonarch.SetModelFile("Z:MyFolderModel Two.xmod")  

               objMonarch.CurrentSummary = "Second Summary Name"

               objMonarch.JetExportSummary ("Z:MyFolderSecond Summary Export - Second Model.xls","Second",0)

               OpenModelFile = objMonarch.SetModelFile("Z:MyFolderModel Three.xmod")  

               objMonarch.CurrentSummary = "Third Summary Name"

               objMonarch.JetExportSummary ("Z:MyFolderThird Summary Exports - Third Model.xls","Third",0)

               'continues on for 10 models and summaries

            end if

            objMonarch.CloseAllDocuments

            objMonarch.Exit[/font][/quote]Plus, I do not want to close all documents and exit at the end just in case I need to tweak something.  (It takes too long to open all the reports again.)  How would I end it?  Just remove

            [font="courier"]objMonarch.CloseAllDocuments

            objMonarch.Exit[/font][/quote]?

            • Automating with reports already open?
              Soylent Green

              Shoudn't I also set Monarch to equal nothing at the end?  If so, does that look like what is below?

              [font="courier"]set objMonarch = Nothing[/font][/quote]

              • Automating with reports already open?
                Grant Perkins

                Soylent,

                 

                I am somewhat intrigued by this in that I cannot think of any previous circumstances I have come across which would require 10 different models acting on the same data records, whether the data is coming from a report or an less structured character file.

                 

                In the spirit of learning are you able to outline for us what sort of data inputs theses reports are and what it is about them that leads you to apply so many different models?

                 

                Thanks in advance.

                 

                 

                Grant

                • Automating with reports already open?
                  Soylent Green

                  Grant,

                   

                  They are boxscore files in HTML format from football(US) games.  I am using Monarch to extract 10 different statistical categories from each and summarize over an entire season or season-to-date.

                   

                  I can open all of the files that I have for a particular season to date and see the season-to-date summary of stats for each player in each category depending on which model I use.  I export these into an excel file that I have set up to format a few things and to compile a little bit more info before I create my own HTML summary files.

                   

                  Also, one of these models pulls the game scores and preps them so that when I export to an excel template it automatically creates the standings for the entire league.

                   

                  I now have a script that saves me having to go through all the mouse clicks to open the models and export the files (thanks to the posts above), but I cannot figure out how to leave Monarch and all the files open at the end of the script.

                  • Automating with reports already open?
                    Grant Perkins

                    Originally posted by Soylent Green:

                    Grant,

                     

                    They are boxscore files in HTML format from football(US) games.  I /b[/quote]Ah, OK.

                     

                    My experiences with HTML files do indeed suggest that the route I would hope to go down for this sort of analysis - one model, one table, lots of summaries with filters - can be severely challenged if the HTML file has a lot of different data fields. So I suspect you are using the most practical approach.

                     

                    I just love the way Monarch can be used so effectively for so many different purposes.

                     

                    smile.gif[/img]

                     

                     

                    Thanks for sharing the information.

                     

                     

                    Grant

                    • Automating with reports already open?
                      Soylent Green

                      You have me thinking though...  I may be able to do it all in one model with different summaries and filters if I set up my traps differently.  I set this up a couple of years ago and I keep seeing more efficient ways to do it and I keep modifying accordingly. 

                       

                      But, right now I know everything works properly, the data ties out, and numerous exception items have been incorporated, so I will probably leave it as is unless I get a ton of free time somehow...

                      • Automating with reports already open?
                        Data Kruncher

                        Soylent,

                         

                        Sorry for not getting back sooner - taking a long weekend with the family.

                         

                        I don't have the time to test something for you properly, but I *think* that if you add this line before the .CloseAllDocuments, you will be able to make manual tweaks before shutting down Monarch.

                         

                        [font="courier"]MsgBox "Click OK to close Monarch when you are ready."  /font[/quote]A better approach might be to incorporate something like the IsServerActive example routine from the programmers manual.

                         

                        But again, here's the big disclaimer, I haven't tried this out myself yet.

                         

                        I'm traveling on business next week and will try to check in on your progress. Must get back to the family now.

                         

                        HTH

                         

                        Kruncher

                        • Automating with reports already open?
                          Nick Osdale-Popa

                          There is an example in the [url="http://www.datawatch.com/pdf/products/monarch/Monarch_8_Programmers_Guide.pdf"]Programmer's Guide[/url] that allows for interactivity.  Here's the somewhat modified code I use:

                           

                          [font="courier"]Option Explicit

                          Dim oMonarch As Object

                           

                          Sub Main()

                              InterActiveMonarch

                              Do While IsMonarchActive()

                                  DoEvents

                              Loop

                              On Error Resume Next

                              'Clean up the Monarch Object

                              oMonarch.CloseAllDocuments

                              oMonarch.Exit

                               

                              Set oMonarch = Nothing

                              MsgBox "Monarch Interactive mode ended."

                          End Sub

                           

                           

                          Sub InterActiveMonarch()

                              Dim bOpenReport As Boolean

                              Dim bOpenModel As Boolean

                              Dim bServerActive As Boolean

                               

                              bServerActive = IsMonarchActive()

                              If bServerActive = False Then

                                  Set oMonarch = CreateObject("Monarch32")

                              End If

                               

                              With oMonarch

                                   

                                  'Replace the following reports & models with your files

                                       

                                  bOpenReport = .SetReportFile("C:Program FilesMonarchReportsclassic.prn", False)

                                  If bOpenReport = True Then

                                      bOpenModel = .SetModelFile("C:Program FilesMonarchModelsLesson12.xmod")

                                  End If

                              End With

                          End Sub

                           

                          Function IsMonarchActive() As Boolean

                              Dim bActive As Boolean

                              On Error GoTo MonarchInActive

                              If oMonarch.IsActive > 0 Then

                                  bActive = True

                              End If

                           

                          Exit_IsMonarchActive:

                              IsMonarchActive = bActive

                              Exit Function

                           

                          MonarchInActive:

                              bActive = False

                              Resume Exit_IsMonarchActive

                          End Function[/font][/quote]

                          • Automating with reports already open?
                            Nick Osdale-Popa

                            I just re-read your requirements: You want to run the script on a currently opened session of Monarch with reports and you want to add your ten models to those reports. The Code would be adjusted as so:

                            [font="courier"]Option Explicit

                            Dim oMonarch As Object

                             

                            Sub Main()

                                Dim bServerActive As Boolean

                                 

                                bServerActive = IsMonarchActive()

                                If bServerActive = False Then

                                    Set oMonarch = CreateObject("Monarch32")

                                End If

                                 

                                OpenMonarchModels

                                 

                                Do While IsMonarchActive()

                                    DoEvents

                                Loop

                                On Error Resume Next

                                 

                                oMonarch.CloseAllDocuments

                                oMonarch.Exit

                                 

                                Set oMonarch = Nothing

                                MsgBox "Monarch Finished!"

                            End Sub

                             

                            Sub OpenMonarchModels()

                                Dim intIndex As Integer

                                Dim intCount As Integer

                                Dim astrModels(1 To 10) As String

                                Dim astrSummaries(1 To 10) As String

                                Dim astrExports(1 To 10) As String

                                Dim bOpenModel As Boolean

                                Dim bRet As Boolean

                                 

                                astrModels(1) = "Model One.xmod"

                                astrModels(2) = "Model Two.xmod"

                                'All ten models

                                 

                                astrSummaries(1) = "First Summary Name"

                                astrSummaries(2) = "Second Summary Name"

                                'All Ten Summaries

                                 

                                astrExports(1, 1) = "First Summary Export - First Model.xls"

                                astrExports(1, 2) = "First"

                                astrExports(2, 1) = "Second Summary Export - Second Model.xls"

                                astrExports(2, 2) = "Second"

                                'All ten Files

                                 

                                intCount = UBound(astrModels)

                                 

                                With oMonarch

                                    For intIndex = 1 To intCount

                                     

                                        bOpenModel = oMonarch.SetModelFile("Z:MyFolder" & astrModels(intIndex))

                                        If bOpenModel = True Then

                                            oMonarch.CurrentSummary = astrSummaries(intIndex)

                                            bRet = oMonarch.JetExportSummary("Z:MyFolder" & astrExports(intIndex, 1), astrExports(intIndex, 2), 0)

                                        End If

                                         

                                    Next intIndex

                                End With

                            End Sub

                             

                             

                            Function IsMonarchActive() As Boolean

                                Dim bActive As Boolean

                                On Error GoTo MonarchInActive

                                If oMonarch.IsActive > 0 Then

                                    bActive = True

                                End If

                             

                            Exit_IsMonarchActive:

                                IsMonarchActive = bActive

                                Exit Function

                             

                            MonarchInActive:

                                bActive = False

                                Resume Exit_IsMonarchActive

                            End Function[/font][/quote]You could even Read the Models, Summaries, Exported Files and Sheet names from a worksheet rather than having to update the code if you need to add more models.

                            • Automating with reports already open?
                              Soylent Green

                              I will have to try some of these out.  I think it's time I got some sort of VBS for dummies book...