02-14-2024 06:35 AM
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:
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.
Solved! Go to Solution.
02-14-2024 08:01 AM
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!
02-14-2024 08:01 AM
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!
02-14-2024 12:02 PM
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!
02-14-2024 01:03 PM
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.