Javier

I don't think there's any built-in way to do what you want.

One option is to disable the Wizard control. If the user can't get to the
wizard, then he can't easily change the external data query. You might
disable those controls in the Workbook_Activate and _Deactivate events.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

Dim ctlWizard As CommandBarControls
Dim ctlIndex As CommandBarControl

Set ctlWizard = Application.CommandBars.FindControls(, 457)

For Each ctlIndex In ctlWizard
ctlIndex.Enabled = False
Next ctlIndex

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

Dim ctlWizard As CommandBarControls
Dim ctlIndex As CommandBarControl

Set ctlWizard = Application.CommandBars.FindControls(, 457)

For Each ctlIndex In ctlWizard
ctlIndex.Enabled = True
Next ctlIndex

End Sub

Another tact may be to test the CommandText property of the PT every time
the worksheet is calculated (you can use the PT events if you have XP or
newer, but you have to use the Calculate event for earlier versions)

Private Sub Worksheet_Calculate()

Dim sSql As String
Dim pc As PivotCache

'Hardcode the sql you want
sSql = "SELECT TblTest.TestId, TblTest.TestName, " & _
"TblTest.TestNum FROM `C:\Dick\Temp`.TblTest TblTest"

Set pc = Me.PivotTables(1).PivotCache

'Make sure the sql wasn't changed
If pc.CommandText < sSql Then
pc.CommandText = sSql
pc.Refresh
End If

End Sub

This example uses an Access database because I don't have SQLServer loaded
here. If you know what your CommandText property is supposed to look like,
then use it. If not, go to the Immediate Window and type

?Sheet1.PivotTables(1).PivotCache.CommandText

and see what it returns.

There's really know reason that you can't use both of these methods
together. No matter what you do, if the user cracks your vba password, this
is all worthless. If the data is so critical that you can't afford to have
someone else see it, then unfortunately you can't use Excel because the
protection just isn't strong enough. It sounds like you just need to thwart
some efforts by mischievous users though. These two methods should help in
that regard.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Javier wrote:
This is what I did:

1) I create a database view on the SQL server (very long and complex
query) 2) I open Excel, create a PivotTable and indicate "External
Data Source" then click "Get Data", choose a DSN, select View, bring
all fields, Filter by "Company = Microsoft"

I want to distribute that Excel, but the problem is that anyone can
edit the Filter and get all the data.

Once the Filter (where clause) is defined inside Excel, I want to be
able to password protect that screen so no User can change it.

Regards,

Javier


"Dick Kusleika" wrote:

Javier

You have a pivot table based on external data, or you have an
external data table in Excel and pivot table based on that?

How are the legitimate changes to the WHERE clause being made now?
Are you only concerned that all the records may be shown, or do you
want to limit wha't shown to one of a few criteria?

I think this can be done, but depending on how you answer the above,
I don't know how easy it will be. You should be able to check the
WHERE clause in code to make sure it's only returning what you want
it to return.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Javier wrote:
I'm geting external data for a Pivot Table using the 'Get External
Data' option, this works fine and retrieves the information from a
SQL 2000 database view.

In order to don't retrieve "all" the records, I use a filter (ie.
where Company equal to Microsoft), that way the User only sees that
amount of data that he is supposed to see.

The problem I have is that a power User that knows excel will be
able to edit/remove that filter and see "all" data.

Of course I can create different database views (ie. one for each
filter) and apply proper access rights to that view (ie. trusted
connection) to the proper User.

But that means high maintenance.

Is there a way to password protect (or something like that) the
actual query/filter so no power User can see/change it ?

Or is there another way that you can suggest to approach this
problem.

Please advise.

Thanks,

Javier