Cancel
Showing results for 
Search instead for 
Did you mean: 

Export Orders in Excel using Testlab Automation

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

4 REPLIES

Re: Export Orders in Excel using Testlab Automation

Siemens Genius Siemens Genius
Siemens Genius

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

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

Experimenter
Experimenter
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

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.