Change Excel file expression references

References, please

The NX expression system has functions to read values from external spreadsheet (Excel) files. These functions, as of this writing (NX 8.5), include:

  • ug_cell_hlookup
  • ug_cell_read
  • ug_cell_vlookup
  • ug_excel_read
  • ug_list_hlookup
  • ug_list_vlookup
  • ug_read_fixed_list
  • ug_read_list

These functions come in handy, and it is easy to use lots of them in a single file. However, there will come a day when you want to move the existing Excel file, or reference a different one. When that day comes, you will be tasked with changing all of those expression references by hand: a tedious and error prone process. A perfect time to use a journal!

The journal below will search through the current work part's expressions looking for any of the above expression functions. For each unique Excel file reference found, it will prompt you to choose a replacement Excel file. Note: if you have multiple Excel references and only want to replace some of them, you can choose the original file as the replacement for those you want to keep unchanged.

' NXJournaling.com
' June 11, 2012
' NX 7.5.0.32
' find expressions that reference an external excel file, prompt for replacement file
' eng-tips thread561-323557
 
' updated August 23, 2013
'    added new excel expression functions found in NX 8 and NX 8.5
'    additional error handling
 
Option Strict Off
Imports System
Imports NXOpen
Imports System.Text.RegularExpressions
Imports System.Collections.Generic
Imports System.IO
Imports System.Windows.Forms
 
Module Module1
 
    Sub Main()
 
        Dim theSession As Session = Session.GetSession()
        Dim workPart As Part = theSession.Parts.Work
        Dim displayPart As Part = theSession.Parts.Display
 
        Dim lw As ListingWindow = theSession.ListingWindow
        Dim i As Integer = 0
 
        Dim re As New Regex("ug_(excel_read|cell_read|cell_hlookup|cell_vlookup|list_hlookup|list_vlookup|read_fixed_list|read_list)\s?\(\s?\""(.+\.xls(x|m)?)")
        Dim excelFile As String
        Dim newExcelFile As String
        Dim originalExcelFiles As New List(Of String)
        Dim myExcelFiles As New Dictionary(Of String, String)
 
        Dim myExpressions As Expression()
        Dim myExcelExpressions As New Dictionary(Of Expression, String)
 
        myExpressions = workPart.Expressions.ToArray()
 
        lw.Open()
        For Each myExp As Expression In myExpressions
            If re.IsMatch(myExp.RightHandSide) Then
                excelFile = re.Match(myExp.RightHandSide, 0).Groups(2).ToString
                myExcelExpressions.Add(myExp, excelFile)
                If Not originalExcelFiles.Contains(excelFile) Then
                    originalExcelFiles.Add(excelFile)
                End If
                i += 1
            End If
        Next
 
        For Each Str As String In originalExcelFiles
            newExcelFile = ReplacementFile(Str)
            If newExcelFile <> "" Then
                myExcelFiles.Add(Str, newExcelFile)
            Else
                MsgBox("Cancel pressed, journal exiting.", MsgBoxStyle.OkOnly, "Journal canceled")
                Exit Sub
            End If
        Next
 
        'Dim pair As KeyValuePair(Of Expression, String)
        For Each pair As KeyValuePair(Of Expression, String) In myExcelExpressions
            Try
                lw.WriteLine("Old: " & pair.Key.Name & " = " & pair.Key.RightHandSide)
                pair.Key.RightHandSide = Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value))
                lw.WriteLine("New: " & pair.Key.Name & " = " & Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value)))
                lw.WriteLine("")
 
            Catch ex As NXException
                If ex.ErrorCode = 3270010 Then
                    MessageBox.Show("The value type read from the Excel file does not match the expression type" & ControlChars.CrLf & ControlChars.CrLf & _
                                    pair.Key.Name & " will be skipped", "Datatype Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    'lw.WriteLine(pair.Key.Name & " = " & pair.Key.RightHandSide)
                    lw.WriteLine("replacement Excel file: " & myExcelFiles(pair.Value))
                    lw.WriteLine("skipped: data type in new excel file does not match data type of expression")
                    lw.WriteLine("")
 
                Else
                    MessageBox.Show("An unexpected error occurred." & ControlChars.CrLf & ControlChars.CrLf & ex.Message, "Error: " & ex.ErrorCode, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    'lw.WriteLine(pair.Key.Name & " = " & pair.Key.RightHandSide)
                    lw.WriteLine("replacement Excel file: " & myExcelFiles(pair.Value))
                    lw.WriteLine("skipped: unexpected error, " & ex.Message)
                    lw.WriteLine("")
 
                End If
            End Try
 
        Next
 
        If i = 0 Then
            lw.WriteLine("No expression names matched the specified regular expression")
        End If
 
        lw.WriteLine("Expression processing finished.")
 
        lw.Close()
 
    End Sub
 
    Function ReplacementFile(ByVal oldFile As String) As String
 
        Dim fdlg As OpenFileDialog = New OpenFileDialog()
        fdlg.Title = "Select replacement for: " & oldFile
        Dim dir As String
        dir = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        fdlg.InitialDirectory = dir
        fdlg.Filter = "Excel Files(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"
        fdlg.FilterIndex = 2
        fdlg.RestoreDirectory = True
        If fdlg.ShowDialog() = DialogResult.OK Then
            Return fdlg.FileName
        Else
            Return ""
        End If
 
    End Function
 
    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

Comments

This is almost what I needed.
Is it possible to use this in an assembly and change the references of every part with 1 journal?

Or is it easier to use 1 expression for the path and link that expression to every part?

You could combine this journal with the one that processes the assembly components:
http://nxjournaling.com/?q=content/creating-subroutine-process-all-compo...

Using one linked expression may be easier to manage, it really depends on your workflow, design intent, preferences, etc etc.

Thanks for you solution, but I couldn't figure out how to make this work.

Do you have a suggestion how to combine this two journals?
Or how to make one expression changer with windows?

Try the code below.


'NXJournaling.com
'May 23, 2014
'change excel file references in each part of assembly
'http://nxjournaling.com/?q=comment/889#comment-889
'http://nxjournaling.com/?q=content/change-excel-file-expression-references
 
Option Strict Off
Imports System
Imports System.Collections.Generic
Imports NXOpen
Imports NXOpen.UF
Imports NXOpen.Assemblies
Imports System.Text.RegularExpressions
Imports System.IO
Imports System.Windows.Forms
 
Module Module1
 
    Dim theSession As Session = Session.GetSession()
    Dim workPart As Part = theSession.Parts.Work
    Dim displayPart As Part = theSession.Parts.Display
    Dim lw As ListingWindow = theSession.ListingWindow
 
    Sub Main()
 
        If IsNothing(theSession.Parts.Work) Then
            'active part required
            Return
        End If
 
        lw.Open()
 
        Dim NxjAsmInfo As New NXJ_Assembly_info
        NxjAsmInfo.Part = displayPart
 
        Dim myPart As Part = displayPart
        ReplaceReference()
 
        For Each tempPart As Part In NxjAsmInfo.AllUniqueParts
            ChangeDisplayPart(tempPart)
            ReplaceReference()
 
        Next
 
        ChangeDisplayPart(myPart)
 
        lw.Close()
 
    End Sub
 
    Public Sub ChangeDisplayPart(ByVal comp As Component)
 
        'make the given component the display part
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Invisible, "Display Part")
 
        Dim compPart As Part = comp.Prototype.OwningPart
        Try
            Dim partLoadStatus1 As PartLoadStatus
            Dim status1 As PartCollection.SdpsStatus
            status1 = theSession.Parts.SetDisplay(compPart, False, False, partLoadStatus1)
 
            workPart = theSession.Parts.Work
            displayPart = theSession.Parts.Display
            partLoadStatus1.Dispose()
        Catch ex As Exception
            lw.WriteLine("error opening part: " & compPart.FullPath)
            lw.WriteLine(ex.Message)
            theSession.UndoToMark(markId1, "Display Part")
        Finally
            theSession.DeleteUndoMark(markId1, "Display Part")
        End Try
 
    End Sub
 
    Public Sub ChangeDisplayPart(ByVal myPart As Part)
 
        'make the given component the display part
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Invisible, "Display Part")
 
        Try
            Dim partLoadStatus1 As PartLoadStatus
            Dim status1 As PartCollection.SdpsStatus
            status1 = theSession.Parts.SetDisplay(myPart, False, False, partLoadStatus1)
 
            workPart = theSession.Parts.Work
            displayPart = theSession.Parts.Display
            partLoadStatus1.Dispose()
        Catch ex As Exception
            lw.WriteLine("error opening part: " & myPart.FullPath)
            lw.WriteLine(ex.Message)
            theSession.UndoToMark(markId1, "Display Part")
        Finally
            theSession.DeleteUndoMark(markId1, "Display Part")
        End Try
 
    End Sub
 
    Sub ReplaceReference()
 
        Dim i As Integer = 0
 
        Dim re As New Regex("ug_(excel_read|cell_read|cell_hlookup|cell_vlookup|list_hlookup|list_vlookup|read_fixed_list|read_list)\s?\(\s?\""(.+\.xls(x|m)?)")
        Dim excelFile As String
        Dim newExcelFile As String
        Dim originalExcelFiles As New List(Of String)
        Dim myExcelFiles As New Dictionary(Of String, String)
 
        Dim myExpressions As Expression()
        Dim myExcelExpressions As New Dictionary(Of Expression, String)
 
        lw.WriteLine("Part: " & workPart.FullPath)
 
        myExpressions = workPart.Expressions.ToArray()
 
        lw.Open()
        For Each myExp As Expression In myExpressions
            If re.IsMatch(myExp.RightHandSide) Then
                excelFile = re.Match(myExp.RightHandSide, 0).Groups(2).ToString
                myExcelExpressions.Add(myExp, excelFile)
                If Not originalExcelFiles.Contains(excelFile) Then
                    originalExcelFiles.Add(excelFile)
                End If
                i += 1
            End If
        Next
 
        For Each Str As String In originalExcelFiles
            newExcelFile = ReplacementFile(Str, workPart.Leaf)
            If newExcelFile <> "" Then
                myExcelFiles.Add(Str, newExcelFile)
            Else
                MsgBox("Cancel pressed, journal exiting.", MsgBoxStyle.OkOnly, "Journal canceled")
                Exit Sub
            End If
        Next
 
        For Each pair As KeyValuePair(Of Expression, String) In myExcelExpressions
            Try
                lw.WriteLine("Old: " & pair.Key.Name & " = " & pair.Key.RightHandSide)
                pair.Key.RightHandSide = Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value))
                lw.WriteLine("New: " & pair.Key.Name & " = " & Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value)))
                lw.WriteLine("")
 
            Catch ex As NXException
                If ex.ErrorCode = 3270010 Then
                    MessageBox.Show("The value type read from the Excel file does not match the expression type" & ControlChars.CrLf & ControlChars.CrLf & _
                                    pair.Key.Name & " will be skipped", "Datatype Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    'lw.WriteLine(pair.Key.Name & " = " & pair.Key.RightHandSide)
                    lw.WriteLine("replacement Excel file: " & myExcelFiles(pair.Value))
                    lw.WriteLine("skipped: data type in new excel file does not match data type of expression")
                    lw.WriteLine("")
 
                Else
                    MessageBox.Show("An unexpected error occurred." & ControlChars.CrLf & ControlChars.CrLf & ex.Message, "Error: " & ex.ErrorCode, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    'lw.WriteLine(pair.Key.Name & " = " & pair.Key.RightHandSide)
                    lw.WriteLine("replacement Excel file: " & myExcelFiles(pair.Value))
                    lw.WriteLine("skipped: unexpected error, " & ex.Message)
                    lw.WriteLine("")
 
                End If
            End Try
 
        Next
 
        If i = 0 Then
            lw.WriteLine("No expression names matched the specified regular expression")
        End If
 
        lw.WriteLine("")
 
    End Sub
 
    Function ReplacementFile(ByVal oldFile As String, ByVal inPart As String) As String
 
        Dim fdlg As OpenFileDialog = New OpenFileDialog()
        fdlg.Title = "Select replacement for: " & oldFile & " in part: " & inPart
        Dim dir As String
        dir = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        fdlg.InitialDirectory = dir
        fdlg.Filter = "Excel Files(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"
        fdlg.FilterIndex = 2
        fdlg.RestoreDirectory = True
        If fdlg.ShowDialog() = DialogResult.OK Then
            Return fdlg.FileName
        Else
            Return ""
        End If
 
    End Function
 
    Public Function GetUnloadOption(ByVal dummy As String) As Integer
 
        'Unloads the image when the NX session terminates
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.AtTermination
 
        '----Other unload options-------
        'Unloads the image immediately after execution within NX
        'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately
 
        'Unloads the image explicitly, via an unload dialog
        'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Explicitly
        '-------------------------------
 
    End Function
 
End Module
 
 
 
 
 
 
Public Class NXJ_Assembly_info
 
#Region "Private Variables"
 
    Private Const Version As String = "0.1.1"
 
    Private _theSession As Session = Session.GetSession()
    Private _theUfSession As UFSession = UFSession.GetUFSession
 
    Private _components As New List(Of Assemblies.Component)
    Private _uniqueParts As New List(Of Part)
    Private _allComponents As New List(Of Assemblies.Component)
    Private _allUniqueParts As New List(Of Part)
    Private _notLoaded As New List(Of String)
 
    Private lg As LogFile = _theSession.LogFile
 
#End Region
 
#Region "Properties"
 
    Private _isTCRunning As Boolean
    Public ReadOnly Property IsTCRunning() As Boolean
        Get
            Return _isTCRunning
        End Get
    End Property
 
    Private _thePart As Part = Nothing
    Public Property Part() As Part
        Get
            Return _thePart
        End Get
        Set(ByVal value As Part)
            _thePart = value
            'Me.GetInfo()
            Me.GetAllInfo()
        End Set
    End Property
 
    Public ReadOnly Property AllComponents() As List(Of Component)
        Get
            Return _allComponents
        End Get
    End Property
 
    Public ReadOnly Property AllUniqueParts() As List(Of Part)
        Get
            Return _allUniqueParts
        End Get
    End Property
 
    Public ReadOnly Property Components As List(Of Component)
        Get
            Return _components
        End Get
    End Property
 
    Public ReadOnly Property UniqueParts As List(Of Part)
        Get
            Return _uniqueParts
        End Get
    End Property
 
    Public ReadOnly Property NotLoaded As List(Of String)
        Get
            Return _notLoaded
        End Get
    End Property
 
#End Region
 
    Public Sub New()
 
        lg.WriteLine("")
        lg.WriteLine("~ NXJournaling.com: NXJ_Assembly_info object created ~")
        lg.WriteLine("  ~~ Version: " & Version & " ~~")
        lg.WriteLine("  ~~ Timestamp of run: " & DateTime.Now.ToString & " ~~")
        lg.WriteLine("NXJ_Assembly_info Sub New()")
 
        'determine if we are running under TC or native
        _theUfSession.UF.IsUgmanagerActive(_isTCRunning)
        lg.WriteLine("IsTcRunning: " & _isTCRunning.ToString)
 
        lg.WriteLine("exiting Sub New")
        lg.WriteLine("")
 
 
    End Sub
 
    Private Sub GetAllInfo()
 
        'get all component info from assembly (all levels)
        lg.WriteLine("Sub GetAllInfo()")
 
        Try
            Dim c As ComponentAssembly = Part.ComponentAssembly
            If Not IsNothing(c.RootComponent) Then
                '*** insert code to process 'root component' (assembly file)
                lg.WriteLine("  part has components")
                '*** end of code to process root component
                lg.WriteLine("  calling GetAllComponentChildren")
                GetAllComponentChildren(c.RootComponent)
            Else
                '*** insert code to process piece part, part has no components
                lg.WriteLine("  part has no components")
            End If
        Catch ex As NXException
            lg.WriteLine("Sub GetAllInfo error: " & ex.ErrorCode)
            lg.WriteLine("  " & ex.Message)
        End Try
 
        lg.WriteLine("exiting Sub GetAllInfo()")
 
    End Sub
 
    Private Sub GetAllComponentChildren(ByVal comp As Component)
 
        For Each child As Component In comp.GetChildren()
            lg.WriteLine(child.DisplayName)
            '*** insert code to process component or subassembly
 
            If Me.LoadComponent(child) Then
 
                _allComponents.Add(child)
 
                Dim tempPart As Part = child.Prototype.OwningPart
                If Not _allUniqueParts.Contains(tempPart) Then
                    _allUniqueParts.Add(tempPart)
                End If
 
            Else
                'component could not be loaded
            End If
            '*** end of code to process component or subassembly
            If child.GetChildren.Length <> 0 Then
                '*** this is a subassembly, add code specific to subassemblies
 
                '*** end of code to process subassembly
            Else
                'this component has no children (it is a leaf node)
                'add any code specific to bottom level components
 
            End If
            Me.GetAllComponentChildren(child)
        Next
    End Sub
 
 
    Private Sub GetInfo()
 
        'get top level component info from assembly (no recursion)
        lg.WriteLine("Sub GetInfo()")
 
        Try
            Dim c As ComponentAssembly = Part.ComponentAssembly
            If Not IsNothing(c.RootComponent) Then
                '*** insert code to process 'root component' (assembly file)
                lg.WriteLine("  part has components")
                '*** end of code to process root component
                lg.WriteLine("  calling GetComponentChildren")
                Me.GetComponentChildren(c.RootComponent)
            Else
                '*** insert code to process piece part, part has no components
                lg.WriteLine("  part has no components")
            End If
        Catch ex As NXException
            lg.WriteLine("Sub GetInfo error: " & ex.ErrorCode)
            lg.WriteLine("  " & ex.Message)
        End Try
 
        lg.WriteLine("exiting GetInfo()")
 
    End Sub
 
    Private Sub GetComponentChildren(ByVal comp As Component)
 
        For Each child As Component In comp.GetChildren()
            '*** insert code to process component or subassembly
            _components.Add(child)
            Dim tempPart As Part = child.Prototype.OwningPart
            If Not _uniqueParts.Contains(tempPart) Then
                _uniqueParts.Add(tempPart)
            End If
            '*** end of code to process component or subassembly
            If child.GetChildren.Length <> 0 Then
                '*** this is a subassembly, add code specific to subassemblies
 
                '*** end of code to process subassembly
            Else
                'this component has no children (it is a leaf node)
                'add any code specific to bottom level components
 
            End If
        Next
    End Sub
 
    Private Function LoadComponent(ByVal theComponent As Component) As Boolean
 
        lg.WriteLine("Sub LoadComponent()")
 
        Dim thePart As Part = theComponent.Prototype.OwningPart
 
        Dim partName As String = ""
        Dim refsetName As String = ""
        Dim instanceName As String = ""
        Dim origin(2) As Double
        Dim csysMatrix(8) As Double
        Dim transform(3, 3) As Double
 
        Try
            If thePart.IsFullyLoaded Then
                'component is fully loaded
            Else
                'component is partially loaded
            End If
            lg.WriteLine("  component: " & theComponent.DisplayName & " is already partially or fully loaded")
            lg.WriteLine("  return: True")
            lg.WriteLine("exiting Sub LoadComponent()")
            lg.WriteLine("")
            Return True
        Catch ex As NullReferenceException
            'component is not loaded
            Try
                lg.WriteLine("  component not loaded, retrieving part information")
                _theUfSession.Assem.AskComponentData(theComponent.Tag, partName, refsetName, instanceName, origin, csysMatrix, transform)
                lg.WriteLine("  component part file: " & partName)
 
                Dim theLoadStatus As PartLoadStatus
                _theSession.Parts.Open(partName, theLoadStatus)
 
                If theLoadStatus.NumberUnloadedParts > 0 Then
                    If theLoadStatus.NumberUnloadedParts > 1 Then
                        lg.WriteLine("  problem loading " & theLoadStatus.NumberUnloadedParts.ToString & " components")
                    Else
                        lg.WriteLine("  problem loading 1 component")
                    End If
 
                    Dim allReadOnly As Boolean = True
                    For i As Integer = 0 To theLoadStatus.NumberUnloadedParts - 1
                        lg.WriteLine("part name: " & theLoadStatus.GetPartName(i))
                        lg.WriteLine("part status: " & theLoadStatus.GetStatus(i))
                        If theLoadStatus.GetStatus(i) = 641058 Then
                            'read-only warning, file loaded ok
                        Else
                            '641044: file not found
                            allReadOnly = False
                            If Not _notLoaded.Contains(partName) Then
                                _notLoaded.Add(partName)
                            End If
                        End If
                        lg.WriteLine("status description: " & theLoadStatus.GetStatusDescription(i))
                        lg.WriteLine("")
                    Next
                    If allReadOnly Then
                        lg.WriteLine("  'read-only' warnings only")
                        lg.WriteLine("  return: True")
                        Return True
                    Else
                        'warnings other than read-only...
                        lg.WriteLine("  return: False")
                        lg.WriteLine("exiting Sub LoadComponent()")
                        lg.WriteLine("")
                        Return False
                    End If
                Else
                    lg.WriteLine("  component(s) loaded successfully")
                    lg.WriteLine("  return: True")
                    lg.WriteLine("exiting Sub LoadComponent()")
                    lg.WriteLine("")
                    Return True
                End If
 
            Catch ex2 As NXException
                lg.WriteLine("  Load error: " & ex2.Message)
                lg.WriteLine("  error code: " & ex2.ErrorCode)
                lg.WriteLine("  return: False")
                lg.WriteLine("exiting Sub LoadComponent()")
                lg.WriteLine("")
                If ex2.Message.ToLower = "file not found" Then
                    If Not _notLoaded.Contains(partName) Then
                        _notLoaded.Add(partName)
                    End If
                End If
                Return False
            End Try
        Catch ex As NXException
            'unexpected error
            lg.WriteLine("  Error in Sub LoadComponent: " & ex.Message)
            lg.WriteLine("  return: False")
            lg.WriteLine("exiting Sub LoadComponent()")
            lg.WriteLine("")
            Return False
        End Try
 
    End Function
 
End Class

Thanks, It works almost perfect.

But I have 2 more problems/questions;

Is it possible to select 1 excelsheet for every part, so every asm/part has the same excel refence? Because now I have to select a new excelpath for every found part with an excel reference.

Also sometimes I get the error: 'Update undo happend.' and then it will loop through the same part and ask for excelpath every time.

I've not seen the "update undo happened" error in my (admittedly limited) testing. Does it give any other indication of what went wrong (a line number where the error happened, other error text)?

It is possible to get it to prompt once for an excel sheet; the way I see it, the journal will have to loop through the components twice. On the first pass it will gather existing excel references, eliminating any duplicates. Then it would prompt for replacement excel sheets for the references found, and finally loop through the components the 2nd time replacing the excel references.

I'd like to update the journal to do this, but it may be a week or so before I can get back to it. I've given the overall strategy I'd use in case you'd like to modify it yourself.

This happens on a random part.
'Error given in listing window'

Part: A
Changing references...
 
error opening part: B
Update undo happend.
Part: A
Changing references...<code>
 
Thanks for your help.

Has anyone created a journal to replace all component references at once as mentioned above?

EN US