6 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Monarch / Excel

    Phil _

      I'm lacking help from my support guys and hopefully one of you can point me in the right direction.

       

      I've got 2 project files osc.prj + osl.prj  everyday we have to spool new updated reports and run the prj files and then export them to excel then using an excel macro to filter out seperate users info and print that users report.

       

      Is there anyway (easy to understand) to automate so that monarch will automatically export files to osc.xls and osl.xls so that all we have to do is run the macro in the excel file.

       

      Hope this makes sense  

        • Monarch / Excel
          Grant Perkins

          Phil,

           

          You don't mention which version of Monarch you have (V7 for example might offer some interesting options) or where the source files are coming from (Operating system of machine and is it on a network for example) but ...

           

          It certainly should be possible to write a script (or scripts) to pick the file, run the Monarch project(s) and export to xls.

           

          Have a look at Autoscript for example. I have used this to do what you seem to be describing (I am being cautious just in case there is something that could be a blocker). I have also, on occasion, started from the point of creating an ftp session to automatically grab a file from a remote Unix server ...

           

          The only tricky thing was to find a decent Windows NT (at the time) program scheduler when we wanted to automate the process fully. Could be done but nothing we found then ( a few years ago) have the frequency of update we were looking for.

           

          Plan A on the other hand would be to write some VB code for the Excel part of the process and get that to call Monarch from Excel. You may still have some issues about getting scheduling to work though. Also I get the impression from your comments that you don't have the support required to go down that route ...

           

          Let us know which of the above (or neither!)sounds interesting and let's take it from there.

           

          Grant

           

           

           

            Originally posted by Phil:

          I'm lacking help from my support guys and hopefully one of you can point me in the right direction.

           

          I've got 2 project files osc.prj + osl.prj  everyday we have to spool new updated reports and run the prj files and then export them to excel then using an excel macro to filter out seperate users info and print that users report.

           

          Is there anyway (easy to understand) to automate so that monarch will automatically export files to osc.xls and osl.xls so that all we have to do is run the macro in the excel file.

           

          Hope this makes sense        /b[/quote]

           

          [size="1"][ September 17, 2003, 05:41 AM: Message edited by: Grant Perkins ][/size]

          • Monarch / Excel
            Nick Osdale-Popa

            If you are comfortable with using Excel's VBA, you can instruct the macro you have now to run Monarch and export your files.

             

            Be sure to download the [url="http://www.datawatch.com/pdf/products/monarch/Monarch_6_Programmers_Guide.pdf"]Programmers Guide[/url] if you haven't already done so.  It has excellent examples of controlling Monarch with Visual Basic.

             

            If the downloaded files are already scheduled at a certain time, you can use Windows Task manager to schedule a run time of the Excel macro shortly after the download is completed.

            • Monarch / Excel
              Phil _

              Thanks for the replies - I'll try to answer some of the questions.

               

              Windows NT/2000 Teriminal Services

               

              Monarch Version 5.02 - unlikely at this stage to ge the update

               

              Excel 97 SR-2

               

              The original files are exported from our own internal software as prt files - this has to be done manually.

               

              Monarch's exe file is located on E:Program FilesMonarchprogram

               

              Excel exe file is located on E:Program FilesMirosoft OfficeOffice

               

              prt files (osl.prt & osc.prt) are saved to E:Program FilesMonarchReports

               

              mod files (osl.mod & osc.mod) are saved to

              E:Program FilesMonarchModels

               

              I can also use prj files which have been created.

               

              I'm confident with excel @ monarch and have created a few macros but using VB code is new to me and can't see our support staff helping me with this - any help with vb code (is this the Visual Basic Editor in excel)would be much appreciated

               

              Thanx in advance

              • Monarch / Excel
                Grant Perkins

                Phil,

                 

                I forgot to ask if the Excel file you export is a new stand alone file or whether it needs to be appended to the existing excel file (in both cases).

                 

                VB stuff is not my area of expertise but it is for some of the other guys so Iwill leave them to provide guidance.

                 

                However if you wanted to look at the Autoscript route the Monarch Help files and a quick search of the Forum for Monarch and the FAQ's would provide a lot of input.  (autoscript, "export excel" and schedule(r) pick up some interesting and useful information.)

                 

                If you go that way watch out for long file names and so on, which DOS will not like. However you can get around that by modifying a script command to the DOS file name format OR simply placing the entire command within quote marks.

                 

                If you already have projects available I think you are half way there.

                 

                I hope this helps a little.

                 

                Grant

                 

                 

                  Originally posted by Phil:

                Thanks for the replies - I'll try to answer some of the questions.

                 

                Windows NT/2000 Teriminal Services

                 

                Monarch Version 5.02 - unlikely at this stage to ge the update

                 

                Excel 97 SR-2

                 

                The original files are exported from our own internal software as prt files - this has to be done manually.

                 

                Monarch's exe file is located on E:Program FilesMonarchprogram

                 

                Excel exe file is located on E:Program FilesMirosoft OfficeOffice

                 

                prt files (osl.prt & osc.prt) are saved to E:Program FilesMonarchReports

                 

                mod files (osl.mod & osc.mod) are saved to

                E:Program FilesMonarchModels

                 

                I can also use prj files which have been created.

                 

                I'm confident with excel @ monarch and have created a few macros but using VB code is new to me and can't see our support staff helping me with this - any help with vb code (is this the Visual Basic Editor in excel)would be much appreciated

                 

                Thanx in advance /b[/quote]

                 

                [size="1"][ September 18, 2003, 07:03 AM: Message edited by: Grant Perkins ][/size]

                • Monarch / Excel
                  Phil _

                  Got the upgrade to  Monarch Pro V7

                   

                  Still hitting the brick wall with inhouse support!

                   

                  As previously stated we transfer our internal reports into prt files so that monarch can pick them up.  These files are named osc.prt and osl.prt

                   

                  I have created models and project files to pick up the relevant details from the reports and export these into excel.

                   

                  I have built a simple macro that picks up both reports and filters records to print reports per user.

                   

                  If anyone can show me how to create VBA code as creating "bat" file with autoscript failed (missing dll etc) I would be greatly appreciated.

                   

                  Cheers

                   

                  Phil

                  • Monarch / Excel
                    Grant Perkins

                    Originally posted by Phil:

                    Got the upgrade to  Monarch Pro V7

                     

                    Still hitting the brick wall with inhouse support!

                     

                     

                    If anyone can show me how to create VBA code as creating "bat" file with autoscript failed (missing dll etc) I would be greatly appreciated.

                     

                    /b[/quote]Phil,

                     

                    So the impossible becomes possible! Good upgrade.

                     

                    I still can't help you with VBA knowledge but  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000206#000000"]this link[/url] may be useful if you have not already seen it.

                     

                    Failing that, which dll is claimed to be missing from where?

                     

                    Grant

                     

                    [size="1"][ May 19, 2006, 12:28 PM: Message edited by: Todd Niemi ][/size]