Editing a spreadsheet controlled Tabular Note

The code below doesn't quite work, but it is close. The code looks at each tabular note; if the note in row 1, col 1 references a spreadsheet, it will attempt to modify the note. It successfully extracts and edits the spreadsheet; but it errors on the call to .StoreSpreadsheet. It doesn't seem to like the name of the spreadsheet even though it is the name that was extracted from the spreadsheet. This might be a case for GTAC to investigate. My guess is that the .StoreSpreadsheet method has not been updated to handle tabular note spreadsheets.

For what it's worth, here's the code (edit the value of "tempFile" to something appropriate for your setup before running it):

Option Strict Off
Imports System
Imports System.Collections.Generic
Imports NXOpen
Imports NXOpen.UF

Module Module2

Dim theSession As Session = Session.GetSession()
Dim theUfSession As UFSession = UFSession.GetUFSession()
Dim theUISession As UI = UI.GetUI

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 = "NXJ journal"
Dim markId1 As Session.UndoMarkId
markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, undoMarkName)

Dim myTabularNoteTags As New List(Of Tag)
If FindTabularNotes(myTabularNoteTags) = 0 Then
'no tabular notes to process
Return
End If

Const tempFile As String = "C:\Temp\sheetnote.xlsx"

For Each tableNote As Tag In myTabularNoteTags

Dim numRows As Integer
theUfSession.Tabnot.AskNmRows(tableNote, numRows)

Dim numCols As Integer
theUfSession.Tabnot.AskNmColumns(tableNote, numCols)

Dim tableSectionTag As Tag
theUfSession.Tabnot.AskNthSection(tableNote, 0, tableSectionTag)

'edit cell 0,0

Dim rowTag As Tag
theUfSession.Tabnot.AskNthRow(tableNote, 0, rowTag)

Dim colTag As Tag
theUfSession.Tabnot.AskNthColumn(tableNote, 0, colTag)

Dim cellTag As Tag
theUfSession.Tabnot.AskCellAtRowCol(rowTag, colTag, cellTag)

Dim theCellPrefs As UFTabnot.CellPrefs
theUfSession.Tabnot.AskCellPrefs(cellTag, theCellPrefs)

Dim spreadSheetTag As Tag = Tag.Null
Dim sheetNum As Integer
Dim ssRow As Integer
Dim ssCol As Integer

spreadSheetTag = theCellPrefs.referenced_spreadsheet
If spreadSheetTag <> Tag.Null Then
lw.WriteLine("this cell references a spreadsheet cell")

Dim ssObj As NXObject
ssObj = Utilities.NXObjectManager.Get(spreadSheetTag)
lw.WriteLine("Spread sheet name: " & ssObj.Name)

sheetNum = theCellPrefs.ss_sheet
ssRow = theCellPrefs.ss_row
ssCol = theCellPrefs.ss_col
lw.WriteLine("Sheet: " & sheetNum.ToString & " row: " & ssRow.ToString & " column: " & ssCol.ToString)

theUfSession.Xs.ExtractSpreadsheet(ssObj.Name, tempFile)

EditSpreadSheetNote(tempFile, theCellPrefs, "ss text edit")

'lw.WriteLine("storing Excel file")
theUfSession.Xs.StoreSpreadsheet(ssObj.Name, tempFile)

Else
'edit normal cell text
theUfSession.Tabnot.SetCellText(cellTag, "normal text edit")

End If

lw.WriteLine("")

Next

lw.Close()

End Sub

Function FindTabularNotes(ByRef theTabNotes As List(Of Tag)) As Integer

Dim tmpTabNote As NXOpen.Tag = NXOpen.Tag.Null
Dim type As Integer
Dim subtype As Integer

Do
theUfSession.Obj.CycleObjsInPart(workPart.Tag, UFConstants.UF_tabular_note_type, tmpTabNote)
If tmpTabNote = NXOpen.Tag.Null Then
Continue Do
End If
If tmpTabNote <> NXOpen.Tag.Null Then
theUfSession.Obj.AskTypeAndSubtype(tmpTabNote, type, subtype)
If subtype = UFConstants.UF_tabular_note_subtype Then

theTabNotes.Add(tmpTabNote)

End If
End If
Loop Until tmpTabNote = NXOpen.Tag.Null

Return theTabNotes.Count

End Function

Sub EditSpreadSheetNote(ByVal ssFile As String, ByVal theCellPrefs As UFTabnot.CellPrefs, ByVal newText As String)

Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object

Dim markId1 As Session.UndoMarkId
markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, "Excel import points")

Dim excelFileExists As Boolean = False
'cell pref row seems to be "one" based while the column seems to be "zero" based
Dim row As Long = theCellPrefs.ss_row
Dim column As Long = theCellPrefs.ss_col + 1

'lw.WriteLine("row: " & row.ToString & " column: " & column.ToString)

'create Excel object
Try
objExcel = CreateObject("Excel.Application")
Catch ex As Exception
'handle error, warn user
Return
End Try

If objExcel Is Nothing Then
theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not start Excel, journal exiting")
theSession.UndoToMark(markId1, "journal")
Exit Sub
End If

If IO.File.Exists(ssFile) Then
'Open the Excel file
excelFileExists = True
objWorkbook = objExcel.Workbooks.Open(ssFile)
Else
theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Excel file does not exist")
theSession.UndoToMark(markId1, "journal")
Exit Sub
End If

If objWorkbook Is Nothing Then
theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not open Excel file: " & ssFile & ControlChars.NewLine & "journal exiting.")
theSession.UndoToMark(markId1, "journal")
Exit Sub
End If

objExcel.Visible = True

objWorksheet = objWorkbook.WorkSheets(theCellPrefs.ss_sheet)

objWorksheet.cells(row, column).value = newText

objWorkbook.Save()
objWorkbook.Close()

objExcel.Quit()
objWorksheet = Nothing
objWorkbook = Nothing
objExcel = Nothing

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