The journal below will update the formula of an expression that uses the "ug_cell_read" or "ug_excel_read" function. It will loop through the given cells attempting to update the model. If any update errors occur, it will write the corresponding Cell address to the listing window. This code does not use the Excel file directly, it only updates an expression that makes use of a spreadsheet function.
Change the values of the constants defined near the beginning of the journal code to meet your requirements. To extend this code, you could turn it into a function or subroutine and pass in the desired range of cells as parameters to the function/sub.
'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 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 = 1
Const spreadsheetRowEnd As Integer = 5
' specify expression name in .prt file that references
' spreadsheet value
Const expressionName As String = "length"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
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*(?"".*?"")\s*,\s*"")(?.*?)(""\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")
Catch ex As NXException
lw.WriteLine("** Update Error with value in cell: " & cellRef)
lw.WriteLine("** " & ex.ErrorCode & ": " & ex.Message)
lw.WriteLine("")
End Try
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
It seems to me that this would all be *much* simpler if you just put your table of X and Y values in your code file, as shown below. If you just have a static table of numbers, keeping them in an Excel file just makes your life more difficult.
The code below is mostly copied from the previous solution. It uses some SNAP functions, for brevity and comprehensibility, but replacing them by the corresponding NX/Open functions should be straightforward.
Option Infer On
Class MyProgram
Sub Main()
Dim theSession = NXOpen.Session.GetSession()
' Get the relevant expressions
Dim expXname = "the name of the expression driven by the X values" '## Change this ##
Dim expYname = "the name of the expression driven by the Y values" '## Change this ##
Dim expX As Snap.NX.Expression = Snap.NX.Expression.FindByName(expXname)
Dim expY As Snap.NX.Expression = Snap.NX.Expression.FindByName(expYname)
For i = 0 To values.GetLength(1) - 1
theSession.UpdateManager.ClearErrorList()
Dim mark = theSession.SetUndoMark(NXOpen.Session.MarkVisibility.Invisible, "Expression edit")
Try
theSession.UpdateManager.DoUpdate(mark)
Catch ex As NXOpen.NXException
Snap.InfoWindow.WriteLine("** Update Error with value number: " & i)
Snap.InfoWindow.WriteLine("** " & ex.ErrorCode & ": " & ex.Message & vbCr)
End Try
Next
End Sub
' Copy your x and y values to here from your Excel spreadsheet
' Each line has an x-value and a y-value
Dim values As Double(,) =
{
{1, 2.75},
{1, 3.25},
{2, 1.001},
{3.14159, 4.69}
}
re: update from spreadsheet
The journal below will update the formula of an expression that uses the "ug_cell_read" or "ug_excel_read" function. It will loop through the given cells attempting to update the model. If any update errors occur, it will write the corresponding Cell address to the listing window. This code does not use the Excel file directly, it only updates an expression that makes use of a spreadsheet function.
Change the values of the constants defined near the beginning of the journal code to meet your requirements. To extend this code, you could turn it into a function or subroutine and pass in the desired range of cells as parameters to the function/sub.
'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 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 = 1
Const spreadsheetRowEnd As Integer = 5
' specify expression name in .prt file that references
' spreadsheet value
Const expressionName As String = "length"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
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*(?"".*?"")\s*,\s*"")(?| .*?)(""\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
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
Put the X and Y values in your code
It seems to me that this would all be *much* simpler if you just put your table of X and Y values in your code file, as shown below. If you just have a static table of numbers, keeping them in an Excel file just makes your life more difficult.
The code below is mostly copied from the previous solution. It uses some SNAP functions, for brevity and comprehensibility, but replacing them by the corresponding NX/Open functions should be straightforward.
Option Infer On
Class MyProgram
Sub Main()
Dim theSession = NXOpen.Session.GetSession()
' Get the relevant expressions
Dim expXname = "the name of the expression driven by the X values" '## Change this ##
Dim expYname = "the name of the expression driven by the Y values" '## Change this ##
Dim expX As Snap.NX.Expression = Snap.NX.Expression.FindByName(expXname)
Dim expY As Snap.NX.Expression = Snap.NX.Expression.FindByName(expYname)
For i = 0 To values.GetLength(1) - 1
theSession.UpdateManager.ClearErrorList()
Dim mark = theSession.SetUndoMark(NXOpen.Session.MarkVisibility.Invisible, "Expression edit")
expX.RightHandSide = values(i, 0).ToString
expY.RightHandSide = values(i, 1).ToString
Try
theSession.UpdateManager.DoUpdate(mark)
Catch ex As NXOpen.NXException
Snap.InfoWindow.WriteLine("** Update Error with value number: " & i)
Snap.InfoWindow.WriteLine("** " & ex.ErrorCode & ": " & ex.Message & vbCr)
End Try
Next
End Sub
' Copy your x and y values to here from your Excel spreadsheet
' Each line has an x-value and a y-value
Dim values As Double(,) =
{
{1, 2.75},
{1, 3.25},
{2, 1.001},
{3.14159, 4.69}
}
End Class