Pivot Table Connection String - How to hide it?

[fijieddie]

Hi

We have several pivot table reports (Excel 97) that we distribute to our
users. The reports connect to an SQL Server 2000 database. Some of the
reports use trusted connection (windows) and others use non-trusted (SQL
login). These reports can easily be opened using VBA or in Excel 2007 to view
the connection string (settings). How can we protect or hide these settings?
(especially the username and password). Any help would be appreciated.

Thanks

 

[white605]

Not sure about 97 but if your code is in a project in 2003, from the vba editor - tools - vba project properties - Protection tab - Lock project for viewing - with a password.

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[fijieddie]

No we don't use Project 2003. It is a simple Pivot Table which can be called from Visual Basic or Access 97 (for refreshing of query and connection string). A user also can double click it and just view the report and move around the columns etc.

 

[white605]

Sorry if my description was too short.
In MS Excel 2003 if you are creating the pivot table programttically with VBA or an autoexecute macro you can do as i said above to protect the source.
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[fijieddie]

Thanks for the response - no, we are not using VBA but simple pivot table using the wizard. The VBA is used only when opening the report through the code (refreshing the query, connection string).

 

[straybullet]

What I do with several pivot table reports I send out daily is actually copy the area the PT is in and paste special values. The file size is much smaller but it can't be manipulated by the end user - useful in my case.

Let them hate - so long as they fear... Lucius Accius

 

[fijieddie]

But the new file that you paste the PT area to - it becomes like a normal sheet, rite? So its no longer like a pivot table where you can drag columns, re-order, etc?

Thanks for the help