in the past I have been successfully using Testlab Automation to export Time Data and Autopower Spectras from Stationary Measurements. To calculate the Overall Level from the Autopower Spectra I had to do several corrections(Hanning Window, Energy Spectra, single sided Spectrum) but eventually I had the exact same curve in Excel as in LMS and I also had the exact same Overall Level in Excel as in LMS.
Now I would like to export calculated Orders from LMS in Excel. Sadly I don't get the same values as when I manually copy the data from a picture.
For example I have a 1.5th order: Acceleration Data vs. Rpm. So the Y-axis should be Acceleration and the X-axis should be rpm. That rpm axis stems from Tacho2 input. In LMS everything looks great. When I export the data using LMS Automation I get a similar curve, but it does differ from when I manually copy the values out of a picture into Excel.
In my Excel VBA Scipt I do:
Input: pfad, att, Nummer
Dim Path2 As String Dim Spalte As Variant Dim YUserValues2 As Variant Dim XValues As Variant Dim dataBlock As LMSTestLabAutomation.IBlock2
Dim i as Integer Path2 = pfad & att.Item(Nummer)
Set dataBlock = my_db.GetItem(Path2) XValues = dataBlock.XValues YUserValues2 = dataBlock.UserYValues(LMSTestLabAutomation.CONST_ScaleProperty.Amplitude_Scale) for i = 0 to UBound(XValues) Cells(1,i+2).Value=XValues(i) Cells(1,i+3).Value=YUserValues2(i) Next
Here is what this curves looks like in LMS (top, red) and the exported one in Excel (bottom, blue):
As one can see the X-axis is quite different but also the amplitudes are somewhat off. In both cases I used the same tracking Tacho (Tacho2). So dataBlock.XAxisId gives me "Tacho2" and also dataBlock.XQuantity is "rpm". However the result is wrong and I would like to know what I would have to change to get the same result. Do I have to apply some sort of corrections on the x- and y-axis? I thought that is only needed for the Sum-level but not the Order...
Maybe I should add that the underlyingn time data was filtered by an ISO5349 filter and that before calculationg the order I smoothed the Tacho using the "Smooth" function on the time data of the Tacho. I don't know if that is relevant for this.
Anyway, any help is greatly appreciated
Solved! Go to Solution.
in order to get the correct RPM value you must convert these from MKS -> UserValue. This can be done in 2 ways or
1. rpm = tl.UnitSystem.MKSToUserValue(tl.UnitSystem.QuantityRotationalSpeed, xval[i]); or
2. rpm = (xval[i] * 60) / (2 * Math.PI);
Make also sure that you didn't apply any processing(y-axis processing) on the data in the display, because this is not applied on the data you get from the database.
are you familiar with VBA in Excel? First you should create a new Makro in Excel. Then you need to make sure that the LMS Test.Lab Automation Library is loaded in Excel. I made some screenshots to show how you do that:
My Excel Visual Basic Editor is in German, but you get what I mean.
Then you need to check the LMS Test.Lab library.
After you did this you can open the Test.lab project with your data. With the following minimal working code snippet you can get started in navigating the structure of your LMS file and extract data.
Dim my_db As IDatabase Dim tmp_att As attributeMap Dim att3 as attributeMap Dim att4 as attributeMap Set tl = New LMSTestLabAutomation.Application Set my_db = tl.ActiveBook.Database tmp_s = my_db.ProjectName section_names = List_Sections(my_db) 'read out all existing sections in your project. Set tmp_att = my_db.ElementNames(section_names(0)) 'open first section Path = section_names(0) & "/" & run_name & "/Tracked processing/Fixed sampling/Time/Sections/Overall level" Set att3 = my_db.ElementNames(Path) Path2 = Path & "/" & att3(0) att4 = my_db.ElementType(Path2) Set dataBlock = my_db.GetItem(Path2) Xvalues = dataBlock.Xvalues YRealValues = dataBlock.RealYValues YUserValues = dataBlock.UserYValues(LMSTestLabAutomation.CONST_ScaleProperty.dB_Scale) XLange = dataBlock.XCount label = dataBlock.label YValues = dataBlock.YValues YQuantity = dataBlock.YQuantity XQuantity = dataBlock.XQuantity YAxisId = dataBlock.YAxisId XAxisId = dataBlock.XAxisId LengthX = UBound(Xvalues) Function List_Sections(my_database As IDatabase) As String() Dim my_att As attributeMap Dim result(100) As String jj = 0 Set my_att = my_database.ElementNames("") For i = 1 To my_att.Count tmp_t = my_database.ElementType(my_att.Item(i)) If tmp_t = "Section" Then result(jj) = my_att.Item(i) jj = jj + 1 End If Next If jj > 1 Then jj = jj - 1 ReDim liste(jj) As String For i = 0 To jj liste(i) = result(i) Next Else ReDim liste(1) As String liste(0) = result(0) End If List_Sections = liste End Function
I haven't tested this exact code, but I hope you get a general idea of what you need to to. The function "List_Sections" returns a string array with all the sections in the project.
thank you for your example. It don't work in my case, but it gave me some helpful tips for my own code.
If anyone is interested, here's the code from my userform:
Private Sub CommandButton1_Click() Set TL = New LMSTestLabAutomation.Application 'read out project Name Dim sProjectName As String sProjectName = TL.ActiveBook.ActiveProjectWatch.Data 'give out on gui Label1.Caption = sProjectName End Sub Private Sub CommandButton2_Click() Set TL = New LMSTestLabAutomation.Application 'Example read out Active worksheet name Dim sSheetName As String sSheetName = TL.ActiveBook.ActiveSheetWatch.Data 'give out on gui Label2.Caption = sSheetName End Sub Private Sub CommandButton3_Click() Set TL = New LMSTestLabAutomation.Application TL.ActiveBook.SheetOnTop = "Documentation" End Sub Private Sub CommandButton4_Click() Set TL = New LMSTestLabAutomation.Application TL.ActiveBook.SheetOnTop = "Navigator" End Sub
The userform looks like this:
Hello, how can I copy a Simcenter display combined with data to the excel by VBA codes ?
I write some code as follow:
Private Sub CBY()
Dim TL As New LMSTestLabAutomation.Application
Dim mydb As LMSTestLabAutomation.IDatabase
Dim datawatchPictManag As LMSTestLabAutomation.DataWatch
Dim myPictManager As LMSTestLabAutomation.IPictureManager
Dim Color_1 As LMSTestLabAutomation.IBlock2
Dim mypicture1 As LMSTestLabAutomation.IPicture
Dim mydisplay1 As LMSTestLabAutomation.IDisplay
Dim mycopy As LMSTestLabAutomation.IPicture
Dim n As Single, m%, i%, j%, go1 As String, _
go2 As String, acc As String, sect As String, _
runstyle As String, runname As String, tp As String
Set mydb = TL.ActiveBook.Database
Set datawatchPictManag = TL.ActiveBook.FindDataWatch("Navigator_DataViewing_PictureManager")
Set myPictManager = datawatchPictManag.Data
Set mypicture1 = myPictManager.AddPicture("1x1")
Set mycopy = myPictManager.CopyToClipBoard("1x1_1", mc_eActivePictureEmbedded)
'(the excel execution error,wha's wrong ?)
I´m working with Visual Basic, but I think in general it´s very simialar.
So the IBlock2 supports to read out the X and Y Values by
XValues(ac_nIndex As Integer = 0) As Array
RealYValues As System.Array
So maybe this snippet will help you:
Sub() 'myBlk will contain the x and y values 'mydb is the IDatabase 'get the Data from my section myBlk = my_db.GetItem("Disc/Run 5/FRF/101:+Y/FRF 101:+Y\/101:+Y") 'Dim two Array´s, this can be also managed in one two dimensional array Dim xval As Array Dim yval As Array 'set the Arrays with the x and y values from myBlk xval = myBlk.XValues yval = myBlk.RealYValues 'print the Data in two List boxes ListBox3 and ListBox4. For i = 0 To xval.Length - 1 ListBox3.Items.Add(xval(i).ToString) Next For i = 0 To yval.Length - 1 ListBox4.Items.Add(yval(i).ToString) Next End Sub
So you can use the Data with Excel as well.
Is this the information you wanted?