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

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

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

Is this done in Access or SQL? What version of Office are you running. In
Excel 2003 you can actually password protect any editions to your
spreadsheet. I don't know how off the top of my head, but I bet if you went
to an Excel Forum they'd be able to help you there better. This is an Access
forum, and it doesn't seem like your question applies to Access. Good Luck..

"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

I geting the data from SQL, but the same steps are done in side Excel for
both SQL or MS-Access, so the question is the same for either one.

I'm using Excel 2003-SP1

I thought this was an Excel forum, did not know is only for MS-Access, do
you have a link/URL for a Excel forum ?
-------------------------------

"Micah Chaney" wrote:

Is this done in Access or SQL? What version of Office are you running. In
Excel 2003 you can actually password protect any editions to your
spreadsheet. I don't know how off the top of my head, but I bet if you went
to an Excel Forum they'd be able to help you there better. This is an Access
forum, and it doesn't seem like your question applies to Access. Good Luck..

"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

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

Actually I realized that whatever we do in the Excel, the User can open a new
Excel and do a PivotTable-GetExternalData and point to the SQL database-view
and get "all data". I have to put the protection on the database-view itself.
I will do this by adding on the WHERE clause a condition based on the
SYSTEM_USER (the Domain\NT-LoginID of the person accessing the database-view)
Thanks all for your help !!

"Dick Kusleika" wrote:

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