External Data Refresh (1 Viewer)

[PearlGI]

I have a spreadsheet with an External Data link to another spreadsheet, but the refresh behaves differently on some computers in the office.

1. On some, when you refresh the data, it runs a background refresh and returns the data – great
2. On others, when refreshing, it automatically opens the source data spreadsheet, runs a background refresh, returns the data and then leaves the source file open – bad

All computers using this spreadsheet used to demonstrate the behaviour seen in scenario 1, but have now started behaving like scenario 2.

Does anyone have any suggestions / pointers?
The inconsistency in behaviour on some pc's could possibly be MS patches which have not been applied to all office pc’s yet. However, the main issue is how to return to the correct behaviour?

 

[Rx_]

Subscriber is the Excel that links to the Master to copy the data.
Is Subscriber Excel located on the Workstation or on a Network drive?
Is Master (what is being copied) on a Network drive?

There are many reasons these Subscriber links can hiccup, try to recover but change a property setting.

Start by deleting a copy of the Subscriber on the Workstation, replacing it with a working copy. Does the problem still exist?

We will start there and look at your answers.

I seem to remember a inconsistent network could cause this problem if there was a delay in connecting to the external Excel Data.

 

[PearlGI]

Hi, Thanks for your reply.

Both the Subscriber and Master are located on a network drive.
If we refresh the Subscriber simultaneously on two pc’s (one working correctly and one not), they both return the results at the same time (visually, not measured) and the good/bad behaviour is always consistent, this seems to rule out network inconsistencies.

We have discovered that the pc’s demonstrating the good behaviour have not had recent MS updates applied to them – if this is an MS change, how can the new/bad behaviour be prevented!

 

[Rx_]

The version of the Publisher can cause this.
By default Excel saves a hidden table of the link values for calculation purposes. In some scenarios the values for the external links may not be the same as what was last calculated in the formulas. It is also possible to turn off the feature to store the external link values on an individual workstation / workbook basis in Excel Options.

The fact that workstations are opening from a Network also adds another issue when linked files are involved.
Do this for a test:
Copy the network version of the subscriber onto the hard drive of a workstation's that is misbehaving. Run it from there. Is the same exact problem still there?
A normal Excel file will open fine from the network. The refresh introduces more back-and-forth action on the local workstation plus the networked links. So, this little test is worthwhile.


Not keeping workstations configuration management up to date in my opinion is a security risk. There are threats that can change the workstation MSOFFICE options. The best solution would be for those
responsible for the desktop to uninstall MS Office, reboot, and re-install MS Office with all of he latest updates.

OR - buy Citrix for small business, install one copy of Excel on Citrix and allow all users to run from Citrix. This vastly removes all of the workstation maintenance for each desktop Operating System and Office updates / configuration.

Because the Publisher has embedded file links, one option is to make sure it is compatible with all the other workstation options. There is an update calculation chain that all point back to the Publisher.
Do This test:
Open a blank Excel on a Workstation believed to be the latest and greatest updated version. From that blank version, import all the objects (e.g. worksheets) from the old Publisher into the new version. Use Save as (new publisher name) and save it on the local hard drive (not a network drive).
Take the network Excel Publisher, rename it, then copy the new publisher copy from the local drive and replace the network copy.
Now, do the same with a Subscriber copy.

This process will eliminate the possibility of an issue that comes out from time to time.


Another way to address this is to go around the workstation support staff and identify a business process objective in order to automate that process.
For example: is the subscriber something that typically happens on a schedule every morning? Or is it an ad hoc at the users request several times a day?
Automating the linking process with vba is a bigger step. It is one that puts Excel in control over the built in menu on workstations where configuration management (e.g. upgrades) may be lacking.

The objective of these test is to hopefully find something that causes a different behavior. That can point to the solution.

 

[PearlGI]

Interesting…..

Based on your suggestions, the issued is now fixed. Although I now understand how to fix the issue (if it arises again), I don’t understand why.

The problem can be fixed by opening the Publisher on each pc which has the problem and then re-saving and closing the file.

The interesting thing with doing this, is that after fixing the issue on the problem pc’s, the pc which was functioning correctly, then exhibited the problem behaviour! But by opening and re-saving the Publisher on that pc, it fixed that one. At that point, I expected the other pc’s to have reverted to the problem behaviour – but, no – everything still worked!

Many thanks for your help.

 

[Rx_]

YOU WIN - My 300th THANKS!!! - Now I can cash them in.

Trust me, it is a curse knowing why that worked.
It had to do with network Latency during a user session.
Something I covered years ago in a MS WOSA I & WOSA II certified exam preparation book and interactive prep.

Ancient History: https://www.amazon.com/Readings-Microsoft-Windows-WOSA-Architecture/dp/155615836X
This was the book template, plus my 4,000+ cited references that became an 1,100 page book. The interactive WOSA test-prep was a hit. Back in the mid '90s

Your probably in a very small office environment. If you had many dozens of workstations, there might be some easy automation tricks at night to employ.

So glad it worked! But not surprised.
Was on the phone since 4 AM with a remote session in a big company that is trying to do something innovative with SQL Server and Excel. Couldn't get back to you this morning. Hope your coworkers think your a genius. Because you are for taking such strange advice from a stranger... just don't make a habit of it! LOL

Just a reminder, as the poster, go back to the first one and mark it as Solved.
It will probably help someone else. This problem happens all the time.