3 Replies Latest reply: May 15, 2014 10:02 AM by RalphB _ RSS

    2 Filters to export

    Awol40 _

      Could anyone tell me how to export an additional filter named "Non On Acct" in the below code.

       

      I have two filters and one summary to export. This deals with one of each but I need the other filter to appear as a seperate tab in Excel.

       

      Any help appreciated

       

       

       

      'Launch Monarch

      '

          Dim MonarchObj As Object

          Dim openfile As Boolean

          Dim openmod As Boolean

          Dim t As Boolean

          Dim exptable As Boolean

          Dim expsum As Boolean

      '

          Set MonarchObj = GetObject("", "Monarch32")

           

          If MonarchObj Is Nothing Then

              Set MonarchObj = CreateObject("Monarch32")

          End If

       

          MonarchObj.Visible True

      '   MonarchObj.Visible = False

           

          t = MonarchObj.SetLogFile("C:\Temp\Monarch\LogFile.log", False)

          File_Name = PRN_Dir & User_Name & "_Income Print.prn"

          openfile = MonarchObj.SetReportFile(File_Name, False)

           

          If openfile = True Then

              File_Name = Model_Dir & "PRN PMA Trans Listing - Income.xmod"

              openmod = MonarchObj.SetModelFile(File_Name)

                      

              If openmod = True Then

                      MonarchObj.CurrentFilter = "Service Charge On Account"

                      MonarchObj.CurrentSort = "On Acct Debtor Totals"

                      File_Name = Export_Dir & User_Name & "_PMA Income file.xls"

                      exptable = MonarchObj.JetExportTable(File_Name, "On Account Detail", 0)

                      expsum = MonarchObj.JetExportSummary(File_Name, "On Account Totals", 0)

             End If

          End If

           

          MonarchObj.CloseAllDocuments

          MonarchObj.Exit

           

          Workbooks.Open Filename:= _

              File_Name _

              , Editable:=True

       

      End Sub

        • 2 Filters to export
          RalphB _

          Hi Awol40 and welcome to the forum.

           

          You are so close to getting your summary on a new tab.

           

          All you have to do is change your JetExportSummary Integer Appendflag to either a 1.  Like expsum = MonarchObj.JetExportSummary(File_Name, "On Account Totals", 1).

           

          The Integer AppendFlag in the JetExportSummary or JetExportTable method tells it if it should create a new table (0), add a new table or tab (1), or to append to an existing table or tab (2).

           

          HTH.

            • 2 Filters to export
              Awol40 _

              Thanks for the reply Ralph.

               

              What I don't understand is that if I only have one filter defined in the code i.e  MonarchObj.CurrentFilter = "Service Charge On Account"

               

              How do I get the second filter named "Not On Account" to export.

               

              Have tried changing the numbers like you say but I think this only to adds to or overwrites the old document for the one filter.

               

              I want 2 filters and 1 summary to replace the old file.

               

              Sorry if I'm being dim and thanks again for your time.

                • 2 Filters to export
                  RalphB _

                  Hi,

                   

                  No problem.

                   

                  After you export the table  and summary with your first filter, you need to set your filter to whatever your second filter name is and export the table and summary again using the appropriate append flag.