4 Replies Latest reply: Sep 15, 2016 10:15 AM by pleen RSS

    Excel formulas not updating

    pleen

      Hi Everyone,

       

      I have 3 summaries from Monarch and 1 table from DataPrep being exported to a single excel file, each on their own tab. Then there is a 5th tab that is summarizing data into more readable formatting and doing simple calculations. My model and exports are working fine but after the data is updated in excel the formulas are not updating. The only way I have found to update the formulas is to copy and paste each of the 4 tabs that were just exported. I have my calculation set to Automatic, even clicking calculate now does not work. I have played around a little bit and cannot figure this out. I am using Monarch 13 complete with DataWatch Server Visual Process to automatically run this process everyday and using Excel 2010.

       

      Has anyone else run into this issue?

      Any help is much appreciated.

       

      Thanks,

      Pat

        • Re: Excel formulas not updating
          Marianna Romero

          Hi Pat,

          The issue might be more on the Excel-side.

          There was an old thread related to this that might be of help - https://community.datawatch.com/message/14755#14755

           

          Hope that helps!

           

          Marianna

            • Re: Excel formulas not updating
              pleen

              Thank you for response, that thread is the same as my issue and gives a good temporary solution to my issue as I want this completely automated without having to open the document.

              I should give more background on my file, it is calculating a running total of a variance each month. So the calculated tab is pulling in the previous day's totals and adding todays totals, which is calculated from the 4 exported tabs. At the end of the month I use this amount, so my goal is to only have to open the file at the end of the month when I need it. Weekends would create an issue with the macro as I am not working to open the file and update the data which would mean the next day is pulling incorrect data.

              One of the posts in that thread mentioned it worked for different version combinations of Monarch and Excel, so if that is true I would think that the export is doing something different than it used to, maybe communicating differently? I have asked people who are extremely knowledgeable in Excel and they didn't understand why either.

               

              Thank you once again,

              Pat

            • Re: Excel formulas not updating
              Tracy Garon Hojka

              Hi Patrick~

               

              I think that code could actually become a permanent solution for you, because I think you can automate the daily file opening by scripting & scheduling it.  That should eliminate your need for weekend updates.  I'm not familiar with Datawatch Server, so I do not know if you can automate this within there but I know you can script this using a Windows batch file and schedule it with Task Scheduler, so I would think this solution has a lot of potential.

               

              As a side note - I really don't think it has to do with the export specifically, but rather the way Excel interprets the data and the calculations.  I've seem some very odd issues crop up in Excel working with SQL, so I could believe that it is the culprit here too.  All it takes for Excel to "break" is an update and Microsoft is notorious for sending out faulty updates.  Just my opinion...

               

              Thanks!  Tracy