Programmatically refreshing a data source for a pivot table (olap)
 

Hi all,

I have a spreadsheet that has an olap connection. When the spreadsheet
loads an ugly error message is displayed before the login is asked for.

I have been asked to make this a little more user friendly.

I hoped to be able to catch the error, cancel it, load a form where the user
can enter login details, make the connection and refresh the data source.

Could anyone point me in the direction of doing this please?

My primary concerns are;
1. catching the error i.e. when the user tries to refresh the data source
(this includes changing a filter).
2. programatically refreshing the connection using given username and
password details.

Any help would be much appreciated. I'm using SQL 2005 with Excel 2007 but
it generally needs to work in most versions of Excel.

Regards,
Phil