9 Replies Latest reply: May 15, 2014 9:57 AM by Praxair Paul RSS

    Programming techniques???

    Melissa _

      Does anyone out there use Monarch programming?  We use OLE and VBA in Excel to download reports from our main frame or optical system, open them in a model in Monarch, export the data, and then open excel files which contain formulas tied to the exported data from Monarch. It really saves alot of time and is pretty accurate (beats keying in the data by hand).  Our major problem was that we wanted to export several summaries at once to the same spreadsheet but I guess you don't get that "goodie" until version 6? Are there any others who have used automation and would like to share their knowledge.  I would really appreciate it.  Trial and error can be time consuming <g>.  So far I haven't figured out how to use automation with PRF files though, only text files.

        • Programming techniques???
          randy _

          Melissa,

           

          Sounds like you'll need to dump all the summaries to seperate workbooks.  If you record a macro where you open the source workbooks and copy all the worksheets individually to your target workbook you'll be well on your way.  You may get better results if you set the recording type to "relative" and fix it up with the CurrentRegion range property.  If you know the number of Summaries you will be exporting you can hardcode temp filenames, copy each into your target and kill them when done.

           

          Not very elegant, but sometimes ROI is more important.  It's a lot easier than some sort of screen scraping.   

           

          Good luck,

          Randy

          • Programming techniques???
            Mandrake _

            I have done some pretty extensive automations also, and I've found that Access is more capable than Excel for some of the more advanced operations.  I use Monarch (V5Pro) to export each summary or table into its own .MDB "holding" file, which are then pulled into the main database via links, with queries moving the data into more permanent tables.

             

            From an Excel-only standpoint, you might be able to accomplish your goal using VBA and Microsoft Query.  Export each of your summaries into its own spreadsheet.  Create a "master" spreadsheet with a macro or few to run queries that import (Get External Data) the individual summary spreadsheets in the fashion of:

                goto sheet1

                runqry import_summary1

                goto sheet2

                runqry import_summary2

                ...

            Your success will likely vary depending on the complexities of the summaries you are working with.

            • Programming techniques???
              Gareth Horton

              Melissa

               

              If you are using automation, Monarch 6 Pro gives you some powerful new functionality in that you can support the latest Excel and Access versions.  All other versions of Monarch use the internal export engine, which cannot export to Access.  The internal engine exports to Excel 2.1.

               

              The new methods JetExportTable and JetExportSummary allow you to specify a table name within a file and also whether you wish to create, append or overwrite.

               

              The new methods also enable the Excel formatting feature for OLE automation.

               

              You can combine these features with the  SummaryCount property, GetSummaryNameAt method and CurrentSummary property to loop through all the summaries in the model and export them.

              • Programming techniques???
                Mandrake _

                Originally posted by Gareth Horton:

                 

                ...Monarch 6 Pro gives you some powerful new functionality ... All other versions of Monarch use the internal export engine, which cannot[/i] export to Access[/b].[/quote]I've been using Monarch to export to Access 2000 (exporting as Access 4.0) for quite some time - a practice that has helped automate quite a few processes.  So I'm a little confused with your statement, since it contradicts my actual experience.  :confused:

                • Programming techniques???
                  Gareth Horton

                  Originally posted by Mandrake:

                  /size[quote]quote:[/size]Originally posted by Gareth Horton:

                   

                  ...Monarch 6 Pro gives you some powerful new functionality ... All other versions of Monarch use the internal export engine, which cannot[/i] export to Access[/b].[/quote]I've been using Monarch to export to Access 2000 (exporting as Access 4.0) for quite some time - a practice that has helped automate quite a few processes.  So I'm a little confused with your statement, since it contradicts my actual experience.   :confused: /b[/size][/QUOTE]Sorry, my mistake. 

                   

                  The latest Access was supported, Excel was not.

                   

                  There was no facility to create, append or overwrite specific tables before 6 Pro however.

                  • Programming techniques???
                    Mandrake _

                    "There was no facility to create, append or overwrite specific tables before 6 Pro however."[/i] - Gareth

                     

                    A little clarification please?  With V5.02 Pro when I do a manual export into Access I can specify to create a new table, replace an existing table, or append to an existing table.  It is when I do a command-line export that I have no control and the entire database is over-written and a new table (named for the filter) is created.

                     

                    Does your comment suggest that Version 6 will enable command-line exports, or other form of automated export, to selectively export to an existing database?  If so, this feature alone could justify my asking our IT manager to order V6 tomorrow. 

                    • Programming techniques???
                      Gareth Horton

                      Originally posted by Mandrake:

                      [i]"There was no facility to create, append or overwrite specific tables before 6 Pro however."[/i] - Gareth

                       

                      A little clarification please?  With V5.02 Pro when I do a manual export into Access I can specify to create a new table, replace an existing table, or append to an existing table.  It is when I do a command-line export that I have no control and the entire database is over-written and a new table (named for the filter) is created.

                       

                      Does your comment suggest that Version 6 will enable command-line exports, or other form of automated export, to selectively export to an existing database?  If so, this feature alone could justify my asking our IT manager to order V6 tomorrow.   /b[/quote]Yes indeed, Monarch 6 Pro allows a pair of new OLE methods:

                      JetExportSummary(stringExportFile,stringTableName,integerAppendFlag)

                       

                      JetExportSummary causes the data to be exported from the Summary window and written to the file ExportFile, to the table specified by TableName.

                       

                      JetExportTable(stringExportFile,stringTableName,integerAppendFlag)

                       

                      JetExportTable causes the data to be exported from the Summary window and written to the file ExportFile, to the table specified by TableName.

                       

                      Valid AppendFlag values are 0 for overwrite, 1 for new table or sheet and 2 for append

                      to existing table or sheet.

                       

                      For some formats, it is not possible to specify a table or sheet name and the TableName will be ignored. Additionally for these cases, multi table or sheet options are not allowed.

                       

                      The export file version is defined by the settings in the Monarch Options under Folders

                      & File Types and will apply to the extension specified as part of ExportFile.

                       

                      The command line export facilities have only changed in that the file format is now derived from the settings in "Folders & File Types", giving the latest formats and Excel formatting capability.  No facility to create, append and overwrite at the table level is possible.

                       

                      You can download the programmers guide for Monarch 6   [url="http://www.datawatch.com/pdf/products/monarch/Monarch_6_Programmers_Guide.pdf"]here[/url]

                       

                      If you have not used COM/OLE before, you will find a wealth of information on automating via OLE Monarch here on the messageboard by doing a search on OLE, automation, vb.

                       

                      I also have some examples in Excel written a long time ago for a user conference track that may be useful to you.

                       

                      Just let me know and I can mail them to you.

                       

                      Best regards

                      • Programming techniques???
                        Gareth Horton

                        Just to clarify,

                         

                        The new OLE methods are not available in the Standard edition.

                        • Programming techniques???
                          Praxair Paul

                          Originally posted by Gareth Horton:

                          /size[quote]quote:[/size]Originally posted by Mandrake:

                            /size[quote]quote:[/size]Originally posted by Gareth Horton:

                           

                          ...Monarch 6 Pro gives you some powerful new functionality ... All other versions of Monarch use the internal export engine, which cannot[/i] export to Access[/b].[/quote]I've been using Monarch to export to Access 2000 (exporting as Access 4.0) for quite some time - a practice that has helped automate quite a few processes.  So I'm a little confused with your statement, since it contradicts my actual experience.    :confused:  /b[/size][/QUOTE]Sorry, my mistake. 

                           

                          The latest Access was supported, Excel was not.

                           

                          There was no facility to create, append or overwrite specific tables before 6 Pro however.[/b][/size][/QUOTE]:eek:  This is FANTASTIC.............. I've been waiting for Monarch to add this functionality ever since Monarch 3. To put things in persoective, I have one application where I currently export 21 tables into 21 separate Access files. With this improvement I can now export all 21 to a single EXISTING MDB. The time saving alone will justify upgrading to Monarch Version 6.0.

                           

                          Thank you!!

                           

                          Paul