Excel Error? Data Link Failure

[Hirono]

I need your assistance again.

It may be excel problem, but if I can fix it on Monarch, will you please advise me?

I want to see data on Sheet1 Cell A1 whatever What Sheet2 Cell A1 has. So, I set formula on Sheet1 Cell A1 as


=Sheet2!A1

But, it is not working :-)

Sheet2 is data source updated by performing "Project Exports" via Monarch.

Always data is updated by--

"Add Data To File" when Output File Exists,
"Overwrite Existing Table" when Output Talbes Exist

So, Sheet2 is data source and always updated. Sheet1 is easy-viewable data sheet with pivot table and charts. One of good Excel features that if I mark on pivot table option, "refresh data when the document is opened," Sheet1 pivot table is always refreshed whenever the excel spread sheet is opened. Always, updated information is ready on Sheet1 if perform data export via Monarch.

But, except the cell A1 on Sheet1 with that simple formula.

After data is exported Monarch on Sheet2, I see the record from previous data. (So, technically it is no longer the equal anymore! Sheet2!A1 data value is not matching!)

Why the cell remembers old data? To resolve this problem, I must re-write formula again on Sheet1. So I just set macro to solve the problem. But, still I have to run the macro.... This is additional step that I want to eliminate.

Do you have any ideas? Thank you. Hirono

[Data Kruncher]

It took a couple of exports and Excel configurations to duplicate your results, Hirono but I was able to do so.

I think that this problem relates more to Excel 2003 (haven't tried it with 2007) and how it recalculates workbooks than it is a problem with how Monarch handles or creates the Excel export when appending data to an existing file and overwriting worksheets.

You're correct in thinking that your solution lies in creating a macro, but you won't have to run it manually yourself every time that you open the file. So it should be an easy fix that will not become more work later.

What you want to do is create a short macro that runs automatically every time that the file is opened. To do so, go to the Visual Basic Editor. Now find the item that is named ThisWorkbook in the project window. This is probably on the left side of the editor window. Double click the ThisWorkbook item.

Copy the following program code and paste it into the editor in the main document window:



Private Sub Workbook_Open()
Application.CalculateFull
End Sub


Literally, this means that every time the the workbook is opened, Excel will perform a full recalculation of every formula in the workbook. This is a more extensive recalculation than normal, or if you just hit the F9 key to force Excel to recalculate.

Now save and close the file. When you open it, Excel may prompt you to Enable or Disable macros. Choose Enable and you should find that the value in Sheet1 cell A1 is now always reflecting the correct value in Sheet2 cell A1.

My testing shows that the macro code that you have added should safely stay in the file when you replace the Sheet2 data with future Monarch project exports.

Your use of a formula that automatically links to freshly exported Monarch data is a great use of the combination of Excel and Monarch together, and is something that I've been using in my work for a long time. Further, when you combine the automatic refresh for a pivot table with updated Monarch data (even though you can now export pivot tables directly with Monarch v10), you have the makings for a solid foundation for great dashboard style Excel reports.

Please advise if this resolves your problem.

Kruncher

[Hirono]

Dear Data Kruncher, thank you for your advise. It worked. :-)

Definitely, your suggested solution eliminates the extra step.

I am happy to see the result on my report. Thank you again! Hirono

[zlatan24]

As a matter of fact exist some tools for resolving troubles with excel documents and one of them consist of only a tool. It was my theory and not more. But some times this application saved my time. It would be helpful solution for solving varied kinds of excel problems - repair recovered Excel file (http://www.recoverytoolbox.com/recover_excel_files.html).

[pams_cf]

I also have this problem. I have one machine with Monarch 10.5 and Excel 2010. When I export, the summary updates properly in 2010. If I open the same spreadsheet in 2003, the summary links do not update. I then exported my info using Monarch 9.0 and the 2003 spreadsheet updated properly. This leaves me to believe there is something different in 10.5 with this kind of export. This was the first time I used Monarch 10.5.