17 Replies Latest reply: May 15, 2014 9:52 AM by somethinggood4 _ RSS

    Automate reports

    jonbhoy _

      Hi,  I am new to forum.

       

      I have been using monarch 7.0 for a while now. i.e. building models, exporting data to excel etc.  What i would like to do though is automate the process of opening several files and exporting them to the same excel worksheet using vba.  With the help of the programmers handbook i have managed to open up one file, then the model then export it to a workbook.  Where i have become stuck is trying to export the other files to the same worksheet without overwriting the previous data.  Below is the code i have been using

       

       

      openfil = MonarchObj.SetReportFile("C:Documents and SettingsjomcleanDesktopWeekly Files     oledo.txt", False)

       

      openmod = MonarchObj.SetModelFile("G:AP TeamMonarch11.03 debit balance TB.mod")

       

      MonarchObj.ExportTable ("C:Documents and SettingsjomcleanDesktopAP Debit Balance data.xls")

       

       

      Can anyone help?

        • Automate reports
          jonbhoy _

          Hi,  I am new to forum.

           

          I have been using monarch 7.0 for a while now. i.e. building models, exporting data to excel etc.  What i would like to do though is automate the process of opening several files and exporting them to the same excel worksheet using vba.  With the help of the programmers handbook i have managed to open up one file, then the model then export it to a workbook.  Where i have become stuck is trying to export the other files to the same worksheet without overwriting the previous data.  Below is the code i have been using

           

           

          openfil = MonarchObj.SetReportFile("C:Documents and SettingsjomcleanDesktopWeekly Files     oledo.txt", False)

           

          openmod = MonarchObj.SetModelFile("G:AP TeamMonarch11.03 debit balance TB.mod")

           

          MonarchObj.ExportTable ("C:Documents and SettingsjomcleanDesktopAP Debit Balance data.xls")

           

           

          Can anyone help?

          • Automate reports
            Data Kruncher

            Jonbhoy,

             

            Hello and welcome!

             

            You're right, if you're using the Standard version of Monarch 7, you're limited to the ExportTable method, which will always overwrite the destination file.

             

            The JetExportTable method, available in the Pro version only, offers you much more flexibility and the ability to append to existing files.

             

            But, since you're using VBA to control the process, all is not lost!

             

            You can code your program to export your data sets to individual files. Then open the workbook files individually, and copy the sheets form the exported files in question to a single master workbook.

             

            You'll need to make sure that the sheet names are unique before you copy them over to the master workbook, or the copy will fail.

             

            If you need help with this code, let us know, but I'm sure you'll be fine with it.

             

            Kruncher

            • Automate reports
              Data Kruncher

              Jonbhoy,

               

              Hello and welcome!

               

              You're right, if you're using the Standard version of Monarch 7, you're limited to the ExportTable method, which will always overwrite the destination file.

               

              The JetExportTable method, available in the Pro version only, offers you much more flexibility and the ability to append to existing files.

               

              But, since you're using VBA to control the process, all is not lost!

               

              You can code your program to export your data sets to individual files. Then open the workbook files individually, and copy the sheets form the exported files in question to a single master workbook.

               

              You'll need to make sure that the sheet names are unique before you copy them over to the master workbook, or the copy will fail.

               

              If you need help with this code, let us know, but I'm sure you'll be fine with it.

               

              Kruncher

              • Automate reports
                jonbhoy _

                Thanks for the welcome and quick reply Data Kruncher.

                 

                I will see our IT guy and see if we have or can get the pro version.  I have it setup so each file is exported seperately to an excel workbook like you suggested but i would like it to be more efficient.  But if i can't get the pro version then at least that's an option. 

                 

                Thanks again for your help.

                • Automate reports
                  jonbhoy _

                  Thanks for the welcome and quick reply Data Kruncher.

                   

                  I will see our IT guy and see if we have or can get the pro version.  I have it setup so each file is exported seperately to an excel workbook like you suggested but i would like it to be more efficient.  But if i can't get the pro version then at least that's an option. 

                   

                  Thanks again for your help.

                  • Automate reports
                    Grant Perkins

                    Originally posted by jonbhoy:

                    I will see our IT guy and see if we have or can get the pro version.   /b[/quote]Jon,

                     

                    If you need justification for an upgrade to the Pro version  - which would also mean moving up to Version 8 these days I suspect - it might be worth knowing that the Pro version supports Project Exports which, unless I have missed something, would probably do all you need for the exporting to a single worksheet OR separate worksheets within a workbook.

                     

                    There are a number of other significant benefits from both the Pro and the V8 components of an upgrade - if you need help for justify them just ask here and you will get many useful inputs.

                     

                    Is it safe to assume that you are using multiple versions of the same report? And all in the same pprocess at the same time in effect? In which case have you looked at opening all of the reports at the same time and exposting the results as a single export? Or would that not work for you for some reason?

                     

                    HTH.

                     

                    Grant

                    • Automate reports
                      Grant Perkins

                      Originally posted by jonbhoy:

                      I will see our IT guy and see if we have or can get the pro version.   /b[/quote]Jon,

                       

                      If you need justification for an upgrade to the Pro version  - which would also mean moving up to Version 8 these days I suspect - it might be worth knowing that the Pro version supports Project Exports which, unless I have missed something, would probably do all you need for the exporting to a single worksheet OR separate worksheets within a workbook.

                       

                      There are a number of other significant benefits from both the Pro and the V8 components of an upgrade - if you need help for justify them just ask here and you will get many useful inputs.

                       

                      Is it safe to assume that you are using multiple versions of the same report? And all in the same pprocess at the same time in effect? In which case have you looked at opening all of the reports at the same time and exposting the results as a single export? Or would that not work for you for some reason?

                       

                      HTH.

                       

                      Grant

                      • Automate reports
                        jonbhoy _

                        Hi Grant,

                         

                        Unfortunately we only have version 7 and there's no way our IT guy will upgrade to version 8, nevermind the pro version!  Every nickel is a prisoner with this guy! 

                         

                        Basically i have 15 reports that have the same data but are split by country, i.e. each report is for a different country.  I tried opening them all at once then exporting them as the one but this didn't work. 

                         

                        What i have done using VBA is run them through monarch one by one and extracting the data to a seperate excel workbook.  I then call another subroutine to bring them all together in the same workbook and carry out addional formulas. 

                         

                        It's not the most efficient way of doing it but with my hands tied on the Monarch version i have then it's the best i can do. 

                         

                        Thanks for your input Grant - it was greatly appreciated. 

                         

                        Jon

                        • Automate reports
                          jonbhoy _

                          Hi Grant,

                           

                          Unfortunately we only have version 7 and there's no way our IT guy will upgrade to version 8, nevermind the pro version!  Every nickel is a prisoner with this guy! 

                           

                          Basically i have 15 reports that have the same data but are split by country, i.e. each report is for a different country.  I tried opening them all at once then exporting them as the one but this didn't work. 

                           

                          What i have done using VBA is run them through monarch one by one and extracting the data to a seperate excel workbook.  I then call another subroutine to bring them all together in the same workbook and carry out addional formulas. 

                           

                          It's not the most efficient way of doing it but with my hands tied on the Monarch version i have then it's the best i can do. 

                           

                          Thanks for your input Grant - it was greatly appreciated. 

                           

                          Jon

                          • Automate reports
                            Grant Perkins

                            Jon,

                             

                            The price differential and upgrade costs would be no more than paying for you to do the work the long way I would guess. And the cost of continuing maintenance is likely to be lower as well.

                             

                            Oh well. Some people are ruled by by what a few of may see as illogical constraints.

                             

                            I am slightly surprised that you cannot open all of the reports, picking up the country code which I assume exists somewhere, and exporting them from there.

                             

                            What sort of problem did it give you?

                             

                             

                            Grant

                            • Automate reports
                              Grant Perkins

                              Jon,

                               

                              The price differential and upgrade costs would be no more than paying for you to do the work the long way I would guess. And the cost of continuing maintenance is likely to be lower as well.

                               

                              Oh well. Some people are ruled by by what a few of may see as illogical constraints.

                               

                              I am slightly surprised that you cannot open all of the reports, picking up the country code which I assume exists somewhere, and exporting them from there.

                               

                              What sort of problem did it give you?

                               

                               

                              Grant

                              • Automate reports
                                jonbhoy _

                                Some of us are ruled by constraints out with our control! 

                                 

                                Moving on though - Like Data Kruncher suggests, you need the pro version to be able to open all reports and export as the one file.  This is something you can do manually with the version i have but not via coding.  When i try it just exports the last file loaded into Monarch

                                 

                                The reports are text files taken from Oracle but they don't have a specific country code.  I name the files per country as i extract them from Oracle.

                                 

                                I have added a calculated field to the model i run the reports through that returns the file path and file name.  Using a formula that splits this field it only returns the name of the file i.e. country.  This is how i differentiate each country file when pulling all the reports together.

                                 

                                As i said previously - it's not the most efficient way to do it. But given that my report now takes between 40 and 50 seconds to run it'll do me nicely. 

                                 

                                 

                                When there's a will, there's a way!

                                 

                                Jon

                                • Automate reports
                                  jonbhoy _

                                  Some of us are ruled by constraints out with our control! 

                                   

                                  Moving on though - Like Data Kruncher suggests, you need the pro version to be able to open all reports and export as the one file.  This is something you can do manually with the version i have but not via coding.  When i try it just exports the last file loaded into Monarch

                                   

                                  The reports are text files taken from Oracle but they don't have a specific country code.  I name the files per country as i extract them from Oracle.

                                   

                                  I have added a calculated field to the model i run the reports through that returns the file path and file name.  Using a formula that splits this field it only returns the name of the file i.e. country.  This is how i differentiate each country file when pulling all the reports together.

                                   

                                  As i said previously - it's not the most efficient way to do it. But given that my report now takes between 40 and 50 seconds to run it'll do me nicely. 

                                   

                                   

                                  When there's a will, there's a way!

                                   

                                  Jon

                                  • Automate reports
                                    Grant Perkins

                                    Jon, thanks.

                                     

                                    Most interesting.

                                     

                                    Not having used a Standard version since the Pro differentiation was introduced waaaay back it can be difficult to be familiar with the differences between them.

                                     

                                    And I don't do automation (I don't have a requirement) beyond the odd script now and again so the subtleties of those differences also pass me by.

                                     

                                    Sounds like you have a good solution in place though, especially with the country identification problem. And that, after all, is what it is all about in the final analysis.

                                     

                                      :cool: 

                                     

                                     

                                    Grant

                                    • Automate reports
                                      Grant Perkins

                                      Jon, thanks.

                                       

                                      Most interesting.

                                       

                                      Not having used a Standard version since the Pro differentiation was introduced waaaay back it can be difficult to be familiar with the differences between them.

                                       

                                      And I don't do automation (I don't have a requirement) beyond the odd script now and again so the subtleties of those differences also pass me by.

                                       

                                      Sounds like you have a good solution in place though, especially with the country identification problem. And that, after all, is what it is all about in the final analysis.

                                       

                                        :cool: 

                                       

                                       

                                      Grant

                                      • Automate reports
                                        somethinggood4 _

                                        I realize this conversation is over seven months old, but it seemed reasonable to post here than to try to reinvent the wheel...

                                         

                                        I have the same problem - 15 reports and want to automate the extraction to Excel.  The bigger problem is that the reports are not in a standard format.  I've figured out a trap that will cover all the report data I need (N.NN), but I can't get the fields to work out across all reports.

                                         

                                        Is there any way I can program Monarch to Automatically open all the reports in a specific folder, in a specific order, then pull the lines that have amounts in them?  If I can get that, I ought to be able to map my existing spreadsheet to the values I export from Monarch. 

                                         

                                        Side note to Jonbhoy, Have you considered rather than cutting-and-pasting, mapping your "master sheet" to the individual sheets that are generated from the reports?  Keep the file names that Monarch generates as separate workbooks, but refer to the other workbooks in your master sheet. (Just a thought)

                                        • Automate reports
                                          somethinggood4 _

                                          I realize this conversation is over seven months old, but it seemed reasonable to post here than to try to reinvent the wheel...

                                           

                                          I have the same problem - 15 reports and want to automate the extraction to Excel.  The bigger problem is that the reports are not in a standard format.  I've figured out a trap that will cover all the report data I need (N.NN), but I can't get the fields to work out across all reports.

                                           

                                          Is there any way I can program Monarch to Automatically open all the reports in a specific folder, in a specific order, then pull the lines that have amounts in them?  If I can get that, I ought to be able to map my existing spreadsheet to the values I export from Monarch. 

                                           

                                          Side note to Jonbhoy, Have you considered rather than cutting-and-pasting, mapping your "master sheet" to the individual sheets that are generated from the reports?  Keep the file names that Monarch generates as separate workbooks, but refer to the other workbooks in your master sheet. (Just a thought)