For a little while now I’ve been looking for hands-off ways to auto-update Excel files with data from our BW system, without having to use Broadcasting or any other additional servers.
After reading through numerous posts and forums from a lot of different sources, and a bunch of trial and error I have a technique working reliably both both Analysis for Office and BEX.
Kutools for Excel includes more than 120 handy Excel tools. Get it Now After installing Kutools for Excel, please do as follows:1.
Click Kutools Find and Break broken Links, see screenshot:2.
Some are accessed via BEX and others via Analysis for Office 2.3.
A lot of forum posts and blogs may say use Broadcasting or Web reporting, but thats not something we are looking at investing time in at this stage.
We could force it to update immediately but that'd slow it down considerably. Type = mso Linked OLEObject Then On Error Resume Next ' Verify that file exists If Len(Dir$(Replace(o Sh. Source Full Name, s Old Path, s New Path))) 0 Then o Sh.
Then somebody comes along and installs a new server with a new name.
We are running the following : While the title sounds intimidating, once you have the basics working its quite straight forward.
We will be using a VBscript to run and control Excel, and then make a call to a VBA Macro within the Excel file to refresh the data.
Instead we can do it using simple, reliable and efficient scripting from within Windows (and Excel) itself.
The added bonus is this method doesn’t require additional systems or servers.