9 Replies Latest reply: Sep 23, 2016 5:29 PM by Grant Perkins RSS

    VBA & Monarch

    Tom2 _

      Givens: 

      1. VBA knowledge limited enough to be dangerous.

      2.  Monarch knowledge 12 years of advanced models and autoscript/bat/excel usage.

      3.  Ver 8 Pro

       

      Some functions I performed in Monarch using autoscript I want to put into excel using VBA.  A good example is:

       

      files:  dailyATM2006_08_01.txt with a file for each calendar day - I used to put a batch file in place and copy dailyatm2006_08_*.txt monthly082006.txt using an autoscript prework.bat called from the "work.bat" file executed by autoscript.  Just have to keep changing the batch file each month and I have a dozen different uses that an answer would make my life easier (as Monarch always has!).

       

      My knowledge of VBA is limited to that found in the Monarch Programmers Guide.

       

      I have tried to find a couple of simple subroutines on the web but remembered my friends at this forum and thought they CAN help (proven resource).

       

      How can I commingle those reports using "simple" VBA in excel so that a menu form can be designed with year and month prior to making my project calls and exports?

       

      I have already searched for VBA as a keyword within the forum and not too many examples that match up with someone who learns from a well built example!

        • VBA & Monarch
          Data Kruncher

          Hi Tom,

           

          Wow, lots of possibilities here.

           

          For a beginner with VBA, I'd try to simplify this as much as possible in Excel.

           

          For the year, create a drop down list in a cell using Data Validation. Let's say you put "Year:" in A5, and the drop down in B5.

           

          Now put "Month:" in A7 and another drop down list in B7.

           

          Now with the Control Toolbox toolbar, add a command button to your worksheet. Clicking this button will start your export process.

           

          You'll need the Range keyword to get the values out of B5 and B7. To get the leading zero you'll need to match the filename format for the month number, you'll need to use WorksheetFunction.Text(Range("B7"),"00").

           

          I've got to leave you with this for now. I'd be pleased to help with further questions, but, as you seem to want to head in this direction, would also recommend that you invest some time learning as much as you need to about the object model for Excel. You can often do some amazing things with very little program code; just a few lines sometimes.

           

          First tackle how you do something in VBA that you think should be pretty straightforward but don't know how to do now, based on your current experience. I'd start with how to manipulate Worksheets, Ranges, and maybe Workbooks.

           

          Good luck. If you understand batch files, it really isn't as big a leap to VBA as it often seems at first. Depending upon what you're trying to do, of course!    

           

          Kruncher

          • VBA & Monarch
            Melissa2 _

            Tom,

             

            Do you need to use each file right away under the new name or do you store the files for a limited time and then open say 30 day's worth of ATM transaction reports and work with the data...There is a simple way to have excel look in a folder and list all of the files in that folder and then rename them all at once using a new format...You can even move the files to a different directory at that time....Is that what you want to do?

            email mwhite@yvcu.org[/email]

            Melissa

            • VBA & Monarch
              Tom2 _

              Thanks Melissa & Datakruncher!  I am following advice to date... VBA Excel book on the way and extensive review of all web-based VBA examples at various sites.  To answer Melissa's question...

               

              I am looking to concatenate text files within Excel prior to executing VBA Monarch commands(text1.txt+text2.txt...=textresult.txt) based on drop-down variables of YYYY and MM where I have a folder full of YYYYMMAtmDaily.txt files. 

               

              Might be able to skip this step if I knew how to execute Monarch using VBA with multiple files for reports based on the excel input of YYYY and MM.

               

              Thanks again for all the help so far!

              • VBA & Monarch
                RalphB _

                Mark,

                 

                Check out this post to [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000517#000000"]open multiple files[/url] .

                 

                This should give you some ideas as to how to open mulitple files with similar names without appending all of them into one.

                 

                Ralph

                • Re: VBA & Monarch
                  Nick Osdale-Popa

                  Thought I'd rear my ugly head here. (Hi Grant! {waves})

                   

                  So has anyone had trouble automating v10.5 with Excel 2016 VBA?

                  Still using v10.5 due to it's API/COM interface and all the macros I have running against it.

                   

                  Here's the code I have:

                   

                      With mapMonarch

                          sDatabase = msSourcePath & sDatabase

                          sModelFile = msModelPath & msModel

                          LogMessage "Opening Database/Table/Model: " & sDatabase & "/" & msDatabaseTable & "/" & sModelFile

                          'OpenDatabase(stringConnectString,stringPassword, stringTable|View,stringModel)

                          OpenFile = .OpenDatabase(sDatabase, vbNull, msDatabaseTable & "$", sModelFile)

                          If OpenFile = False Then

                              blSuccess = False

                              sProblem = "Error Opening File: " & sDatabase & " (-" & sModelFile & "-)"

                              Err.Raise vbObject + DatabaseErr, "Monarch", sProblem

                          End If

                      End With

                   

                  The issue is with this line:

                          OpenFile = .OpenDatabase(sDatabase, vbNull, msDatabaseTable & "$", sModelFile)


                  The database file (Excel) has 15K rows.

                  In Excel 2007, Monarch opens it without so much as a wink.

                  In Excel 2016, Monarch starts, but just stalls on opening the file. No warnings/errors. It eventually goes into a 'Not responding' status, to which I have to kill the Monarch process.

                   

                  Thoughts?

                  -Nick

                    • Re: VBA & Monarch
                      Grant Perkins

                      Hi Nick!

                       

                      How are things?

                       

                      You've been away too long!

                       

                      And as for raising a post this old .... well, yeah, I know, it relates to older stuff so it's not a bad call.

                       

                      As you will recall I'm no coder. However I do seem to remember seeing some stuff about certain  MS supplied patches being advisable or required for backwards compatibility from Office 16, notably Excel and Access iirc. How that might relate to VB code I'm not sure but with so many under-the-hood changes over the years it's possible that something subtle has changed.

                       

                      Those, of course, are likely to be the products of most interest to us historically.

                       

                      As it's not something I need to be concerned about often AND as it's a bit of a moving target anyway, I don't have any links to share with you. Sorry.

                       

                      However there is so much (some good, some not) that appears if one searches either the MS KBs or more publicly that I think you might find some conformation pretty easily.

                       

                      It's possible, of course, that nothing discussed there matches your problem. Also more than possible you have already undertaken that research direction.

                       

                      Great to hear from you!

                       

                      Grant