I would like to export some data on an Excel file.
I have created a Macro which works well, but I would prefere to use a journal (more convenient, and easier to use on every NX version).
To do so, inside a same loop, I use the function " AskElementResult" for each element (about 456 000 elements), and then, I write every result (stress on element) in a excel file, so that every result "k" is written in the cell (k,1).
My problem is that this script takes a very long time...Indeed, it seems that functions used to write results on an Excel file are very time consuming (when I skip the writing procedure, the script is a lot quicker).
On the contrary, when I use the macro, those similar actions (exportation on excel) are made very quickly (maybe, with a Macro, all results are written at the same time, and not row after row ? Just a thought...).
So, I was wondering if it was possible to call a macro inside a journal script ? (in order to use some parts of my macro inside a global journal), in order to same time.
Solved! Go to Solution.
I want to be sure that I understand your question. Is the macro that you mentioned an NX macro - a file with the extension .macro? If so, there is no supported way to launch that from an NX Journal.
Sometimes when people are talking about Excel, they refer to VB "macros", so I just wanted to be sure about which one you are asking about.
Can you post the relevant parts of your code? Perhaps there are some easy optimizations that you could make when writing the results.
I'd recommend saving the results in memory then writing them all to Excel in one shot (if you are not already doing this).
Thanks for your response !
Yes, I'm talking about NX macro ('.macro' file), automatically written (by using the 'recording option') when I do some actions in the NX user interface.
Ok, it is too bad, so I will try to understand why it takes so long to write data on my Excel File using NX journal whereas NX Macro is much quicker.
Thanks for helping me !
Indeed, the code is quit long, but to sum up :
- I choose a specific solution and a specific group
- Inside a loop on loadcases, I ask results (precisely the 6 components (XX, YY, ZZ, YZ, XZ, XY) for stresses on 2D elements, and for both Top and Bottom locations, so a total of 12 components)
- I write them all (12 components), row after row, in a Excel file, inside the initial loop.
--> Each component is written in a column (so 12 columns)
--> Each row refers to a 2D element (if there are n elements in the group, n rows are written).
--> Each Excel file refers to a loadcase (if there are n loadcases, I create n Excel files).
The general construction of my code is implemented like this :
Dim Solutions() As CAE.SimSolution = simPart.Simulation.Solutions.ToArray
' CHOOSE THE SOLUTION
Dim Sim_solution As CAE.SimSolution = Solutions(0)
Dim Loadcases() As CAE.BaseLoadcase = Sim_result.GetLoadcases
For Each loadcase As CAE.BaseLoadcase In Sim_result.GetLoadcases
index = index + 1
For Each iteration As CAE.BaseIteration In loadcase.GetIterations
' OPEN EXCEL APPLICATION
Dim objExcel = CreateObject("Excel.Application")
Dim Classeur = objExcel.Workbooks.Add
Classeur.SaveAs("C:\temp\" + Out + "\" + "Case_" + CStr(index))
Dim objWorkbook = objExcel.Workbooks.Open("C:\temp\" + Out + "\" + "Case_" + CStr(index))
objExcel.Visible = False
objExcel.ActiveSheet.Name = "Results"
objExcel.ScreenUpdating = False
Dim caeGroups() As CAE.CaeGroup = simPart.CaeGroups.ToArray
' CHOOSE THE GROUP TO CONSIDER
Dim caegroup As CAE.CaeGroup = CaeGroups(0)
' THE GROUP IS MADE UP OF 2D ELEMENTS
' EACH ITEM IS AN ELEMENT
Dim groupItems() As TaggedObject = caegroup.GetEntities
For Each grpItem As Taggedobject In groupItems
Dim element As CAE.FEElement = CType(grpItem, CAE.FEElement)
Dim elementIndex As Integer = Sim_result.AskElementIndex(element.Label)
S1 = resultAccess.AskElementResult(elementIndex)
' HERE IS AN EXAMPLE, I DO THE SAME FOR 12 OTHER COMPONENTS
' WRITTING RESULTS ON EXCEL FILE
' ROW i AND COLUMN 1 (first component)
objExcel.Cells(i, 1).numberformat = "0.00"
objExcel.Cells(i, 1).value = S1
i = i + 1
objExcel.ScreenUpdating = True
objWorkbook = Nothing
objExcel = Nothing
Have you tried/thought of writing all your data to a array N*13 (elm ID,S1,S2,..S12) on "the fly"
and when you have process all the items in the group , write the data to xls at once?
Thanks a lot for your response !
Yes, maybe it could work, I will try to perform as you said.
Have you any idea to write the array at once in the Excel file ? How can I match/assign each cell to array index at once ?
If you do a websearch on ".net write array to excel", you will get many results; here is one of the top results:
Also, I would suggest applying the number format to the entire range rather than to each cell individually.