3 Replies Latest reply: May 15, 2014 10:07 AM by Olly Bond RSS

    Monarch data verification

    scrappy1855 _

      I am trying to figure out if there is a way via VBscript how to open a report, open a model or project file, set a filter, and then check to see if any data has been trapped, if data is trapped then export using the project file export settings, but if no data is trapped then close all documents and exit the object. Here is the code i have so far which will correctly open the project file but i'm not sure if its a possibility to check to see if any data is present before exporting. 

      Dim MonarchObj

      Dim openfile, openmod, report, openreport

      Dim reportname

       

      ' STARTS ACTUAL SCRIPT

       

       

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

          If MonarchObj Is Nothing Then

      Set MonarchObj = CreateObject("Monarch32")

          End If

              reportname = "c:\reportpath\reportname.prn"

                    With MonarchObj

                  openreport = MonarchObj.SetReportFile(reportname, True)

                      If openreport = True Then

                      .SetProjectFile("c:\reportpath\projectfile.xprj")

                      End If

           ' RIGHT HERE TRYING TO VERIFY DATA BEFORE EXPORT

                ' VERIFY DATA

                'IF DATA EXIST THEN EXPORT DATA

                    'IF NOT THEN EXIT       

                      .CloseAllDocuments

                      .Exit

                  End With      

                  MonarchObj.CloseAllDocuments

      MonarchObj.Exit   /CODE

        • Monarch data verification
          Olly Bond

          Hello Scrappy,

           

          There's two ways to attack this - firstly, this functionality is out of the box in Monarch DataPump, so if you have the server & budget available, I'd recommend that.

           

          But I think there's a DIY way around it using VB script - if you have two projects, the first opens the report, opens the model, has a summary called "Check" that has Recno() as a hidden key, and Count as a measure, and displays a total. Export that to a text file, then use VB script to open that file. If the last field is numeric and >0, then it's pretty safe to say that the report works, and your script can then carry on with the original project.

           

          HTH,

           

          Olly

            • Monarch data verification
              scrappy1855 _

              I'm not seeing Recno() can you point out where that is? This is a good idea, i hadn't even thought of.

                • Monarch data verification
                  Olly Bond

                  Hello Scrappy,

                   

                  Sorry, I jumped ahead of myself. Monarch has ton of useful functions (among them Page(), Line(), TextLine(), Rowno(), Recno(), File()...) which help you use the location of the data in the report to give context in the table window.

                   

                  You can't call them directly in Summaries - you need to define a calculated field say in the table window, with expression Recno(), then use as your key in the summary.

                   

                  For your challenge, you might want to look at the Text export options to make sure that the text file doesn't have the data wrapped in "quotes", in case that causes problems with the script's efforts to check the numeric value.

                   

                  Good luck,

                   

                  Olly