Refreshable spreadsheet question - determining DSN used by Excel
Date: Wed, 26 Mar 2008 09:53:56 +1100
Message-ID: <B1C87DCFE2040D41B6F46ADF9F8E4D9C01099A40@CALBBEX01.cal.riotinto.org>
Joe,
There are 2 ways that I know of - neither is ideal.
One is to get regmon from www.sysinternals.com (or Process Monitor) and
set it to filter on msqry32 and capture registry events.
Then use your spreadsheet.
You can search the captured output for instances of
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ and you will find the DSN
names.
But you have to have exercised all the DSNs.
Or in Excel, startup VBE and then from within it choose View, Immediate
Window.
Also show the project explorer and expand the VBAProject to see the name
of all the sheets.
In the immediate window enter
?Sheet1.QueryTables(1).Connection
and press <enter>
(Update the sheet and querytables number as appropriate)
You should get something like this returned:
ODBC;DSN=BDB1;UID=orauser;;SERVER=BDB1;
You can also change the DSN being used - If you type:
Sheet1.QueryTables(1).Connection="ODBC;DSN=BDB2;UID=orauser;;SERVER=BDB2
;"
And press <enter>
The connection is changed.
Confirm by
?Sheet1.QueryTables(1).Connection
and press <enter>
And then run the query and look at the respective databases to confirm the connection is now to the new database.
You could probably write code to loop through all the sheets and connections - please share if you do so.
Regards,
Bruce Reardon
mailto:bruce.reardon_at_riotinto.com
Date: Mon, 24 Mar 2008 13:04:49 -0600
From: "Sweetser, Joe" <JSweetser_at_icat.com>
I have been given a refreshable spreadsheet - and, oh, what a joy, :-) that connects to an Oracle financials database here in the shop.
Windows Env: Excel 2002 running on XP Pro SP2 Oracle Env (though I don't think it's particularly relevant to this question): 9.2.0.8 running on RHEL.
I am trying to determine the *existing* data source the spreadsheet is trying to use and having no luck at all. If I try to refresh the data I get an MS ODBC error saying that the data source name is not found and there is no default driver specified. Fair enough. I believe that since I haven't set up any ODBC connections on my laptop. And when I click OK I get a dialog box that allows me to choose a data source. But is there a way to determine what data source the initial/existing query/connection is using? I'm thinking the long since gone developer of the spreadsheet used a connection s/he had set up on their laptop.
Thanks,
-joe
NOTICE
This e-mail and any attachments are private and confidential and may contain privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments.
This notice should not be removed.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 25 2008 - 17:53:56 CDT