AnsweredAssumed Answered

Using Ctrl F to Change Array With DataLink

Question asked by dcunningham on Sep 24, 2015
Latest reply on Apr 20, 2016 by dcunningham

Hi Everyone,


I'm attempting to change a large number of queries in PI DataLink at the same time, but am encountering an issue. What I'm doing is the following, I hit "Ctrl+~" to enter formula viewing mode (I don't know if that's the correct vernacular, that's what I've taken to calling it), I select the range of cells that contain the queries I'd like to amend, open up the Find and Replace dialogue, and search out the part of the function I want to change and replace it with what I want it to be now. What happens is I get a #VALUE error after I exit formula viewing mode. Originally I assumed that I had made an error, but it seems as though if I click on the output cell of the query and click apply from the PI DataLink dialogue pane that appears, suddenly my #VALUE error disappears and the formula works. 


So, I guess my questions are: (1) Is there a better way to change multiple queries at once? and (2) If I'm doing it in a good way already, how do I mimic the clicking of "Apply" in the DataLink dialogue panel all at once so that I don't have to manual go through every query and do this process? I've tried hitting Ctrl+Shift+Alt+F9, and it doesn't appear to work. I have the workbook set to Manual Calculation mode because I'm querying large volumes of data and it takes a long time for the calculations to process so while I'm tinkering it's easier for me to just manually recalculate certain values.


Just in case I'm not being quite clear, here's what I'm trying to do in pictures:


I hit Ctrl+~ to get this:

I hit Ctrl+F to get the find and replace dialogue box and replace input this:

Hit replace to get this:

And then hit Ctrl+~ again to see:

But, if I click the cell:

You can see that the 1mo info I added with Find and Replace is in the Time Interval field, so I hit apply and:



It worked!


The end goal is to change a large volume of calculated data queries so that they all have 1mo time intervals attached to them as a way to get around a cap on the number of data retrievals our IT department put when they set up our PI Server. They're reluctant to change the system to accommodate larger retrievals, so this is my best option for now.


Thanks for any help you all can provide!






NOTE: I'm using Excel 2013 with PI DataLink 2015.