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 |
All times are GMT +1. The time now is 09:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com