Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations LittleInch on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

NX JOURNAL TO AUTOMATE PART UPDATION BY READING THRU' SPREADSHEET VALUES 1

Status
Not open for further replies.
mdhanush

mdhanush

Mechanical
Apr 9, 2015
1
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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.

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 Module

www.nxjournaling.com
 
Upvote 0 Downvote
My example only deals with one parameter, it will need to be extended to work with a second one.

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
 
Upvote 0 Downvote
Below is code that works with your example file (2 parameters).

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 Module

www.nxjournaling.com
 
Upvote 0 Downvote
Cowski, You are the Genius. I really impressed with your journals
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

swale2
  • Question
Trigger Journal outside NX
Replies
0
Views
886
swale2
swale2
Forzindi
  • Question
Can we attach a solid body through journal in NX part ?
Replies
1
Views
2K
Bleaker
Bleaker
Explorien86
  • Question
Journal / NX Open contracting options
Replies
0
Views
959
Explorien86
Explorien86
alj722
  • Question
NX Journal - Cylinders from CSV File
Replies
3
Views
1K
alj722
alj722
SBlackBeard
  • Question
NX Journal for PMI and Location
Replies
2
Views
2K
cowski
cowski

Part and Inventory Search

Sponsor

Back
Top Bottom