|
|
Rank: Newbie
Groups: Member
Joined: 4/1/2011
Posts: 2
Location: Dublin, Ireland
|
Hi,
I am an absolute beginner and am looking to create a macro in Excel which uses PI DataLink to populate several columns based on provided tags, start and end dates and a sampling interval.
I am working with PI DataLink Version 4.0.3 and Excel 2007.
My goal here is to have a macro run automatically, every 12hrs (or ideally, continuously) which pulls data in to Excel. Based on the data, I then wish to apply conditions to each data set which if breached, causes Excel to either alarm, highlight in red, or send an email via Outlook. E.g. when each column is populated with it's 72 data, for each column, if a particular value in that column is greater then or less than 3 standard deviations, then it needs to be flagged.
I have a set number of tags (approx 500) which I have entered in a row in Excel, along with a start and end timestamp (using =NOW() and =NOW()-0.5), along with a sampling interval of 10m.
With my very basic knowledge in both PI and Excel, I simply tried to record a macro of my carrying out the data pull manually but of course when run, the macro only goes as far as opening the 'Sampled Data' selection window and it becomes a manual process from there on.
I thought that this seems a very basic operation, and that maybe it had been done before. Maybe I am going about it the wrong way or there is a much simpler way of doing this?
As I say, I have no real experience with PI DataLink except for manually pulling data but if anyone has sucessfully tackled this type of thing before, I would greatly appreciate your help.
Thanks,
TerryP.
|
|
|
|
|
OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System. We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ or contact OSIsoft vCampus for more details.
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 4/6/2011
Posts: 40
Location: Grimsby UK
|
Hi TerryP, I think what you want is just to update the Excel sheet whenever it gets opened by your macro. This can be done by using some VB to recalculate the Datalink function. If you go to the OSIsoft website you will see on the Tech support page that there is a number of downloads; go for the "sample code - VB" section and you should see an entry for an Excel sheet which has VB code that will recalculate a datalink function. If not just copy the link below into your browser and it should take you there (assuming you have a OSIsoft login - if not, just register and try the link again) http://techsupport.osiso...=Sample%20Code%20-%20VB
To turn the cells Red, well you can do that with conditional formatting in Excel. To send an email is a bit more difficult although I'm sure someone on these forums has posted some code on how to do it - it should be fairly easy using VB and Outlook, but I use Lotus Notes and thats a swine to work with. Hope that helps!
|
|
|
Rank: Newbie
Groups: Member
Joined: 4/1/2011
Posts: 2
Location: Dublin, Ireland
|
Hi caffreys_co,
Thanks for this info. i will give it a whirl and see how I get on....I will post the solution once arrived at!!
Thanks again,
TerryP
|
|
|
Rank: Member
Groups: Member
Joined: 11/30/2010
Posts: 15
Location: New York, USA
|
TerryP - caffreys_col linked to some code using the DLResize method which essentially updates (or, recalculates) any array returned by PI datalink functions in Excel, like compressed data, sampled data, etc.
This will allow you to automate keeping data up-to-date, essentially, in Excel.
|
|
|
Rank: Member
Groups: Member
Joined: 5/24/2009
Posts: 11
Location: Wisconsin, USA
|
Here is some Excel VBA code for sending an email through Outlook. It uses a command button to initiate, sounds like you would want to trigger it based on an event. I also had the email addressess saved in a worksheet, but you could just hardcode them. Code:
Private Sub CommandButton2_Click()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strTo As String, strCC As String
Const strErr As String = "Error occurred; automatic notification was not sent.”
Application.ScreenUpdating = False
‘Get email addresses
With Worksheets("Instructions")
For i = 2 To 8 'Covers 7 cells in case others are added
If .Range("AA" & i).Value Like "*@*" Then
strTo = strTo & .Range("AA" & i).Value & ";"
End If
If .Range("AB" & i).Value Like "*@*" Then
strCC = strCC & .Range("AB" & i).Value & ";"
End If
Next i
End With
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo Cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = strTo
.CC = strCC
.Subject = "Your text here"
.Body = "Your text here"
.Send
End With
Set OutMail = Nothing
Cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
If Err <> 0 Then MsgBox Prompt:=strErr, Buttons:=vbExclamation
End Sub
|
|
|
|
Guest
|
