I tried recording a macro but it didn't record that step. The aim is to retrieve a list of event frames in a period, after the user changes the time range.
Please see this.
Eugene Lee - brilliant, thanks. I'll give that a try now.
I have tried this and it works. There is one problem: the act of selecting the formula array makes the formula command bar pop up at the side of the worksheet and it stays there, even if another worksheet/cell is selected. So when the macro finishes and control is restored to the user, it is still visible.
The answer to this (in VBA) is, after the formula has been updated, should be to use the following code:
Application.CommandBars("nameofcommandbar").Visible = False
For the two formulae I am using, "nameofcommandbar" is:
PIEFDat: "Explore Events"
PIAFSearch: "Asset Filter Search"
(The appropriate name for a given formula command bar is shown at the top of the command bar.)
At any rate, this is what is saved if you record a macro while closing the command bar with the close button(x). However it doesn't seem to work when called from VBA: instead the error is raised:
Automation error Unspecified error
Just found this thread from 2014 but it's not good news. Can I add my voice to the clamour of those wanting this fixed?
Is there a reason you can't use the setting of 'Disable automatic task pane display on click'
That is now my only option. And you can always make the pane appear manually using the right-click menu. However the customer may find it annoying to have to keep changing this setting if he prefers it to be on for other reasons.
Of course the Datalink settings cannot be temporarily adjusted by VBA while the macro runs, which would have been an alternative...
Anyway I'll mark your original response as the answer.
I would suggest you to post the idea on our feedback site so that it becomes tracked. I couldn't find that idea there yet.
PI DataLink: Hot (70 ideas) – Customer Feedback for OSIsoft & the PI System
Good idea Eugene, I have just done that.
NB I couldn't use the other possible fix suggested in the thread of making the array oversize. There is no way to know in advance how many event frames will be returned - anything from zero to many thousands.
Retrieving data ...