LinkBack URL
About LinkBacks
Hi All
I have been given an Excel 2003 spreadsheet to support, which has a data range added to it.
How can I check what the data source is? Can I access the properties through VBA?
Hi All
I have been given an Excel 2003 spreadsheet to support, which has a data range added to it.
How can I check what the data source is? Can I access the properties through VBA?
This is a loaded question -
Do you not have access to the original author?
Is the book full of formulas or Macros?
Is there not a clear instruction on how to update said book?
If you could, upload a copy and someone here should be able to tell you if you can identify sources or not
-If you think you are done, Start over - ELeGault
The original creator is no longer with the company..
The workbook does not contain VBA that connects to a data source, and no there is no clear instruction of how to update it.
When right clicking on the data range the 'Data Range Properties' etc become available.. Its a normal data range..
I'm sure i've managed to access the properties of a data range before (many years ago) but I cannot remember how to do it.
We have recently moved to Citrix and MS Query has not been packaged. I'm sure this is only used to build the query, and then the query is hard coded in to the data properties.
Right so if it is a data connection feed. then click inside the table,
Go to the data tab at the top.
Click on Properties
Once on that screen it will show 'Connection' NAME.
To the right of that connection name will be an icon, click on it.
If that is not available (Greyed out) click on connections just above it to see if there are active connections still in the book. From there click on the properties. and you will be on the same referenced location as above...
In here you can go over and view the definition which will house that query you speak of.
Let me know if you still have issues after that, but I think this is what you needed to know to get there.
Data>Connections>Properties>Definition tab of the connection properties>
Or straight to the properties
Click in the tab
Data>Properties>ICON to the Right of Name>Definition tab of the connection properties>
Are there formulae within the data range? If there are click on a cell with a formula in it then click on the Formula tab and click on Trace Precedents. An arrow should be created and possibly with a rectangle at the end. If there isn't a rectangle at the end follow the arrow to its end and that will be the source for that cell. If there is a rectangle at the end of the arrow, click on the arrow and you should be given a dialogue box with at least one reference that looks similar to this [Book1]Sheet2!$F$6 . Select the reference and click OK and you will be taken to the source.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I was thinking the same thing at first but then he mentions that they are on Citrix and that he believes there is a hard coded Query, so I suspect that the answer is to look at the data connection properties. Here you would find the query "Hardcoded" to that workbook.
Is this how MS Query works? I dont think the workbook looks for the MS Query SQL file each time you refresh the data range.. Doesn't it get cached?Originally Posted by ELeGault
How can I get to the data connection properties of the data range?
There are no formulas in the range btw..
Look at my first post. Its on the Data tab up top. Look in there you should find the Query
Right so if it is a data connection feed. then click inside the table,
Go to the data tab at the top.
Click on Properties
Once on that screen it will show 'Connection' NAME.
To the right of that connection name will be an icon, click on it.
If that is not available (Greyed out) click on connections just above it to see if there are active connections still in the book. From there click on the properties. and you will be on the same referenced location as above...
In here you can go over and view the definition which will house that query you speak of.
Let me know if you still have issues after that, but I think this is what you needed to know to get there.
Data>Connections>Properties>Definition tab of the connection properties>
Or straight to the properties
Click in the tab
Data>Properties>ICON to the Right of Name>Definition tab of the connection properties>
Hi, sorry I didn't actually see this post yesterday..
I've checked and I do not have 'Properties' on the data tab.
From the data tab I have...
1. Data>Import External Data>Edit Query
2. Data>Import External Data>Data Range Properties
Edit Query fails as MS Query is not installed?
Data Range Properties does not give me access to any of the connection properties..
Is there any way I can access the information from VBA ?
On the Data tab, click the Connections button and then check the properties of any connections listed there.
Remember what the dormouse said
Feed your head
Hi, I guess it was not obvious in my opening post that I am running Excel 2003.
I have managed to get access to a machine with Excel 2007 and I can now see the connection properties. Thanks.
How can I get to these connection properties when using my Excel 2003 machine ?
You'd need code to see the connection properties:
for example.
Please Login or Register to view this content.
Thanks.. That is what I was looking for.. instantly recognised that...You'd need code to see the connection properties:
for example.Please Login or Register to view this content.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks