4 Replies Latest reply: May 15, 2014 9:56 AM by KevinCronin _ RSS

    Selecting Reports By VBA

    KevinCronin _

      Hi:

       

      A long time Monarch & Excel user, but recent to using VBA. A goal is to automate existing separate Monarch & Excel report processes. In one case we want to select a specific day's report to produce a weekly report. The reports are identified by day. The directories change by month. The following code describes our efforts to make this happen. The thought is to have the user provide specifics and the code execute. We start from Excel and later import the file. The only option that works is the hard coded sample. The 2 other versions (see code) don't run. I'm unsure whether it's the code or a Monarch limitation. We are using V5. Here's the code and thanks for your insights!

       

      Private Sub Form_Load()

       

      Dim MonarchObj As Object

      Dim openfile, openmod, t As Boolean

      Dim reportDayTiger, pathNameTiger, pathnameExcel, calendarYear, servicemonth As String 'sample lines to control selection

      calendarYear = InputBox("Please Name The Calendar Year For The Target Daily Report", "Identify The Daily Report Year")

      servicemonth = InputBox("Please Name The Service Month For The Target Daily Report. A Sample Entry Is January", "Identify The Report Month")

      reportDayTiger = InputBox("Please Identify The Target Report Date As mmddyy", "Name The Close Report")

      Worksheets("holddatapage").Range("D2") = calendarYear ' have used these lines to build a formula see 2nd open

      Worksheets("holddatapage").Range("E2") = servicemonth

      Worksheets("holddatapage").Range("C2") = reportDayTiger

      Worksheets("holddatapage").Range("G2") = pathNameTiger

      Worksheets("holddatapage").Range("G4") = pathnameExcel

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

      If MonarchObj Is Nothing Then

      Set MonarchObj = CreateObject("Monarch32")

      End If

      t = MonarchObj.SetLogFile("C:program FilesMonTempMPrg_G5.log", False)

      'openfile = MonarchObj.SetReportFile("I:Daily close" & calendarYear  servicemonth  reportDayTiger, False)

      'openfile = MonarchObj.SetReportFile("I:Daily close" & Sheets("holddatapage").Range("G2").Value, False)

      openfile = MonarchObj.SetReportFile("I:Daily close2004julyR1272Closeofday071904.txt", False)  '2004JulyR1272Closeofday071904.txt""I:month endPaychex2004GL06172004.txt", False) ' & GLpathName, False)

      If openfile = True Then

      openmod = MonarchObj.SetModelFile("I:month endmonarch models for month end reportsphysican box cap report 2003.mod")

      If openmod = True Then

      'Set filter for export to Excel

      MonarchObj.CurrentFilter = "professional no zeros"

      MonarchObj.CurrentSummary = "chargesincomebymonth"

      MonarchObj.ExportSummary ("I:daily close2004julyR1272weekcash071904.xls") 'export path

      End If

      End If

      MonarchObj.CloseAllDocuments

      MonarchObj.Exit

       

       

      End Sub

        • Selecting Reports By VBA
          Nick Osdale-Popa

          Should this line

          'openfile = MonarchObj.SetReportFile("I:Daily close" & calendarYear  servicemonth  reportDayTiger, False)

           

          read as:

          (with out the comment ')

           

          openfile = MonarchObj.SetReportFile("I:Daily close" & calendarYear & "" &[/b] servicemonth & "" &[/b] reportDayTiger & ".TXT"[/b], False)

           

          Also, if you're adding multiple reports to be run against 1 model, the FALSE should be changed to TRUE, so that they are added to Monarch's report list, otherwise the last opened file will be the only one run.

           

          [size="1"][ August 03, 2004, 08:18 PM: Message edited by: Nick Osdale-Popa ][/size]

          • Selecting Reports By VBA
            Data Kruncher

            Kevin,

            I have an additional tip for you. When declaring your variable using Dim, list each variable on its own line. While it accepts multiple declarations, VBA doesn't actually set the right type for each variable name. Instead, it will only set the last variable name as the requested type. For instance, this:

            [font="courier"]Dim openfile, openmod, t As Boolean /font[/quote]will create openfile and openmod as String, and t as Boolean. Whereas the repetitive:

            [font="courier"]Dim openfile As Boolean

            Dim openmod As Boolean

            Dim t As Boolean /font[/quote]will get want you really want.

             

            This can really give you headaches in the way of unexpected results.

             

            To test this, put a break at the calendaryear assignment. Open an immediate window and run your code. When it pauses (right away) print the value of openfile and openmod in the immediate window. Each will return a blank value. Printing the value of t however returns False. Now put each variable on its own line and test again.

             

            HTH,

            Kruncher

            • Selecting Reports By VBA
              Nick Osdale-Popa

              Just to nitpick    

              [font="courier"]Dim openfile, openmod, t As Boolean /font[/quote]openfile and openmod would both be VARIANTS and t would be boolean.

              • Selecting Reports By VBA
                KevinCronin _

                Thank you all for the response!

                 

                The missing " " in my initial code was the culprit. I didn't appreciate the potential problems w/ Dim similar items on the same line.

                 

                I was able to replicate the code with other models and reports. But I did get a rude shock when I learned that the code failed when distributed to those who actually run the reports. The code's "I" drive was their "G" or "H".

                 

                Another improvement I can see in the original code is more error messages. The log was quite helpful in suggesting the failure; but I can see a need for a less "tech" message like "reconfirm your answer"

                 

                Thanks