Query of External Data

I'm using a ODBC connection to query a oracle database. If I refresh a query
and email the file to the intended recipients, I can limit what they can see
by changing the query to include/exclude certaing markets.

Is there a way that I can set up the query so the recipient cannot edit the
query and have access to all the data...?

Or is there a way to disconnect the query from the excel file, this way the
query could never be refreshed to the database. We could email them the file
daily, but they wouldn't be able to refresh or change the query(to view
markets they normally wouldn't have access to).

I'm using a pivot table to summarize the data and would prefer to not have
set that up repeatedly.

You can delete the underlying query table.

Manually, or programmatically --
Create a copy of the workbook
Select the sheet that contains the query results
Select a cell in the table
Delete the query table:
Manually -- on the External Data toolbar, click the
Data Range Properties button
Remove the check mark from 'Save query definition'
Click OK
Programmatically -- add code similar to the following:
Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt


Excel GuRu wrote:
> I'm using a ODBC connection to query a oracle database. If I refresh a query
> and email the file to the intended recipients, I can limit what they can see
> by changing the query to include/exclude certaing markets.
>
> Is there a way that I can set up the query so the recipient cannot edit the
> query and have access to all the data...?
>
> Or is there a way to disconnect the query from the excel file, this way the
> query could never be refreshed to the database. We could email them the file
> daily, but they wouldn't be able to refresh or change the query(to view
> markets they normally wouldn't have access to).
>
> I'm using a pivot table to summarize the data and would prefer to not have
> set that up repeatedly.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you Debra.....

"Debra Dalgleish" wrote:

> You can delete the underlying query table.
>
> Manually, or programmatically --
> Create a copy of the workbook
> Select the sheet that contains the query results
> Select a cell in the table
> Delete the query table:
> Manually -- on the External Data toolbar, click the
> Data Range Properties button
> Remove the check mark from 'Save query definition'
> Click OK
> Programmatically -- add code similar to the following:
> Dim qt As QueryTable
> For Each qt In ActiveSheet.QueryTables
> qt.Delete
> Next qt
>
>
> Excel GuRu wrote:
> > I'm using a ODBC connection to query a oracle database. If I refresh a query
> > and email the file to the intended recipients, I can limit what they can see
> > by changing the query to include/exclude certaing markets.
> >
> > Is there a way that I can set up the query so the recipient cannot edit the
> > query and have access to all the data...?
> >
> > Or is there a way to disconnect the query from the excel file, this way the
> > query could never be refreshed to the database. We could email them the file
> > daily, but they wouldn't be able to refresh or change the query(to view
> > markets they normally wouldn't have access to).
> >
> > I'm using a pivot table to summarize the data and would prefer to not have
> > set that up repeatedly.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>