I want to enable the automatic update feature in datalink when a workbook is opened without need of doing this manually. Any idea how can I do this?
I am wary of automatically refreshing Excel spreadsheets because eventually they will hang up, but the code below does work. The code sample below includes a 10 minute ("00:10:00") refresh time... As written the time to refresh the data is not accounted for, so the seconds will change depending on the time it takes to recalculate the spreadsheet.
Private Sub Workbook_Open()
in a User Module Called Timer
Application.OnTime Now + TimeValue("00:10:00"), "RunEverySoOften"
Application.StatusBar = "Automatically refreshed at " & Now()
I usually have the opposite problem... I do not want large spreadsheets people send to be to update when I open them...
Since you want the spreadsheet to update whenever the file is opened, then I am assuming the spreadsheet was saved with Excel Calculation Options set to Manual as seen below... If you want PI Datalink to refresh the data on spreadsheet open, then change the Calculation Options to Automatic.
If the file must saved for Manual updates and you want to automatically refresh, then inserting the code snip-it in in the ThisWorkbook module of the spreadsheet will refresh the data on open, but leave the Calculation Options set to Manual so any other changes to the spreadsheet do not trigger data fetches. NOTE: This only works if the workbook is saved as a macro-enabled spreadsheet and the security settings allow macros...
After that it gets interesting because if you have pivot tables or charts that require updating after the data refresh, then there is more code required to handle automatically... But since you only asked about updating data, I will save that code for later...
This feature is not yet available directly via DataLink and has been slated for consideration in future release as detailed here: https://techsupport.osisoft.com/Troubleshooting/Enhancements/26678OSI8
As suggested by Rick above, you can enable this programmatically although the downside is that the workbook must be macro-enabled.
Thanks Rick, Karn.. that will help to calculate when opening the file but it will only do it at that time. I need the workbook to keep updating, that's why I was wondering if there was a way to enable programmatically the auto-update feature.I don't want rely on people to manually pressing the Update button in datalink as they just some time forget about it.
I guess, I can configure a timer and execute the code that Rick is suggesting...
Thanks, very good macro!
Retrieving data ...