5 Replies Latest reply: May 15, 2014 10:01 AM by rconverse _ RSS

    Copy a Value Progammatically

    rconverse _



      I am trying to completely automate a report that I create everyday.  For this particular report, I am only interested in one number.  Is there a way to just copy that value from the table or pass it as a variable as opposed to exporting an excel spreadsheet or access db for one number?  And again I am doing this thorugh VBA, I know how to do it manually. 


      Using Version 8.0 (Not Pro Version)




        • Copy a Value Progammatically
          Data Kruncher

          Sorry Roger, but you'll need to go through a few extra steps (exporting, as you mentioned) instead of copying/pasting a single value. Still, it shouldn't be too onerous, especially as you only want a single value.


          I'll assume that your VBA is with Excel.


          I'd go so far as to create a special model file for this purpose. In the table, hide all of the other unwanted fields. If you're using a filter to isolate your value, save the model with the filter active. Finally, turn off the option to export the field names as the first row of output.


          That way you'll have your have in A1 in the exported worksheet every time.


          Now, as you're exporting the file programmatically, you'll always know the name of your exported xls workbook, so as soon as you're done with the Monarch processing, open the exported workbook, copy the value from A1 to where you want it, and close the exported workbook.




          Workbooks("Programmed Workbook").Worksheets("Sheet1").Range("B3").value = Workbooks("Exported Workbook)").Worksheets("Sheet1").Range("A1").value


          Will that work for you?



            • Copy a Value Progammatically
              rconverse _

              Hi DK,


              Thank you for the response.  I actually am using MS Access, but do know some Excel.  For this particular report, it does have its own model and in fact, the only value it returns is the field that I want.


              Oh well, I will just do as you suggested.


              Thanks again!


                • Copy a Value Progammatically
                  RalphB _

                  Hi Roger,


                  If you are more familiar with Access, you can go ahead and do you scripting in Acess.  The only limitation is that you cannot export out of Monarch into the Access database or Excel spreadsheet you currently have open and running.  You have to export out to a temp file somewhere and then import it into your db.


                  I primarily set up my scripts in Excel like Kruncher but I also have some set up in Access.  If I need the data I just exported out into my Access db, I'll set up a macro or query to import it after Monarch closes.