Does anyone know of a way to automatically resize/recalulate a spreadsheet upon opening it?
This KB is helpful.
KB01035 - Recalculate a PI DataLink function without using the right-click menu
If you want to run VBA when Excel open, you can do by Workbook_Open().
Thanks for the link. I am trying to use this to resize and recalculate a spreadsheet with multiple Pi tags.
The tags are in raw 8 and the data goes until raw 1495.
When I set up the vba code above, do I refernece the very last cell in the array?
Also, when I run the code as it is in the link, it stops at 'automationObject.SelectRange' command,
Are you able to tell me why this is?
Thanks in advance.
You can reference first cell in the array.
In the KB it explain "How to select all cells in array" and "How to resize the array"
.SelectRange is only select. (You don't need to run this code)
You can use A4 for Set MyRange. Following is example to do ResizeRange.
Private Sub Workbook_Open()
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns("PI DataLink")
Set automationObject = addIn.Object
Dim MyRange As Range
Set MyRange = Range("A4")
Thanks for your help. When I run the code as per the example, I get the follwoing errorat the resize function.
Is the error saying that the 'automationObject' variable is not set? As i understand this is done above with the 'Set' statement. I am not sure where I am going wrong with this?
Please could you comment?
I could see Range("D8:AT8") in your code.
Is it only one array?
You can use Range("D8") too.
Also which PI DataLink version do you use?
This method is supported by PI DataLink 2014 or later.
If you use prior than 2014, you can use pidldialogs.
Code is following.
Range("a1").select ' select a cell that contains the DataLink array
This requires a reference to PIDLDialogs. From the Visual Basic Editor, choose Tools>References Then, check by PIDLDialogs.
Thanks for that. we do have Datalink 2010. This is the issue.
The reason why i had the range ("D8:AT8") was because there is two pi query outputs side by side in this array.
The code you provided works well with PIDLDialogs referenced.
Thanks and have a nice day.
Retrieving data ...