8 Replies Latest reply: May 15, 2014 9:52 AM by Data Kruncher RSS

    VBA code for opening a model with a Runtime Parameter

    HKS _

      I have a V8.0 monarch, could someone write me a VBA code example to open a model with a Runtime Parameter.

       

      PromptAcct = MonarchObj.SetRuntimeParameter("ACCTNO", "2100")

      OpenModel = MonarchObj.SetModelFile("Modelname")

       

      Thanks

        • VBA code for opening a model with a Runtime Parameter
          Nick Osdale-Popa

          Your PromptAcct[/b] variable should be DIM'd as boolean.

           

          Check this variable after it's been set. If it's 0, then there was a problem setting the runtime parameter. Check to make sure your parameter name is correct and that you are passing the correct type of data. Is AcctNo[/b] the parameter name or the field you are setting the filter to?

           

          Other than that, the code looks correct to me.

           

          Originally posted by HKS:

          I have a V8.0 monarch, could someone write me a VBA code example to open a model with a Runtime Parameter.

           

          PromptAcct = MonarchObj.SetRuntimeParameter("ACCTNO", "2100")

          OpenModel = MonarchObj.SetModelFile("Modelname")

           

          Thanks /b[/quote]

          • VBA code for opening a model with a Runtime Parameter
            HKS _

            Thanks for replying both at the post here and the private mail. More questions here:

             

            1) Any code for me just close the model while keeping the current report file opens?

             

            2) I ran the VBA code, and it stopped because monarch still prompted the "Runtime Parameter" and asked for the input.

             

            Anyway, I just want to export data for certain account numbers from a report that has many acoount numbers. When exporting the data for these few account numbers, I need them to export to a file that has different worksheets for each account number.

             

             

            Openfile = MonarchObj.SetReportFile("Reportname", False)

            If Openfile = False Then

                End

            else

                vRow = 8

                Do While IsEmpty(Cells(vRow, 2)) = False

                    vAcct = Cells(vRow, 2)

                    PromptAcct = MonarchObj.SetRuntimeParameter("ACCT", vAcct)

                    OpenModel = MonarchObj.SetModelFile("Modelname")

                    If OpenModel = False Then

                        End

                    else

                        ExportData = MonarchObj.JetExportTable("ExportName" & ".xls", vAcct, 2)

                        MonarchObj.SetView ("R")

                        MonarchObj.CloseModelFile????

                    End if

                    vRow = vRow + 1

                Loop

             

                MonarchObj.CloseAllDocuments

                MonarchObj.Exit

            • VBA code for opening a model with a Runtime Parameter
              Data Kruncher

              I think the part of the puzzle you might be missing is including a filter defined as:

              [font="courier"]ReportAccount = ACCT  /font[/quote]where ReportAccount is the account number field in the table and ACCT is your runtime parameter.

               

              Make this the active filter and save the model.

               

              Now when you pass an account number to the model via the runtime parameter, the filter kicks in automatically, limiting the records to be exported to those matching the account number specified in the runtime parameter.

               

              Does this resolve this challenge for you?

               

              Kruncher

              • VBA code for opening a model with a Runtime Parameter
                Nick Osdale-Popa

                HKS, see this  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001452#000001"]thread[/url]  as it may be related to your problem.

                 

                Also, let's make sure that you are passing a string for the SetRuntimeParameter() function:

                [font="courier"] PromptAcct = MonarchObj.SetRuntimeParameter("ACCT", CStr(vAcct))[/font][/quote]Just to followup with what DataKruncher has stated, you need to make sure you have a filter set with the parameter.  But you  don't necessarily have to save it as the default, you can use the CurrentFilter property to set the filter before exporting.    Granted, saving it as default saves you this bit of code, but the code insures that the filter is correctly set.

                • VBA code for opening a model with a Runtime Parameter
                  HKS _

                  Yes, yes, the macro works!! Thanks a lot!!

                   

                  May I know is there a code for just closing the model?

                   

                  I also used your suggestion on verifying the version. However, I modified slightly so that the user only get the message when the version is less than 8.00 and the macro is ended.

                   

                  Dim vVersion as string

                  vVersion = MonarchObj.Version()

                  If Mid(vVersion, Len(vVersion) - 3, 1) < 8 Then

                  MsgBox "This macro only runs with Monarch version 8.00 or higher, but your Monarch is " & vVersion & ". Macro ends."

                  End

                  End If

                  • VBA code for opening a model with a Runtime Parameter
                    Nick Osdale-Popa

                    I don't think there is a command to explicitly close a model. One thing you can do is open another model with the SetModelFile() function, which will replace the currently loaded model.

                     

                    The only other way is to issue a CloseAllDocuments() and then re-open your report with SetReportFile().

                     

                    Glad to hear you got the macro working! Was it the CStr() (C[/b]onvert to STR[/b]ing) command that helped?

                     

                    Originally posted by HKS:

                    Yes, yes, the macro works!! Thanks a lot!!

                     

                    May I know is there a code for just closing the model?

                     

                    I also used your suggestion on verifying the version. However, I modified slightly so that the user only get the message when the version is less than 8.00 and the macro is ended.

                     

                    Dim vVersion as string

                    vVersion = MonarchObj.Version()

                    If Mid(vVersion, Len(vVersion) - 3, 1) < 8 Then

                    MsgBox "This macro only runs with Monarch version 8.00 or higher, but your Monarch is " & vVersion & ". Macro ends."

                    End

                    End If /b[/quote]

                    • VBA code for opening a model with a Runtime Parameter
                      HKS _

                      Actually it was the runtime parameter name. Earlier, I had the filter name in the SetRuntimeParameter. (I reread your reply on the post, and found out that the above mistake). The variable vAcct is dim as integer in my macro and it worked just fine.

                      • VBA code for opening a model with a Runtime Parameter
                        Data Kruncher

                        Glad I could help.   :rolleyes:    :rolleyes:     

                         

                        Seriously, glad to hear that your problem has been resolved.

                         

                        Kruncher