external data source security

hi all, wants to ask for some help... i have an excel pivot report, this pivot based on an external data source (sql server via odbc). i want to make this pivot to refresh everytime i open the report, so i choose the save password option for external data. and because i save the password, everybody can change the query for this pivot, is there any way that i can get automatically refresh pivot and also secure pivot?


plss helpp..

tx

Hi,

I think the best way is to do this in two staps while updating via a macro.

First you query the data via ODBC to a seperate sheet.
Then you protect the sheet from editing (could hide it additionally)
Then the macro should update the pivottabel.

Good luck.

regards,
Arien

Hi Arien, thanks for your tips. the data cannot be retrieve into another sheet , because the data is exceeded 65600 row (max row in excel).. thats why quite confusing...

Hi Jsy,

OK. I see what you mean.
You may want to look at the following MVP - Debra Dalgleish
site where you will find vba code to restrict you pivottable.

http://contextures.com/xlPivot12.html

This may be easier.

regards,
Arien

Hi Arien,,

thanks for the sollution... appreciate it a lot!


best regards,
jsy