'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 objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox("Could not start Excel, this journal will now exit.", MsgBoxStyle.Critical, "Error")
Exit Sub
End If
Dim excelFile As String = "c:\my excel file location"
If Not IO.File.Exists(excelFile) Then
MsgBox("Specified file not found, journal will now exit.", MsgBoxStyle.Critical, "File not found.")
Exit Sub
End If
Dim objWorkbook = objExcel.Workbooks.Open(excelFile)
If objWorkbook Is Nothing Then
MsgBox("Could not open Excel file, journal will now exit.", MsgBoxStyle.Critical, "Error")
Exit Sub
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 = 4
Const spreadsheetRowEnd As Integer = 6
' specify expression name in .prt file that references
' spreadsheet value
Const expressionName As String = "my expression to vary"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
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
Dim output1 As Expression = Nothing
output1 = workPart.Expressions.FindObject("output1")
Dim output2 As Expression = Nothing
output2 = workPart.Expressions.FindObject("output2")
Dim output3 As Expression = Nothing
output3 = workPart.Expressions.FindObject("output3")
Dim output4 As Expression = Nothing
output4 = workPart.Expressions.FindObject("output4")
objExcel.visible = True
objExcel.cells(i, 2).value = (output1.Value)
objExcel.cells(i, 3).value = (output2.Value)
objExcel.cells(i, 4).value = (output3.Value)
objExcel.cells(i, 5).value = (output4.Value)
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
