Export Orders in Excel using Testlab Automation

Valued Contributor
Valued Contributor

Hello everyone,

 

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):

LMSWOTOrdnung.PNGExcelWOTOrdnung.PNG

 

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

Best regards

16 REPLIES 16

Re: Export Orders in Excel using Testlab Automation

Siemens Legend Siemens Legend
Siemens Legend

Dear,

 

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.

Re: Export Orders in Excel using Testlab Automation

Valued Contributor
Valued Contributor
Thank you very much! Converting the values from MKS did the trick for me. The y-values were off because I confused the x- and z- direction. So now I have the correct data. Thanks!

Re: Export Orders in Excel using Testlab Automation

Creator
Creator
Hi,bro, I'm a beginner of testlab automation. the function I want to implement is to export data to EXCEL and draw pictures.I tried all the ways to find similar cases, but I couldn't find them.There are many users like you on the forum who gave some code, but I don't know how to connect with excel. I want to derive the average sound power. Can you give me a hint? Thank you very much.

Re: Export Orders in Excel using Testlab Automation

Valued Contributor
Valued Contributor

Hello,

 

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.Automat.PNG

 

 

 

Then you need to check the LMS Test.Lab library.

Automation.PNG

 

 

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. 

Re: Export Orders in Excel using Testlab Automation

I am new to this forum, Would you please explain me what is Testlab Automation?

Re: Export Orders in Excel using Testlab Automation

Experimenter
Experimenter

 

 

Hi brockerdocker,

 

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:

 

Userform.PNG

 

 

Re: Export Orders in Excel using Testlab Automation

Experimenter
Experimenter

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 ?)

Sheets("PicSheet").Activate

ActiveSheet.Paste

End Sub

Re: Export Orders in Excel using Testlab Automation

Experimenter
Experimenter

Hi NVH_Group,

 

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

X and Y Values.PNGThe two ListBoxes contain the x and y values.

So you can use the Data with Excel as well.

Is this the information you wanted?

Re: Export Orders in Excel using Testlab Automation

Experimenter
Experimenter
Hi, voth
Thanks for your hint, copy the block data into excel by codes is very useful especially for recalculate evaluating indicator lines.
I want to write some code to give the test report timely after NVH test. So I have to connect Testlab and excel/ppt , copy the display into MS is a necessary step . Maybe I need to understanding the function of copy order and the type of variable.