Import_run_export script

[srAndritz]

I want to import a value from an excell sheet (lets say from column A row one)to a predefined expression (called "SmStroke") update the model according to this newly imported value (a constraint in the model is defined by "SmStroke"), export a number of measurements also defined as expressions back to the excell sheet to row 1 columns B,C,D... and so on. Then import the value from Column A row 2, update model, export values to row 2 columns B,C,D.. and so on, then do the same for column A row 3....

Can this be done?

 

[cowski]

thread561-384497
The thread above contains some code similar to what you are trying to do, perhaps it will be a starting point.

p.s. It appears that the OP deleted some of his own posts, so the thread in its current state makes for some strange reading; hopefully, the code is enough to get you started.

www.nxjournaling.com

 

[srAndritz]

Thank you! I will give this a try and come back if (when) i run into problems

 

[srAndritz]

The code you linked to works like a charm, thank you! I have managed to get the model to take a value from the excel sheet, put it into an expression, update the model according to the new expression, get the next value from the spreadsheet, update end so on. I have spent half a day looking for a way to export a measurement but can't seem to find a way. All i need now is a subroutine at the end of each loop that exports measurements to row "i" in the spreadsheet.
I don't expect someone to do all the work for me just to be pointed in the right direction and i will try to cut it together myself

 

[srAndritz]

Got it working! Running a test tonight as i expect it to take 4-5hours..
I hope it's ok to post altered code from NXJournaling.com?

'spreadsheet_update

'NXJournaling.com
'April 13, 2015
'
'Update a model from a range of values in a spreadsheet.
'The specified expression in the part file must make use of the
'ug_cell_read or ug_excel_read function.
'The journal will loop through the specified rows in the specified column
'and attempt to update the model.
'If any update error occurs, the corresponding cell reference is written to the listing window.

Option Strict Off
Imports System
Imports System.Text.RegularExpressions
Imports NXOpen
Imports NXOpen.UF

Module Module1

    Sub Main()

        Dim theSession As Session = Session.GetSession()
        Dim theUfSession As UFSession = UFSession.GetUFSession()
        If IsNothing(theSession.Parts.BaseWork) Then
            'active part required
            Return
        End If

        Dim objExcel = CreateObject("Excel.Application")  
        If objExcel Is Nothing Then  
            MsgBox("Could not start Excel, this journal will now exit.", MsgBoxStyle.Critical, "Error")  
            Exit Sub  
        End If  

        Dim excelFile As String = "c:\my excel file location"  
        If Not IO.File.Exists(excelFile) Then  
            MsgBox("Specified file not found, journal will now exit.", MsgBoxStyle.Critical, "File not found.")  
            Exit Sub  
        End If  

        Dim objWorkbook = objExcel.Workbooks.Open(excelFile)  
        If objWorkbook Is Nothing Then  
            MsgBox("Could not open Excel file, journal will now exit.", MsgBoxStyle.Critical, "Error")  
            Exit Sub  
        End If  

        Dim workPart As Part = theSession.Parts.Work
        Dim lw As ListingWindow = theSession.ListingWindow
        lw.Open()

        Const undoMarkName As String = "update expression from spreadsheet"
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, undoMarkName)

        '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
        'Change the values of the following constants to suit your needs
        '  specify spreadsheet column
        Const spreadsheetColumn As String = "A"
        '  specify spreadsheet rows
        Const spreadsheetRowStart As Integer = 4
        Const spreadsheetRowEnd As Integer = 6
        '  specify expression name in .prt file that references
        '  spreadsheet value
        Const expressionName As String = "my expression to vary"
        '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

        Dim cellRef As String = spreadsheetColumn & spreadsheetRowStart.ToString

        Dim theExpression As Expression
        Try
            theExpression = workPart.Expressions.FindObject(expressionName)
        Catch ex As NXException
            If ex.ErrorCode = 3520016 Then
                'no expression found with given name
                lw.WriteLine("expression '" & expressionName & "' not found, journal exiting")
                Return
            Else
                lw.WriteLine(ex.ErrorCode & ": " & ex.Message)
            End If
        End Try

        If (theExpression.RightHandSide.ToUpper.Contains("UG_CELL_READ")) Or _
            (theExpression.RightHandSide.ToUpper.Contains("UG_EXCEL_READ")) Then
            'expression references a spreadsheet
        Else
            'cannot update cell
            lw.WriteLine("expression does not reference a spreadsheet cell, journal exiting")
            Return
        End If

        Dim strRegex As String = "(.*?ug_(?:cell|excel)_read\s*\(\s*(?<sheet>"".*?"")\s*,\s*"")(?<cell>.*?)(""\s*\)(.*))"
        Dim regexOptions As RegexOptions = regexOptions.IgnoreCase Or regexOptions.Multiline

        For i As Integer = spreadsheetRowStart To spreadsheetRowEnd

            cellRef = spreadsheetColumn & i.ToString

            Dim theMatches As MatchCollection = Regex.Matches(theExpression.RightHandSide, strRegex, regexOptions)
            Dim newFormula As String = theMatches(0).Groups(1).Value & cellRef & theMatches(0).Groups(2).Value

            theSession.UpdateManager.ClearErrorList()
            Dim markId2 As Session.UndoMarkId
            markId2 = theSession.SetUndoMark(Session.MarkVisibility.Invisible, "Expression edit")

            theExpression.RightHandSide = newFormula

            Dim nErrs1 As Integer

            Try
                nErrs1 = theSession.UpdateManager.DoUpdate(markId2)

            Catch ex As NXException
                lw.WriteLine("** Update Error with value in cell: " & cellRef)
                lw.WriteLine("** " & ex.ErrorCode & ": " & ex.Message)
                lw.WriteLine("")

            End Try
        Dim output1 As Expression = Nothing
        output1 = workPart.Expressions.FindObject("output1")   

        Dim output2 As Expression = Nothing
        output2 = workPart.Expressions.FindObject("output2") 

        Dim output3 As Expression = Nothing
        output3 = workPart.Expressions.FindObject("output3") 

        Dim output4 As Expression = Nothing
        output4 = workPart.Expressions.FindObject("output4")  
 		
        objExcel.visible = True  
        objExcel.cells(i, 2).value = (output1.Value)  
	objExcel.cells(i, 3).value = (output2.Value) 
        objExcel.cells(i, 4).value = (output3.Value) 
        objExcel.cells(i, 5).value = (output4.Value) 

        Next

        lw.Close()

    End Sub


    Public Function GetUnloadOption(ByVal dummy As String) As Integer

        'Unloads the image immediately after execution within NX
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately

    End Function

End Module

Thanks ALOT for your help

 

[cowski]

Are these measurements existing measure features that you need to export the updated values, or do you need to create a new measurement each time?

www.nxjournaling.com

 

[srAndritz]

These are expressions that have their value defined as a measurement, for instance the distance between 2 surfaces or an angle between 2 objects that change for each iteration, the measurements update and i do not need to create new measurements each time.

 

[cowski]

The code below shows how to get the value of a particular expression. Replace p2421 with the expression name that you are interested in.

Option Strict Off
Imports System
Imports NXOpen

Module Module1

    Sub Main()

        Dim theSession As Session = Session.GetSession()
        If IsNothing(theSession.Parts.BaseWork) Then
            'active part required
            Return
        End If

        Dim workPart As Part = theSession.Parts.Work
        Dim lw As ListingWindow = theSession.ListingWindow
        lw.Open()

        Dim myExp As Expression
        Try
            myExp = workPart.Expressions.FindObject("p2421")
        Catch ex As NXException
            lw.WriteLine(ex.ErrorCode & ": " & ex.Message)
            Return
        End Try

        Dim expValue As String
        expValue = myExp.Value.ToString & " " & myExp.Units.Abbreviation

        lw.WriteLine("expression value: " & expValue)

        lw.Close()

    End Sub


    Public Function GetUnloadOption(ByVal dummy As String) As Integer

        'Unloads the image when the NX session terminates
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.AtTermination

    End Function

End Module

www.nxjournaling.com

 

[srAndritz]

I believe that is the code i cut some of the

" Dim output1 As Expression = Nothing
output1 = workPart.Expressions.FindObject("output1")

Dim output2 As Expression = Nothing
output2 = workPart.Expressions.FindObject("output2")

Dim output3 As Expression = Nothing
output3 = workPart.Expressions.FindObject("output3")

Dim output4 As Expression = Nothing
output4 = workPart.Expressions.FindObject("output4")

objExcel.visible = True
objExcel.cells(i, 2).value = (output1.Value)
objExcel.cells(i, 3).value = (output2.Value)
objExcel.cells(i, 4).value = (output3.Value)
objExcel.cells(i, 5).value = (output4.Value) "

from

Thank you so much for your help!