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.I thought I’d therefore share the process I use so anyone who has the same struggles as me can get a potential solution from one location.This works for me, but of course may not work for you depending on the setup you have, so mileage may vary. Part 2 of this topic is available here : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2 Part 3 of this topic is available here : Analysis for Office Variables and Filters via VBA Part 4 of this topic is available here : Analysis for Office and Excel VBA – Lessons learnt This guide covers : We have a number of excel files that we wanted to have updated on a regular basis without users having to load the files, log in/connect to BW and then refresh.Maybe they copy all of your files to the new server, but all of your links break because they point to \Old Server\Your Path\ but the files are now on \New Server\Your Path\ This little macro will look for all of the OLE links in your presentation and change \Old Server\ to \New Server\ (and if the folder names changed, it'll fix that too).You'll need to edit the paths it searches for and replaces where indicated below.Suppose you've got a lot of links in a PPT file or files and all the links point to a network share.
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. For each workbook, open it and refresh all links using Active Workbook. As you open the workbook, you will be prompted to confirm if you want to update external source, Application. The Sub Procedure loop through specific folder and find all xls and xlsx files.When we use Excel workbook, sometimes, we may create some external links to other workbooks for the data for some purpose.But how could you break all the links at once without breaking them one by one?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.