|
|
Rank: Member
Groups: Member
Joined: 7/30/2009
Posts: 11
Location: Rochester, NY, USA
|
Hello,
I'm a big fan of this forum. thanks for all the help I've found on your site!
Is it possible, with or without Datalink, to export data from a PB trend to an Excel Spreadsheet using VBA?
Thanks,
John
|
|
|
|
|
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: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 617
Location: Cheshire, United Kingdom.
|
Hi John...welcome to the forum! Indeed it is. Now there are 2 approaches: 1) ProcessBook >= v3.1 has this functionality built in! So very little work involved in this. 2) Trend VBA with Excel Object model. From a trend you can access each trace and get the data. So, first step get each trace and each trace value: Code:Sub GetTrendData()
Dim TheTrend As Trend
Set TheTrend = Symbols("Trend1")
Dim iTrace As Integer, iTraceValue As Integer
For iTrace = 1 To TheTrend.TraceCount
TheTrend.CurrentTrace = iTrace
For iTraceValue = 1 To TheTrend.TraceValuesCount
Dim vValue As Variant, vTime As Variant, vStatus As Variant
vValue = TheTrend.GetTraceValue(iTraceValue, vTime, vStatus)
Debug.Print TheTrend.GetTagName(iTrace); " @ " & vTime & " = " & vValue
Next iTraceValue
Next iTrace
Set TheTrend = Nothing
End Sub Then just use Excel automation to create a new instance, new workbook and worksheet and put the data on there. Something like: Code:Sub GetTrendData()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Dim xlWB As Excel.Workbook
Set xlWB = xlApp.Workbooks.Add
Dim xlWS As Excel.Worksheet
Set xlWS = xlWB.Worksheets("Sheet1")
xlWS.Name = "DataExportFromTrend"
Dim TheTrend As Trend
Set TheTrend = Symbols("Trend1")
Dim iTrace As Integer, iTraceValue As Integer
For iTrace = 1 To TheTrend.TraceCount
TheTrend.CurrentTrace = iTrace
xlWS.Cells(1, (iTrace * 2) - 1).Value = TheTrend.GetTagName(iTrace) & " Timestamp"
xlWS.Cells(1, (iTrace * 2)).Value = TheTrend.GetTagName(iTrace) & " Value"
For iTraceValue = 1 To TheTrend.TraceValuesCount
Dim vValue As Variant, vTime As Variant, vStatus As Variant
vValue = TheTrend.GetTraceValue(iTraceValue, vTime, vStatus)
xlWS.Cells(iTraceValue + 1, (iTrace * 2) - 1).Value = vTime
xlWS.Cells(iTraceValue + 1, (iTrace * 2)).Value = vValue
Debug.Print TheTrend.GetTagName(iTrace); " @ " & vTime & " = " & vValue
Next iTraceValue
Next iTrace
Set TheTrend = Nothing
Call xlWB.SaveAs("C:\Apps\MyDataExport.xls")
xlWB.Close
xlApp.Quit
End Sub Enjoy! Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
Rank: Member
Groups: Member
Joined: 7/30/2009
Posts: 11
Location: Rochester, NY, USA
|
You are quick! Thanks a ton. If you ever find yourself in upstate New York, I owe you a drink.
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 617
Location: Cheshire, United Kingdom.
|
I have worked with ProcessBook for many years and posses the strange ability to recite most of the ProcessBook object & symbol libraries...bit of a geeky thing to be able to do but useful nonetheless. Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
Rank: Newbie
Groups: Member
Joined: 8/26/2011
Posts: 8
Location: California
|
Hi,
I am also interested in exporting data into excel through VBA. I used the code above, but it gives me an error:
Compile Error:
User-defined type not defined.
the error is thrown by the following line:
Dim xlApp As Excel.Application
Thanks for any help.
|
|
|
Rank: Member
Groups: Member
Joined: 7/5/2010
Posts: 27
Location: Texas
|
You'll need to add a reference to the Microsoft Excel 12.0 Object Library (or Excel 14.0, if you're running Office 2010 instead of Office 2007)
In the VBA editor menu bar, click on Tools, then References and scroll down until you find the above entry and then check the checkbox beside it.
If you can't find that entry, you can click on the Browse button on the References form and navigate to
C:\Program Files\Microsoft Office\Office12\Excel.exe (or Office14, as mentioned above)
On a 64-bit version of Windows, you will may have to look in C:\Program Files (x86)\Microsoft Office\Office12\
In general, any time you automate an Office app from ProcessBook (or any other Office app) you will need a Tools | Reference to the object library of the Office app you're automating.
James
|
|
|
|
Guest
|
