4 Replies Latest reply: May 15, 2014 10:06 AM by pams_cf _ RSS

    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

        • Excel Error?  Data Link Failure
          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 /Bthat 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

          /CODE

           

          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