Wondering if the right click reclaculate Datalink functionality is exposed so that I could write code to recalculate and resize my array of datalink calls?
Mark BowlesProcess Innovations Inc
You can use volatile functions in your arrays (or insert volatile functions artificially for recalculation purposes) and then recalculate the Excel spreadsheet.
To see some more on the volatile functions and how you can use them in your PI DataLink arrays please see the user manual where it talks about "Triggered recalculation".
To update a spreadsheet programmatically you can use ActiveSheet.Calculate in VBA or similar methods for other languages. To see more on this please refer to this page.
Ok..understood. I will check out the user manual. I find it somewhat curious that this appears to not be a common concern as I have only run across a couple of references to it. This is also hard to catch...it is inconsistent. Sometime the entire array will return and sometimes not.....The sheet updates with Activesheet.Calculate and the various other flavors...but appears to do nothing to expand arrays of datalink calls.
I will look into the Volatile excel date functionality and see if it will apply here.
To add on to Ahmad's suggestion, I'd like to refer you to one of our KB Article on Tech Support website here.
The 1st part is of the article is already mentioned by Ahmad. The second part ("Using DataLink features") gives you a link to an sample worksheet that calls DataLink's "dlresize" function in Excel VBA to recalculate and resize the DataLink Array.
For this method, You need to add a reference in VBA to PIDLDialogs.xla.
A simple code snippet is like the following
Private Sub cmdRecalc_Click()Sheet1.Range("B8:G8").Select 'select cells in the other arrayCall dlresize 'recalculate and resize the array End Sub
Private Sub cmdRecalc_Click()ActiveSheet.Select 'select the current active worksheetCall dlresize 'recalculate and resize the array End Sub
Yes!!! This is what I was looking for. Thank you both
Han, worked great. Although the link to your KBA doesn't work anymore.
Also, I would add that the PIDLDialog.xla file is in c:\Program Files\PIPC\Excel.
Please accept our apologies. This thread is more than 6 years old and hence it can happen that a link is not working anymore.
As a recent resource on the subject, please refer to KB01035 - Programmatically Recalculate (Resize) DataLink Functions
Retrieving data ...