12 Replies Latest reply: May 15, 2014 10:01 AM by Nick Osdale-Popa RSS

    VB subroutine no longer working with V9

    Chris Wong

      Can anyone help me with a small problem with running a VB script that was working fine until I upgraded to a server based Monarch V9 versus a stand alone V7.  Now when I launch the script via Excel Macro I get the following error when it gets to exportfile:  Compile Error, can't find project or library.  Script was derived from the progammers manual from V7 and V8.  Here is the script

       

      Sub Bank_ATM_Daily()

       

      ' Macro recorded by pl41348

      'define some terms and connect to Monarch

      Dim MO As Object

      Dim openfile, openmod, t As Boolean

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

      If MO Is Nothing Then

         Set MO = CreateObject("Monarch32")

      End If

      'set-up a file to trap errors

      t = MO.SetLogFile("
      pclfile1\P&C_OPS_Performance\R&A\Private\Monarch\Errorfile", False)

       

      'Process Bank_ATM_Daily(MCEF000061)

      openfile = MO.SetReportFile("
      Pclfile1\p&c_ops_performance\DAS\Monarch\Text\Bank\MCEF000061.rpt", False)

      If openfile = True Then

         openmod = MO.setmodelfile("
      pclfile1\P&C_OPS_Performance\DAS\Monarch\MODELS\ATM_MCEF000061.mod")

         If openmod = True Then

            exportfile = MO.JetExportTable("//reportingandanalysis.usaa.com/C19/C1/Bank Assistance/Document Library/Bank ATM Journal 61.xls", "ATM", 2)

         End If

      End If

      MO.CloseAllDocuments

       

       

      MO.Exit

      End Sub

        • VB subroutine no longer working with V9
          Nick Osdale-Popa

          At first glance, it looks like you haven't DIM'd exportfile as boolean. Not sure if that's actually giving you the problem or not, as I haven't worked with a network version.

           

          Note: Although the programmer's guide shows this:

           

          Dim openfile, openmod, t As Boolean[/B]

           

          Which is perfectly acceptable in VB, however in VBA (Excel, Access, etc), openfile[/B], and openmod /Bwould be DIM'd as variant. VBA has to have the datatype specified for each and every variable.

           

          Also at the top of every module I put in OPTION EXPLICIT[/B]. This will let me know if I have any variables that have not been DIM'd.

            • VB subroutine no longer working with V9
              Chris Wong

              Thanks Nick.  I did Dim exportfile before I posted and it worked but I found it interesting that the exact VB worked for years until V9 and suddenly I need to Dim exportfile.  I wanted some second opinions to be sure.  I will change openfile and openmod as variant.  Would you recommend exportfile be also variant?

                • VB subroutine no longer working with V9
                  Data Kruncher

                  Would you recommend exportfile be also variant?[/quote]

                   

                  JetExportTable returns a Boolean data type, so dim exportfile as Boolean.

                   

                  Variant is meant to be flexible and work for any data type, but it adds more overhead to your program for the inherent flexibility. While debateable, in this day and age with PCs with much more resources than they used to have in terms of RAM, this is really only an issue for larger programs, older computers, or the really picky programmer.

                   

                  Using a Variant data type with JetExportTable really ought to work just fine, but since we know that Boolean is ideal, then just set the field as such.

                   

                  HTH,

                  Kruncher

                  • VB subroutine no longer working with V9
                    Nick Osdale-Popa

                    Thanks Nick.  I did Dim exportfile before I posted and it worked but I found it interesting that the exact VB worked for years until V9 and suddenly I need to Dim exportfile.  I wanted some second opinions to be sure.  I will change openfile and openmod as variant.  Would you recommend exportfile be also variant?[/QUOTE]

                     

                    I think you misunderstood me.  exportfile, openfile and openmod "should" all be DIM'd as Boolean.

                     

                    The statement (in VBA):

                     

                    DIM openfile, openmod, t As Boolean

                     

                    Would only make t as Boolean and the other variables as variant. VBA does not extend the Boolean datatype to the other variables.

                     

                    It's perfectly acceptable, but you should be aware of what your variables are doing as it can[/i] lead to unpredictable results as well as cause overhead for the computer. Granted, as Kruncher has stated, it's negligible in today's PCs, but the fact is still true.

                • VB subroutine no longer working with V9
                  rconverse _

                  I'm intrigued by this.  If it were the variable, then wouldn't the error be different?  Or would that error only show if Option Explicit was at the top?

                   

                  If that is not the issue. then I would check to make sure V9 is the pro version and .jetexporttable is availble.

                   

                  Nick, this is acceptable in VBA as well:

                   

                  Dim openfile, openmod, t As Boolean

                  /code

                   

                  Only reason I know that is because I copied straight from the manual and didn't know enough to change them. 

                    • VB subroutine no longer working with V9
                      Nick Osdale-Popa

                      Nick, this is acceptable in VBA as well:

                       

                      Dim openfile, openmod, t As Boolean

                      /code

                       

                      Only reason I know that is because I copied straight from the manual and didn't know enough to change them.  :)[/QUOTE]

                       

                      Yes, it's acceptable, but as stated, only t will be Boolean, the other two variables will be of type variant.

                        • VB subroutine no longer working with V9
                          rconverse _

                          Yes, it's acceptable, but as stated, only t will be Boolean, the other two variables will be of type variant. ;)[/QUOTE]

                           

                          Oh, I see after reading a little more carefully.  Is it painfully obvious that I am self taught? 

                           

                          Thanks!

                          Roger

                            • VB subroutine no longer working with V9
                              Grant Perkins

                              Not sure if this relates but as I recall V9 requires .xmod files and does not interpret .mod files as V7 and V8 used to do.

                               

                              Likewise .xprj files for anyone using projects.

                               

                              Monarch Utility will provide a conversion facility.

                               

                               

                              Grant

                                • VB subroutine no longer working with V9
                                  Data Kruncher

                                  V9 requires .xmod files and does not interpret .mod files as V7 and V8 used to do[/quote]

                                   

                                  Actually, v9 doesn't have any trouble with older .mod or .prj files, even when used via automation.

                                   

                                  One of my large weekly reporting processes uses both .xprj and .prj, along with .mod and .xmod all within one Excel VBA program without any problems.

                                    • VB subroutine no longer working with V9
                                      Grant Perkins

                                      Actually, v9 doesn't have any trouble with older .mod or .prj files, even when used via automation.

                                       

                                      One of my large weekly reporting processes uses both .xprj and .prj, along with .mod and .xmod all within one Excel VBA program without any problems.[/quote]

                                       

                                      Interesting.

                                       

                                      Must be my memory playing tricks on me. Is that true of scripted batch files as well? It's a while since I needed any that were originally produced with .mod and .prj files.

                                       

                                      So the only constraint between the different file versions is that V9 does not output .mod and .prj files?

                                       

                                      Grant

                                        • VB subroutine no longer working with V9
                                          Data Kruncher

                                          Monarch shouldn't have any trouble with those types, regardless of how it's invoked; be it VBA, command line, or other scripting such as VBS.

                                           

                                          the only constraint between the different file versions is that V9 does not output .mod and .prj files[/QUOTE]

                                           

                                          Correct. And a bit of a pain at that, for those few of us who, on rare occasion, do need to create the older file types, without the fancy bells and whistles that the newer versions provide.

                                • VB subroutine no longer working with V9
                                  Data Kruncher

                                  Hi Chris,

                                   

                                  A couple of possible ideas to try...

                                   

                                  Double check your project references to make sure that the reference to Monarch isn't "MISSING" for some reason. This is pretty unlikely, but worth checking nonetheless.

                                   

                                  Try exporting to a local file instead of a URL. There might be something going on there that gives the export a problem that generates a somewhat misleading error message.