1 Reply Latest reply: May 15, 2014 9:51 AM by Nick Osdale-Popa RSS

    Another way to open all files in a folder

    Data Kruncher

      All,

       

      Thought I'd pass something along to those among us who program Monarch with VBA or VBS.

       

      Last week I built a new reporting system that, for ease of use, involved multiple folders for dedicated purposes (a folder for each topic). I stored four years worth of monthly reports in each folder. Always looking for the easiest ways and because of changing key fields, I determined the easiest way to update my final database tables each new period going forward would be to replace the entire table, rather than adding data for one more period.

       

      Of course, this would require opening all the reports in each folder each period. So I thought to make it easier I'd automate it. A search of the forum comes up with examples similar to [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000517"]Mark Huston's perfectly useful code[/url]. Very nice, but I thought there must be some simpler way.

       

      I turned to a Windows Scripting program I use to assist in downloading dozens of reports each week from our Oracle system.  [url="http://www.autoitscript.com/autoit3/"]Autoit[/url] is a free[/b] program that (with a little effort up front) lets you automate pretty much anything you can do in Windows.

       

      I came up with the following bit of code:

      [font="courier"];start Monarch

      run ("C:Program FilesMonarch Pro V8ProgramMonarch.exe")

       

      ;make sure we're controlling it

      winactivate ("Monarch Pro")

       

      ;wait for the splash screen

      sleep (6000)

       

      ;good, open the files Ctrl-o

      send("^o")

       

      ;point it to the right folder

      send("H:dataDatabasesOracle Reports and Monarch ModelsAR") ;Shift Tab to get to the file list send("+") ;select all of them! send("+")

       

      ;wait for them to open

      sleep(3000)

       

      ;open the model Ctrl-o

      send("^o")

       

      ;force the right folder

      send("H:dataDatabasesOracle Reports and Monarch ModelsAR") ;Shift Tab to get to the file list send("+") ;there's only one model in the folder, so select it send("")

      sleep(3000)

       

      ;go to the Summary Window

      send("WS")

        /font[/quote]Not too many lines of actual code, easy to customize, and works nicely.

       

      Just another option.

       

      Kruncher

        • Another way to open all files in a folder
          Nick Osdale-Popa

          Thanks for the info, Data Kruncher!

           

          Just a word of caution, this routine is sending keys to the active window, not necessarily Monarch. So it's best to make sure that you do not run other programs when this is running, and hope other windows do not pop up during execution. If that happens, all those keystrokes will be sent to that window and not Monarch.

           

          AutoIt is a great little program. I've been using it for years. You can even "compile" the scripts to executibles so that they can run on other computers without AutoIt. Also, the executibles can be "de-compiled" if you ever lose the original script file.