Forum Discussion

kmd's avatar
kmd
Contributor
11 months 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.. t...
Excel Add In
  • aricgresko's avatar
    aricgresko
    11 months 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! 

aricgresko's avatar
aricgresko
Contributor III
11 months 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
    11 months 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
      11 months 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.