Change Excel file expression references

Submitted by NXJournaling on Fri, 08/23/2013 - 08:37

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

nielsvt - Thu, 05/15/2014 - 08:50

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?

NXJournaling - Thu, 05/15/2014 - 18:03

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.

nielsvt - Wed, 05/21/2014 - 23:56

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?

NXJournaling - Fri, 05/23/2014 - 08:36

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

nielsvt - Fri, 06/06/2014 - 02:28

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.

NXJournaling - Tue, 06/10/2014 - 14:28

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.

nielsvt - Wed, 06/18/2014 - 23:17

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...

Thanks for your help.

dunlayz - Fri, 12/19/2014 - 08:01

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

EN US