Since upgrading to Office 2007 recently (SP1 installed, SP2 being pushed out to end users soon, but I have tested the problem on an SP2 machine and it didn't make any difference), my accounting department has run into a problem with the way our financial statements are currently configured. Here's a very basic run-down of what they do currently:
1) Data is processed in Microsoft Access and exported to ''dump files'' in Microsoft Excel file format.
2) Secondary Microsoft Excel files, formatted as the Financial Statement, link to the ''dump files.''
3) The secondary Microsoft Excel files are then inserted into PowerPoint to create the final presentation.
When a secondary Microsoft Excel file was opened in Excel 2003, the user was alerted that the workbook contains links to other data sources and prompted the user to select whether they want to Update or Don't Update. This is the desired effect, and is essentially no different in Excel 2007.
When a PowerPoint linked to the secondary Excel file was opened in PowerPoint 2003, the user was alerted that the presentation contains links to other files and prompted to Update Links or Cancel. Here's where the problem with PowerPoint 2007 comes in. If the user clicked Update Links in PP2003, they would get the Microsoft Excel prompt, even when Excel wasn't open(!), alerting them that the workbook contains links to other data sources and prompting whether they wanted to update all linked information (Yes or No). If they clicked Yes, the PowerPoint would display the latest data in the linked Excel file. If they clicked No, the file remained static with the data as of the last time the Excel workbooks were updated.
The Problem: In PowerPoint 2007, when a user opens the PPT file they are alerted that the presentation contains links to other files and prompted to Update Links or Cancel, but clicking Update Links no longer brings up the Microsoft Excel prompt asking them to update the workbook links, it simply updates the link to the secondary file but does not update the secondary file to show the latest data in the dump file, which PowerPoint 2003 did.
The only way I can see a user getting around this is to modify Excel settings so that it never prompts the user to update the linked information (automatically updates) via Trusted Locations or other available options. This is not the desired solution, however, since this is application-specific and not workbook-specific. People know not to update links in files that don't belong to them when they view the file, but if we disable the prompt they will be unwittingly updating other people's files when they view them. We also realize there are some changes they can make to their financial statement process to make this work better, but there are hundreds of Excel files that would have to be modified and this will be a very time consuming process once they decide to do it.
Ideally what we need is a method for having PowerPoint 2007 display the prompt when updating linked workbooks just like PowerPoint 2003 did (see attached jpg).
Any thoughts on this? If anyone is interested in seeing some example files I put together of the dump file linked to the statement file linked to the PowerPoint file, let me know. Please note that all files are still in Excel/PowerPoint 2003 format, but I have tried with the new file formats (XLSX, PPTX) and have the same problem.