7 Replies Latest reply: May 15, 2014 9:58 AM by Bill Watson RSS

    running monarch from visual basic

    willem westra

      Hello

      I've created vb script to have monarch (9.0 pro)to export like 15 excel files with multiple sheets in them, so far so good. First I tried with the project file I have been using, but that stores the 1st query it opens with and each time I open it the same query opens too. I then tried to open the correct query and then the model file but there are three measures in the summary which all needs to be exported. I could not find any method to have it export all measures. Could you assist? thx in advance - willem westra

        • running monarch from visual basic
          Nick Osdale-Popa

          I also don't see any method that allows you to change the measure for a summary.

           

          There is a RunAllExports() method, you might want to give that a try.

           

          The only other way I can think of doing it, would be to duplicate the summary for as many measures you have, and set each one to a different measure.

          • running monarch from visual basic
            willem westra

            Thanks for your answer but this brings me back to my previous problem. Follwing note was added to the runallexports method: "Note that a call to SetProjectFile must have been made previously and a Project Export of the specified name must exist." This reopens the 1st query stored in the projectfile.

             

            So you see I'm kind of stuck here....

            rgds willem westra

            • running monarch from visual basic
              Data Kruncher

              Hi Willem,

               

              I suspect that you might be overlooking, or perhaps be misinterpreting, a piece of the puzzle.

               

              In order to use the RunAllExports method, your project file (.xprj) must define one or more defined exports. These detail what is going to be exported and where it's going, among other things.

               

              If you haven't setup these in your project file, you can still automate the exports, but it takes a little more work.

               

              Suppose that you have three summaries defined as "Summary A", "Summary B" and "Summary C".

               

              To export all of them you need to set individually each as the active summary in the summary window, then export the summary, as in:

               

              [font="courier"]objMonarch.CurrentSummary = "Summary A"

              objMonarch.JetExportSummary("C:Export file A.xls","Summary A",0)

               

              objMonarch.CurrentSummary = "Summary B"

              objMonarch.JetExportSummary("C:Export file B.xls","Summary B",0)

               

              objMonarch.CurrentSummary = "Summary C"

              objMonarch.JetExportSummary("C:Export file C.xls","Summary C",0)[/font][/quote]Just be careful that you list the CurrentSummary names exactly as they appear in your model.

               

              In this example I opted to export to Excel with Monarch, overwriting the orginal Excel file.

               

              HTH,

              Kruncher

              • running monarch from visual basic
                Nick Osdale-Popa

                Data, and willem correct me if I'm wrong here, I think the problem lies in the fact that he has summaries that have multiple measures that can be picked from the drop-down box in the toolbar. How do  you extract a summary per measure? Or specify a measure to use? 

                 

                I haven't used projects all that much, so my knowledge on them isn't all that great. Would those measures apply when you define the exports?

                • running monarch from visual basic
                  willem westra

                  Hi Data

                  According to my Programmers Guide, downloaded from datawatch site last w/e "the runallexports method" must be preceeded by a SetProjectFile and a Project Export of the specified name must exist. When I do this the datasource I want to export from (a query in access dbase) is unloaded and the one stored in the project is reloaded. I've tried without datasource stored in the project but then it get stuck in gaining access to the database. Also I'm stuck with the measures. I suppose it would be possible to have one summary per measure (provided problem with datasource can be solved) but this does complicate matters a lot.

                  • running monarch from visual basic
                    Data Kruncher

                    The project file you want to use in your code to export the summaries must already contain the link to the Access database and the query table.

                     

                    The project file contains a link to the model that will be used. The model gives you your field and summary information.

                     

                    As a test, open the project manually. If you see it loading records right away, your program code won't have any trouble. If it's not pulling records from the database, then perhaps there's something else happening. Does the database require a password, and if so, is it being supplied automatically? Are you sure that the Access query you've selected in Monarch generates any records in Access?

                     

                    As to the measures to be exported, if, for whatever reason, you must have only one measure per export then you're forced to define them individually and export them individually.

                    • running monarch from visual basic
                      Bill Watson

                      Willem

                       

                      I notice when creating a project export for a summary with multiple measures you can elect to export all measures.

                      on the next tab is the option to export either to seperate tabs in the same xls book or to seperate books/files.

                       

                      So my advice would be to create your summary with the multiple measures and set the summary to DeFault Filter.

                      Then set up a filter for each file you wish to create.

                      You can then have multiple project exports which specify the filter to use with the summary and the specific file name to save it as.

                       

                      Once the project is saved you could then call the RunALLExports()method.

                       

                      I think that might solve your problem and keep your VB nice and simple.

                       

                      Example Using Employ.PRN

                       

                      Save the following to your Program FilesMonarchModels folder as Employ_AllMeasures.xmod:

                      [font="courier"]<model version="XMLMOD02"><author>National Australia Group</author><description></description><settings><input><date_pattern>D/M/Y</date_pattern><first_year_of_century>50</first_year_of_century><active_patterns>D,DT,T</active_patterns><decimal_char>.</decimal_char><leading_chars>0</leading_chars><ignore_form_feeds>false</ignore_form_feeds><ignore_ctrl_chars>false</ignore_ctrl_chars><input_encoding>ASCII</input_encoding><input_ascii>true</input_ascii><case_sensitive_traps>false</case_sensitive_traps><force_break max_page_lines="256"/></input><grid suppress_zero="false" null_as="(Null)"><table header_lines="1" data_lines="1"/></grid><views><table><font face="Tahoma" size="10"/></table><summary><font face="Tahoma" size="10"/></summary></views><export><dbf_field_naming>false</dbf_field_naming><database><summary names_from_fields="false"/></database><text names_row="true" spacing="true" pad_with_zeros="false" character_set="ASCII" eof="true"><delimited character="," quoted="true" names_row_quotes="false" format_dates_with_dashes="true"/></text><excel apply_formatting="false"/></export></settings><chart_appearance hatch_patterns="false"><color number="1">16744576</color><color number="2">6299776</color><color number="3">16711935</color><color number="4">14680224</color><color number="5">8421440</color><color number="6">8388863</color><color number="7">8421504</color><color number="8">16733525</color><color number="9">8388736</color><color number="10">12648641</color><color number="11">7566335</color><color number="12">16728192</color><color number="13">32768</color><color number="14">4210816</color><color number="15">255</color><color number="16">16760831</color><color number="17">16777215</color><color number="18">14803425</color><color number="19">9934743</color><title><font face="Verdana" size="12"/></title><label><font face="Verdana" size="10"/></label><values><font face="Verdana" size="8"/></values></chart_appearance><templates><template><name>Detail</name><level_type>detail</level_type><number>11</number><is_float>false</is_float><is_multicolumn>false</is_multicolumn><trap_line>1</trap_line><mask>                                                                               /  /</mask><sample><line>Stephen     McPherson    410 Market St         Boston          MA     02115  06/14/92  M    Data Processing   27800.00</line></sample></template></templates><fields><table_field><name>FirstName</name><size>11</size><type>C</type><sequence>1</sequence><display_length>11</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>11</length><line>1</line><offset>1</offset></input></table_field><table_field><name>LastName</name><size>12</size><type>C</type><sequence>2</sequence><display_length>12</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>12</length><line>1</line><offset>13</offset></input></table_field><table_field><name>Street</name><size>21</size><type>C</type><sequence>3</sequence><display_length>21</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>21</length><line>1</line><offset>26</offset></input></table_field><table_field><name>City</name><size>15</size><type>C</type><sequence>4</sequence><display_length>15</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>15</length><line>1</line><offset>48</offset></input></table_field><table_field><name>ZIP</name><size>6</size><type>C</type><sequence>6</sequence><display_length>6</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>6</length><line>1</line><offset>71</offset></input></table_field><table_field><name>HireDate_1</name><size>8</size><type>C</type><hide>true</hide><sequence>7</sequence><display_length>8</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>8</length><line>1</line><offset>78</offset></input></table_field><table_field><name>SEX</name><size>1</size><type>C</type><sequence>9</sequence><display_length>4</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>1</length><line>1</line><offset>88</offset></input></table_field><table_field><name>Dept</name><size>15</size><type>C</type><sequence>10</sequence><display_length>15</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>15</length><line>1</line><offset>93</offset></input></table_field><table_field><name>Salary</name><size>18</size><type>N</type><decimal>2</decimal><format_type>0</format_type><sequence>11</sequence><display_length>10</display_length><alignment>R</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>10</length><line>1</line><offset>109</offset></input></table_field><table_field><name>State</name><size>6</size><type>C</type><sequence>5</sequence><display_length>6</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>6</length><line>1</line><offset>64</offset></input></table_field><table_field><name>HireDate</name><size>8</size><type>D</type><format_type>1</format_type><sequence>8</sequence><display_length>20</display_length><alignment>L</alignment><formula_field><expression>ctod(substr(HireDate_1,4,3)left(HireDate_1,3)right(HireDate_1,2))</expression></formula_field></table_field><table_field><name>Service</name><size>18</size><type>N</type><format_type>0</format_type><sequence>12</sequence><display_length>20</display_length><alignment>R</alignment><formula_field><expression>Age(HireDate,today(),1)</expression></formula_field></table_field></fields><filters><current>Shipping</current><filter type="formula_based"><name>Data_Processing</name><short_name>data_proce</short_name><filter_expression>Dept="Data Processing"</filter_expression></filter><filter type="formula_based"><name>Production</name><short_name>production</short_name><filter_expression>Dept="Production"</filter_expression></filter><filter type="formula_based"><name>Marketing</name><short_name>marketing</short_name><filter_expression>Dept="Marketing"</filter_expression></filter><filter type="formula_based"><name>Accounting</name><short_name>accounting</short_name><filter_expression>Dept="Accounting"</filter_expression></filter><filter type="formula_based"><name>Shipping</name><short_name>shipping</short_name><filter_expression>Dept="Shipping"</filter_expression></filter></filters><summaries><current>ByLengthService</current><summary><name>ByLengthService</name><short_name>BYLENGTHSE</short_name><color><default text_color="8" fill_color="0"/><title text_color="8" fill_color="7"/><total text_color="8" fill_color="0"/></color><filter default="true"/><total insert="true" label="Summary"/><state header_lines="1" data_lines="1" active_measure_index="1"/><chart type="Bar (Vertical)" cluster="0"><three_d>false</three_d><joined>false</joined><stacked>false</stacked><h_grid>false</h_grid><v_grid>false</v_grid><force_zero>true</force_zero><same_y>true</same_y><display_values>false</display_values><legends stagger="false" width="5" height="1"/></chart><key><name>Dept</name><display_length>15</display_length><sort direction="A"><sort_by_self/></sort><matching><all/></matching><display across="false"><suppress_duplicates>false</suppress_duplicates><insert_subtotal>false</insert_subtotal><blank_line>false</blank_line><page_break>false</page_break></display></key><key><name>SEX</name><display_length>8</display_length><sort direction="A"><sort_by_self/></sort><matching><all/></matching><display across="true"><suppress_duplicates>false</suppress_duplicates><insert_subtotal>false</insert_subtotal><blank_line>false</blank_line><page_break>false</page_break></display></key><key><name>Service</name><display_length>8</display_length><sort direction="A"><sort_by_self/></sort><matching><all/></matching><display across="false"><suppress_duplicates>false</suppress_duplicates><insert_subtotal>false</insert_subtotal><blank_line>false</blank_line><page_break>false</page_break></display></key><measure><display_length>14</display_length><calculation><expression>SUM(Salary)</expression></calculation></measure><measure><display_length>10</display_length><calculation><expression>AVG(Salary)</expression></calculation></measure></summary></summaries><audit><save action="Created" author="National Australia Group" time="2007-10-30T13:33:39"/></audit>

                      </model>[/font][/quote]Save the following to your Program FilesMonarchProjects folder as Employ_AllMeasures.xprj:

                      [font="courier"]<project version="XMLPRJ01"><author>National Australia Group</author><description></description><model><location>C:Program FilesMonarchModelsEmploy_AllMeasures.xmod</location></model><inputs><main><report><location>C:Program FilesMonarchReportsEmploy.prn</location></report></main></inputs><state><filter>Shipping</filter><summary>ByLengthService</summary><view><window>table</window></view></state><exports><export><data_page view="summary"><name>Accounting</name><summary name="ByLengthService"><drilled_down>false</drilled_down><all_measures>true</all_measures></summary><filter all="false" name="Accounting"/></data_page><file_page><files_in_folder>false</files_in_folder><location>C:Program FilesMonarchExportAccounting.xls</location><when_exists>overwrite</when_exists></file_page><table_page><when_exists>skip</when_exists></table_page></export><export><data_page view="summary"><name>Data_Processing</name><summary name="ByLengthService"><drilled_down>false</drilled_down><all_measures>true</all_measures></summary><filter all="false" name="Data_Processing"/></data_page><file_page><files_in_folder>false</files_in_folder><location>C:Program FilesMonarchExportData_Processing.xls</location><when_exists>overwrite</when_exists></file_page><table_page><when_exists>skip</when_exists></table_page></export><export><data_page view="summary"><name>Marketing</name><summary name="ByLengthService"><drilled_down>false</drilled_down><all_measures>true</all_measures></summary><filter all="false" name="Marketing"/></data_page><file_page><files_in_folder>false</files_in_folder><location>C:Program FilesMonarchExportMarketing.xls</location><when_exists>overwrite</when_exists></file_page><table_page><when_exists>skip</when_exists></table_page></export><export><data_page view="summary"><name>Production</name><summary name="ByLengthService"><drilled_down>false</drilled_down><all_measures>true</all_measures></summary><filter all="false" name="Production"/></data_page><file_page><files_in_folder>false</files_in_folder><location>C:Program FilesMonarchExportProduction.xls</location><when_exists>overwrite</when_exists></file_page><table_page><when_exists>skip</when_exists></table_page></export><export><data_page view="summary"><name>Shipping</name><summary name="ByLengthService"><drilled_down>false</drilled_down><all_measures>true</all_measures></summary><filter all="false" name="Shipping"/></data_page><file_page><files_in_folder>false</files_in_folder><location>C:Program FilesMonarchExportShipping.xls</location><when_exists>overwrite</when_exists></file_page><table_page><when_exists>skip</when_exists></table_page></export></exports><audit><save action="Created" author="National Australia Group" time="2007-10-30T13:34:05"/></audit>

                      </project>[/font][/quote]Open the Project and Run All Exports.

                      This will create a file in the Exports folder for each department with 2 sheets each - Total Salary by Sex, Average Salary by Sex.

                       

                      Hope this helps.