I wish to know how to only update the information on 1 page in a spreadsheet that has 31 pages in the workbook. I.e., 1 Page for each day.
I wish to know how to only update the information on 1 page in a spreadsheet that has 31 pages in the workbook. I.e., 1 Page for each day of the month. Each page collects information for 33 items every hour.
Currently pressing Ctrl + Alt + Shift + F9 updates all 31 pages as well as any other spreadsheets I have open using PI & it takes a few minutes each time. I’m currently running a Macro for this.
Also is there a way that these updates can be done automatically at around 5 minutes past the hour?
Any help in these matters will be greatly appreciated.
Kenneth Barber commented
First of all, UserVoice is for suggestions, not questions. Second of all, you didn't even make your post under PI DataLink.
Your spreadsheet is poorly designed. You can have a single file with a single sheet where you choose the day rather than having the scattered across multiple sheets and files. However, if you insist on keeping daily sheets within monthly files, I would recommend creating a Power Query table in a separate sheet that grabs the entire month of data using PI SQL Client, which in turn requires that "PI SQL Data Access Server (RTQP Engine)" be installed on the PI Asset Framework server. In the daily sheets, use INDEX(,MATCH(,,0)) to look up the data from the Power Query table. PI SQL Client is much faster at grabbing large amounts of data than PI DataLink, so you won't have to worry about the speed of grabbing an entire month of data on each refresh.
To update the spreadsheet automatically, you need to automate Excel. You can use a PowerShell script run by the Task Scheduler. The PowerShell script should open your workbook in Excel, run a macro, save the workbook, and close Excel.