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,
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...