Forum Discussion

kmd's avatar
kmd
Contributor II
1 year ago

Excel XF() functions - problems with Save Offline Copy feature

All,

Does anyone out there have users with massive (thousands of rows and dozens of columns) XFGetCell() workbooks that are seeing issues with the "Save Offline Copy" feature?  (and yes, I know.. they should not be creating these massive workbooks in the first place - that's not what XF() is meant to do)

Here are the symptoms we're having:

  • Regardless of what the seconds setting is (and I'm not even sure this setting applies to anything other than CV connections or quick views) and for some of these users, it's set to the max allowable, the 'save offline copy' often just spins and spins and never completes or eventually the user just has to 'kill' Excel and try again.  Happens at different times so no idea what server activity might be going on at the same time.
  • Save Offline Copy appears to finish but the saved file now contains "#REFRESH" rather than the values 
  • Unable to keep Formatting (this has to be set to False) - loses things like superscript or subscript fonts.

    I have a feeling this has something to do with the built-in behavior with this addin that it wants to refresh everything before saving the offline copy and I suspect it doesn't get to finish that before the save starts to run, but I have no proof of this.  Coupled with this is the fact that many of the cells containing the XF() function ALSO contain vlookup() functions which doesn't help I'm sure.

We have raised a ticket with OS but it's hard to provide them with a replicated sample given the size of these workbooks.

I'm just wondering if anyone else has seen this and what workarounds or resolutions you may have discovered and might be willing to share.

Much thanks in advance for any tips.

Excel Add In

5 Replies

  • aricgresko's avatar
    aricgresko
    Contributor III
    1 year ago

    My organization loves XFGetCells and a lot of our FP&A users have retrieves pulling 25,000-30,000 data cells.  Typically, I recommend two things when facing issues similar to what you're describing. 

    1. Ensure the entire workbook has been refreshed before attempting to save offline copy.  If the entire workbook isn't fully refreshed, it will appear that the offline file saved but you won't get the desired outcome. 
    2. Review your preferences in the Excel Add-in.  The below screenshot of my setup has always worked for saving offline files. 

    Hope this helps! 

    • kmd's avatar
      kmd
      Contributor II
      1 year ago

      Thanks so much for the quick response.  I'm happy to hear we're not the only ones with these massive workbooks and in our case it's FP&A as well.

      I was always under the impression that when saving an offline copy, the addin attempts to refresh the entire workbook one last time before saving anyhow - just wanted to confirm that you're saying we should still do that anyhow because it's possible the offline save isn't finishing the refresh before attempting to save a copy?

      Thanks for the screen capture as well - looking at your preferences, it would appear that you are on a later version than we are.  May I ask what version you're on?  We're still plodding along on 6.8 and I wonder if they made improvements to this offline feature in later versions?  

      I also suggested to my users that it might be a good idea to set their Excel calculation to Manual instead of Automatic because there's an additional checkbox that causes the entire workbook to recalculate.  I'm not sure if they've tested that yet or not.  And I'm not sure how helpful it'll be but I know these folks have combined vlookups() within the same cells as their XF() functions so it's a guess on my part.

      Looking forward to hearing what version you're on because I feel like we're doing what you're suggesting.  On the plus side it gives me greater comfort that this is offline save is doable without issue and maybe we just need that upgrade.

      Thanks again for the great tips!

      • aricgresko's avatar
        aricgresko
        Contributor III
        1 year ago

        Yeah, to confirm, it's recommended to click Refresh Workbook and ensure that all cells are refreshed prior to clicking Save Offline Copy.  
        We are on v7.4 which if I recall correctly, only adds that last preference option as compared to any v6.X.  I'm not sure if any noticeable changes were made, but I've recommended these settings (excluding the last one since it's v7.X related) over the years and it's always been successful, even in the very large data cell workbooks. 

  • Clarke_Hair's avatar
    Clarke_Hair
    Contributor
    3 months ago

    Same issue with us and OS has confirmed it is a bug in our version 9.0.1.  It might have started in 8.5 as I think we had some complaints back then.

    Workaround - Open in Client-Spreadsheet and save offline there.  We were experiencing the issue and it would lock up excel for 5 mintues and then sometimes it would work but most of the time it would not.  In my testing for OS, I found situations where it still worked (but was very slow) and more that did not.  Now that we use the Client to do this, it is back to saving in seconds and working.

    Twist - Most of our users use advanced/new excel functions like XLookup which the spreadsheet tool does not recognize so when opening the file, they get ERROR in all our FXSet fields.  

    Double Workaround - Open Spreadsheet tool, turn Calc Options to Manaul under Formulas, open file (ie it will not refresh the xlookup) and then safe offline.  You will need to refresh and save in normal Excel before doing this.  Pain to have to do all these extra steps, but it works for us with our 50 tab workbooks.

    • kmd's avatar
      kmd
      Contributor II
      3 months ago

      Hi and thanks for this great reply.  We also started seeing these issues in Version 8.  I really appreciate the tips and have passed those on to our users.  Hopefully they will test and come back with favorable results.  I did do some testing a while back with these huge 30 MB Excel XFGETCELL() workbooks and unfortunately didn't see great refresh performance but haven't re-tested in a while so maybe worth running through again.

      Thanks again.