10 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    automate extraction

    cscocy _

      Hi,

       

      I'm using monarch pro v8 and i need to do an automate extraction of data from several files into the same spreadsheet.

       

      Managed to find the following batch codes

       

      C:Progra~1MonarchprogramMonarch.exe "C:     extfile1.txt" "C:MonarchModelssample.mod" "C:combined.xls" /t

       

      c:progra~1monarchprogrammonarch.exe "C:     extfile2.txt" "C:MonarchModelssample.mod" "C:combined.xls" /t

       

      -May i know if the codes above can extract data from textfile1 using sample.mod and write it to combined.xls and then extract data from textfile2 and APPEND to combined.xls

      -If not, can anyone give me some examples on how it can be done

      -May i know the purpose of "/t"

       

      I've also download the programmer guide and i would like to ask where am i suppose to write and run the program?

       

      Also if i'm writing in VB, do i have to install VB platfrom to compile and run the codes, or can monarch do that for me

       

      Many Thanks

       

      [size="1"][ October 11, 2006, 03:46 AM: Message edited by: cscocy ][/size]

        • automate extraction
          Nick Osdale-Popa

          I'm not familiar enough with the command line parameters, so I can't help you with your first question.

           

          As far as the programmer's guide, if you are going to program strictly in VB, then yes, you will need to install VB on your system.  However, the VB examples in the guide can be used in any VBA (Visual Basic For Application) platform (Excel, Access, Word, etc), which I'm guessing you may already have installed.

           

          You can search this forum for many questions concerning using VBA or if you have specific questions, myself, Data Kruncher and many others on this forum are using VBA extensively so we can provide you answers readily.

          • automate extraction
            Data Kruncher

            Don't forget that you can also automate via VBS scripts without the need for Office applications. With the supplied editor, VBA is a easier to work with though.

             

            You can search the forum posts for VBS references.

             

            Just another option.

             

            Kruncher

            • automate extraction
              cscocy _

              Hi,

               

              I've tried coding in VBA code using excel

              here's a copy of my code

               

              Sub automateMonarch()

                   

                  Dim MonarchObj As Object

                  Dim openfile, openmod As Boolean

                  Dim exported As Boolean

                   

                  Set MonarchObj = CreateObject("Monarch32")

                   

                  openfile = MonarchObj.SetReportFile("C:Documents and Settingsco53193DesktopES32Report 0615", False)

               

                  openmod = MonarchObj.SetModelFile("C:Documents and Settingsco53193DesktopES32header.xmod")

                   

                  exported = MonarchObj.JetExportTable("C:Documents and Settingsco53193DesktopES32     esting.xls", "Sheet1", 2)

              End Sub

               

              i've tried running the code and all was fine until they reach the JetExportTable part whereby there's a runtime error (438) indicating that the object does not support this method or property.

               

              i've search the forum and apparantly it's a common error, however the solution is to declare exported as boolean (which i've done, but to no avail), could you help me with this?

               

              Also i've noticed that Monarch pops up upon running the macro, is there any way to let it run in the background when the macro is activated?

               

              I'm using Excel 2000, will it affect the code?

               

              Thanks

              CY

              • automate extraction
                Tom2 _

                I just cut & pasted your VBA, replaced paths & files for local files on my machine, and it executed flawlessly. 

                 

                Just a couple of issues that have been shared with me in the past on this forum for VBA pointers using Monarch:

                -on the line before Sub AutomateMonarch() type Option Explicit and press return (ensures every variable is "initialized" in a Dim statement (helps prevent typing/spelling/mistakes)

                 

                -place each DIM statement on its own line to ensure it is appropriately initialized with the right characteristics

                 

                -make sure your system is "current" (there is an 8.02 update for Monarch Pro)

                 

                -Go to Monarch and verify that XLS Files is set to 97+ and apply special formatting is checked.  (The export file version is defined by the settings in the Monarch Options under Folders & File Types and will apply to the extension specified as part of ExportFile.)

                 

                Just "general" items.  Hope it helps.

                • automate extraction
                  Data Kruncher

                  Tom's posted some good tips. I'd pursue those before proceeding with anything else.

                   

                  Just a thought CY, realistically, it's unlikely, but it is possible[/i], that the JetExportTable is failing because of older MS Data Access Components (MDAC) installed on your system.

                   

                  You can  [url="http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en"]visit the MS site to get a utility which checks the MDAC version you have installed[/url] and download newer software if necessary.

                  • automate extraction
                    Nick Osdale-Popa

                    Originally posted by cscocy:

                    Also i've noticed that Monarch pops up upon running the macro, is there any way to let it run in the background when the macro is activated?

                    CY /b[/quote]To hide the Monarch App Use this in your code:

                     

                    MonarchObj.Visible False

                     

                    Set it to true, to un-hide it.

                    This will still pop-up the Export Progress Window, which is good, because it gives the user an indication that something is happening.

                     

                    You can also use the following commands to set the window size:

                     

                    MonarchObj.DisplayWindow (0) 'Maximize Window

                    MonarchObj.DisplayWindow (1) 'Restore Window

                    MonarchObj.DisplayWindow (2) 'Minimize Window

                    • automate extraction

                      When you say append do you mean to the same worksheet in excel or a seperate worksheet? I know you can append to seperate worksheets, but I don't know if you can append to the same worksheet.

                       

                      I have automated many Monarch processes using the AutoScript Utility that is provided with Monarch. Go to the Monarch Help and do a search on AutoScript. It is pretty simple and works quite well. Also do a search on PRJ. Projects as I recall were introduced in V7 and are quite handy. They make coding the .bat much less cumbersome.

                       

                      A .prj file is a project which is nothing more then a macro. You record key strokes and then save it as a .prj file.

                       

                      Using AutoScript you setup a .bat file to execute the .prj file.

                       

                      Executing the .prj file will execute the key strokes that you recorded.

                       

                      1) Open the input file

                      2) Open the model file and apply any filters, calculations or summaries that are a part of the model

                      3) Export the results to whatever format you want

                       

                      You can execute as many .prj files as you want in a .bat file and each time it will do what you recorded in the .prj file.

                       

                      If you told the 1st .prj file to overwrite the Excel spreadsheet it will create or replace one if it existed.

                       

                      If you told the 2nd .prj file to append to the same spreadsheet. It will append the data as a seperate worksheet to the same Excel Spreadsheet.

                       

                      I hope this helps and if you need better examples then what is in the help let me know.

                       

                      Dan Kinney

                      • automate extraction
                        cscocy _

                        Tom:

                        Just did a check on the version and it's stated Version 8.00

                        Does this means that i'm using Version 8.00 and not Version 8 Pro?

                        If so, is that the reason you i got the error?

                        Also how do i go about getting the 8.02 update?

                         

                        Kruncher:

                        Did a MDAC check and i got MDAC 2.7 SP1 and MDAC 2.7 SP1 REFRESH. Is this the minimum version needed for the codes to run? If not can you kindly advise the version needed.

                         

                        I'm using Windows 2000 with SP4

                        • automate extraction
                          Tom2 _

                          1.  When Monarch is open, my Help, About Monarch shows:

                          Monarch Pro

                          Version 8.02

                           

                          at the top.  I will be assuming (danger) that you do not have the pro version are are running "standard."

                           

                          2.  Downloads @ [url="http://www.datawatch.com:"]www.datawatch.com:[/url]

                          Link for English version: [url="http://www.datawatch.com/support/downloads/m802_upd.exe"]http://www.datawatch.com/support/downloads/m802_upd.exe[/url]

                           

                          The monarch programmer's guide has a table in the appendix that indicates which properties & methods are available in 8.00 vs. 8.00 Pro.  JetExportSummary & JetExportTable are NOT available in the "standard 8.00 version" according to the table.  7 of 42 methods/properties are ONLY available in 8.00 Pro.

                           

                          Please see guide at [url="http://www.datawatch.com/pdf/products/monarch/Monarch_8_Programmers_Guide.pdf"]http://www.datawatch.com/pdf/products/monarch/Monarch_8_Programmers_Guide.pdf[/url]

                           

                          Hope this helps.  Pro has been worth the upgrade due to these few differences when it comes to VBA and database issues.

                          • automate extraction
                            Data Kruncher

                            I'd think you'd be OK with MDAC 2.7 software, though the latest is 2.8, but I'm not positive that installing it would affect anything directly related to the problem at hand.

                             

                            I've run across situations where upgrading from 1.x to 2.x on particular systems did have an impact on how certain software performed.