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

[kmd]

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.

[aricgresko · Accepted solution]

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]

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]

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.