September 24, 2007 at 12:47 am
Hi,
I am using Excel 2003 as a client for MS AS 2005. When I open it I have to manually refresh it to get the latest data from the cube. I was thinking about writing a command in an "AutoOpen" procedure to do the tasks, but some users disable the macros on their PCs. Is there an option that would tell Excel to automatically refresh the pivot tables onces the file is opened.
Thanks.
September 24, 2007 at 8:30 pm
Found a partial solution. If you run this macro in a workbook:
Sub RefreshOnOpen()
For Each pc In ActiveWorkbook.PivotCaches
pc.RefreshOnFileOpen = True
Next
End Sub
The workbook will try to refresh all external datasources. The macro needs to be run just one time and can be deleted after running. However ones you open the workbook you get promted to decide if you want to run the refresh. I wish it were transparent to the user.
Anyone knows how to disable this prompt?
September 24, 2007 at 10:38 pm
There is no need to write any code for this. Excel pivot tables have an option to auto refresh when the spreadsheet opens. All you need is to check the box and you are done.
Looking at the macro, I think it probably does exactly the same as this. Not sure about the prompt - we have already moved to Office 2007 so I can't test 2003 functionality.
September 25, 2007 at 7:06 am
"Any way to disable the prompt"
My best and shortest answer is "Practically: No"
as I understand it:
This is a security setting driven by the installation of Excel not the file - which seems sensible. You can explore options that are under "tools->Options->Security [tab]"
One thing you might want to explore, especially if this is an in-company thing, is setting yourself as a trusted publisher. You only have to do it once and after that the users shouldn't get that message.
I have not ever tried this but from my understanding it should meet your needs.
The following link has a great deal of practical information on the subject: http://office.microsoft.com/en-us/excel/HA100341381033.aspx
May 19, 2010 at 10:34 am
This can easily be done.
1.) Exit your version of Excel.
2.) Click Start, click Run, type regedit, and then click OK.
3.) Locate and then click to select one of the following registry keys, depending on the version of the product that you are using:
For Excel 2003, locate and then click to select HKCU\Software\Microsoft\Office\11.0\Excel\Options.
For Excel 2002, locate and then click to select HKCU\Software\Microsoft\Office\10.0\Excel\Options.
For Excel 2000 SR-1 and later versions of Excel 2000, locate and then click to select HKCU\Software\Microsoft\Office\9.0\Excel\Options.
4.) After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD value.
5.) Type QuerySecurity, and then press ENTER.
Right-click QuerySecurity, and then click Modify.
6.) In the Value data box, type a valid value for the functionality that you want, and then click OK.
7.) On the File menu, click Exit to quit Registry Editor.
Value Effect
------------------------------------------------------------
0 Excel prompts you every time that you open a file
containing a query or PivotTable that has been
configured to refresh automatically.
Note: This is the default behavior in Excel 2000 SR-1.
1 Excel will not prompt you when you open a file containing
a query or PivotTable that has been configured to refresh
automatically. Excel will not refresh the query or
PivotTable automatically.
2 Excel will not prompt you when you open a file containing
a query or PivotTable that has been configured to refresh
automatically. Excel will refresh the query or PivotTable
automatically.
Note: This is the default behavior in Excel 97 and earlier
(non-SR-1) versions of Excel 2000.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply