NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
(OP)
Hi,
Could you please help me out to get thru' this.
I'm having a part file in which all the features are driven by 2 expressed variables (X & Y). These 2 variables are linked from the external spreadsheet thru' "ug_cell_read" function. This spreadsheet is having range of values (thousands of rows) for these 2 variables.
I would like to have a journal to automate the updation of the model by applying the range of values one by one for X & Y to see the part is updating without any errors, as inputting values one by one manually is taking lot of time.
Here is my requirement,
1. Feeding values for X & Y from external spreadsheet one by one within the range I'm specifying, to update the part.
2. In case, for a particular value the part is not able to update the feature(s), I would like the journal to either highlight the respective row(s) or leaving an error message against those values in the spreadsheet.
Please let me know if something is unclear or a sample file would explain the scenario much better.
Your help in this would be highly appreciated.
NOTE: Using NX7.5 and MS office 2010. Soon upgrading to NX 9.0 hopefully from 1st week of May.
Regards,
mdhanush
Could you please help me out to get thru' this.
I'm having a part file in which all the features are driven by 2 expressed variables (X & Y). These 2 variables are linked from the external spreadsheet thru' "ug_cell_read" function. This spreadsheet is having range of values (thousands of rows) for these 2 variables.
I would like to have a journal to automate the updation of the model by applying the range of values one by one for X & Y to see the part is updating without any errors, as inputting values one by one manually is taking lot of time.
Here is my requirement,
1. Feeding values for X & Y from external spreadsheet one by one within the range I'm specifying, to update the part.
2. In case, for a particular value the part is not able to update the feature(s), I would like the journal to either highlight the respective row(s) or leaving an error message against those values in the spreadsheet.
Please let me know if something is unclear or a sample file would explain the scenario much better.
Your help in this would be highly appreciated.
NOTE: Using NX7.5 and MS office 2010. Soon upgrading to NX 9.0 hopefully from 1st week of May.
Regards,
mdhanush





RE: NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
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.
CODE
'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*(?<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 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 Modulewww.nxjournaling.com
RE: NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
However, the error message is puzzling; the line of code in the error message does not match what is posted above. It seems to have deleted part of the regular expression, specifically the angle brackets and the text within them. Perhaps the copy & paste operation "cleaned" them because it thought they were HTML? I'm not sure. Anyway, make sure that the code in your file matches what is posted above.
www.nxjournaling.com
RE: NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
CODE
'spreadsheet_update 'NXJournaling.com 'April 14, 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. 'update: refactor code into subs/functions Option Strict Off Imports System Imports System.Text.RegularExpressions Imports NXOpen Imports NXOpen.UF Module Module2 Dim theSession As Session = Session.GetSession() Dim theUfSession As UFSession = UFSession.GetUFSession() Dim workPart As Part = theSession.Parts.Work Dim lw As ListingWindow = theSession.ListingWindow Sub Main() If IsNothing(theSession.Parts.BaseWork) Then 'active part required Return End If 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 rows Const spreadsheetRowStart As Integer = 2 Const spreadsheetRowEnd As Integer = 10 '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ For i As Integer = spreadsheetRowStart To spreadsheetRowEnd Try UpdateExpressionRef("X", "A", i) Catch ex As NXException If ex.ErrorCode = 3520016 Then 'no expression found with given name lw.WriteLine("expression 'X' not found, journal exiting") Return Else lw.WriteLine(ex.ErrorCode & ": " & ex.Message) End If End Try Try UpdateExpressionRef("Y", "B", i) Catch ex As NXException If ex.ErrorCode = 3520016 Then 'no expression found with given name lw.WriteLine("expression 'Y' not found, journal exiting") Return Else lw.WriteLine(ex.ErrorCode & ": " & ex.Message) End If End Try Try DoUpdate() Catch ex As NXException lw.WriteLine("** Update Error with value in row: " & i.ToString) lw.WriteLine("** " & ex.ErrorCode & ": " & ex.Message) lw.WriteLine("") End Try Next lw.Close() End Sub Sub UpdateExpressionRef(ByVal expressionName As String, ByVal excelColRef As String, ByVal excelRowRef As Long) Dim cellRef As String = excelColRef & excelRowRef.ToString Dim theExpression As Expression Try theExpression = workPart.Expressions.FindObject(expressionName) Catch ex As NXException Throw ex End Try If (theExpression.RightHandSide.ToUpper.Contains("UG_CELL_READ")) Or _ (theExpression.RightHandSide.ToUpper.Contains("UG_EXCEL_READ")) Then 'expression references a spreadsheet 'lw.WriteLine("expression references a spreadsheet cell") 'lw.WriteLine(theExpression.RightHandSide) 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 Dim theMatches As MatchCollection = Regex.Matches(theExpression.RightHandSide, strRegex, regexOptions) 'lw.WriteLine("matches found: " & theMatches.Count.ToString) Dim newFormula As String = theMatches(0).Groups(1).Value & cellRef & theMatches(0).Groups(2).Value theExpression.RightHandSide = newFormula End Sub Sub DoUpdate() theSession.UpdateManager.ClearErrorList() Dim markId2 As Session.UndoMarkId markId2 = theSession.SetUndoMark(Session.MarkVisibility.Invisible, "Model update") Dim nErrs1 As Integer Try nErrs1 = theSession.UpdateManager.DoUpdate(markId2) Catch ex As NXException Throw ex End Try 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 Modulewww.nxjournaling.com
RE: NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
RE: NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES
Glad to help.
www.nxjournaling.com