Assistance with formatting an extracted drawing note to a spreadsheet

[JBerns]

Community,

I have an Inventor 2019 drawing from which I am extracting a Parts List and a drawing note to an Excel spreadsheet using iLogic.

The parts list extraction works well. (a Community solution)

I have challenges with the drawing note due to existence and lack of formatting.

Here is the note as it appears in the drawing:

Once I extract the GeneralNote from the drawing, I can access the properties, FormattedText and Text.

Here are their values:

FormattedText = "<StyleOverride Font='Tahoma' Bold='True'>B = TO BE BURNED W = WATERJET</StyleOverride><Br/><StyleOverride Font='Tahoma'> </StyleOverride><Br/><StyleOverride Font='Tahoma' Bold='True' Underline='True'>NOTES:</StyleOverride><Br/><Numbering Format='1'><StyleOverride Font='Tahoma'>ALL DIMENSIONS ARE IN INCHES</StyleOverride></Numbering><Numbering Format='1'><StyleOverride Font='Tahoma'>DIMENSION TOLERANCE ±0.03</StyleOverride></Numbering><Numbering Format='1'><StyleOverride Font='Tahoma'>BREAK ALL SHARP EDGES</StyleOverride></Numbering>"

Text = "B = TO BE BURNED W = WATERJETNOTES:1." & vbTab & "ALL DIMENSIONS ARE IN INCHES2." & vbTab & "DIMENSION TOLERANCE ±0.033." & vbTab & "BREAK ALL SHARP EDGES"

The FormattedText property string appears to contain XML formatting as denoted by the open/close angle brackets.
The Text property does not contain line breaks, which seems unusual, so I can't use that property to get the note.

Therefore, I am parsing the FormattedText string searching for the <Br/> code. This gets split into a string array.

Next I will have to search for the <Numbering Format='1'> code to convert these to 1., 2., 3., etc.

I have a portion of the code below along with some extra lines for debugging.

	Sub ParseNote(oNote As String)
		break
'		oNote = oNote.ToString
		Dim OffsetIndex As Integer = 1
		Dim oDelimBrcktOpn As String = "<"
		Dim oDelimBrcktCls As String = ">"
		While Strings.InStr(OffsetIndex, oNote, oDelimBrcktOpn)
			Dim oPosBrcktOpn As Integer = Strings.InStr(OffsetIndex, oNote, oDelimBrcktOpn)
			Dim oPosBrcktCls As Integer = Strings.InStr(oPosBrcktOpn + 1, oNote, oDelimBrcktCls)
			Try
			If (oPosBrcktOpn > 0) And (oPosBrcktCls > 0) Then
'				MsgBox(Strings.Mid(oNote, oPosBrcktOpn, (oPosBrcktCls - oPosBrcktOpn) + 1))
'				MsgBox("Before:" & vbLf & oNote)
				Dim oTemp4 As String = Strings.Mid(oNote, oPosBrcktOpn, ((oPosBrcktCls - oPosBrcktOpn) + 1))
				If oTemp4 <> "<Br/>" Then
					dim oTemp5 as string = Strings.Mid(oNote, oPosBrcktOpn, ((oPosBrcktCls - oPosBrcktOpn) + 1)) = "^"
				End If
'				MsgBox("After:" & vbLf & oNote)
				
'				Dim StringPart As String = Mid(MyString, InStr(MyString, " "), InStr(MyString, ",") - InStr(MyString, " "))
				
			End If
			Catch
			end try
			OffsetIndex = Math.Max(oPosBrcktOpn, OffsetIndex + 1)
		End While
			
		Dim oDelimBr As String = "<Br/>"
		oDrawNotes = Strings.Split(oNote, oDelimBr)
	End Sub

Is this the best method to parse and format a general note from an Inventor drawing?

Could there be an XML-processing tool or method available for iLogic (VB)?

Thank you for your time and attention. I look forward to your replies.

Kind regards,

Jerry

P.S. - I will be attending a FIRST Robotics Regional event on Friday, so any replies from me may be delayed.

[yan.gauthier]

Hi,

If Ilogic regular expression engine support positive look behind and positive look ahead (vba regex doesn't). you could use that pattern to isolate everything between > and < 

(?<=\>).*?(?=\<)

[WCrihfield]

Hi @JBerns.  Yes.  You can actually work with the XML data directly by making using the AddReference & Imports statements in the rule's 'Header' area similar to this:

AddReference "System.Xml.dll"
AddReference "System.Data.dll"
Imports System.XML
Imports System.Data

But then you would have to familiarize yourself with using that system of objects & methods.  I am not well versed in using that system at this point, because I almost never need to use it, so I probably could not offer a whole lot of help there right now.  You can search and find a few examples both here on the forums and online though to get you started.  The basics are not too difficult to pick up on if you have been using iLogic/vb.net for a while.

[WCrihfield]

Just dropping a link here to another post that you might find helpful in this area by @Ralf_Krieg that is similar and working with the FormattedText using XML referenced tools.

[Ralf_Krieg · Accepted solution]

Hello

You can set a reference to the System.XML class which offers a lot of functions to handle xml files. To extract the text and numbering you can do as follows. It's not an universal parser, just a few lines of code to achive the goal. As a xml file is structured like a tree, you can traverse down and get your informations.

Each single text line will be stored in a list of string. You can join the list as demonstrated in the msgbox at the end or get every list item to put in one excel table cell (todo).

AddReference "System.Xml"
Imports System.Xml
Class thisrule
	
	Dim oValueList As List(Of String)
	Dim iNumber As Integer

	Sub main
	
		oValueList = New List(Of String)
		iNumber = 0
		
		Dim FormattedText As String = "<StyleOverride Font='Tahoma' Bold='True'>B = TO BE BURNED W = WATERJET</StyleOverride><Br/><StyleOverride Font='Tahoma'> </StyleOverride><Br/><StyleOverride Font='Tahoma' Bold='True' Underline='True'>NOTES:</StyleOverride><Br/><Numbering Format='1'><StyleOverride Font='Tahoma'>ALL DIMENSIONS ARE IN INCHES</StyleOverride></Numbering><Numbering Format='1'><StyleOverride Font='Tahoma'>DIMENSION TOLERANCE ±0.03</StyleOverride></Numbering><Numbering Format='1'><StyleOverride Font='Tahoma'>BREAK ALL SHARP EDGES</StyleOverride></Numbering>"
		'ensure there's only one root element
		Dim sRT As String = "<Root>" & FormattedText  & "</Root>"
		
		Try
		' Create the XmlDocument.  
		Dim xdoc As New System.Xml.XmlDocument()  
		
		xdoc.LoadXml(sRT) 
		Dim oNode As XmlNode
		Break
		
		If xdoc.HasChildNodes = True Then
			TraverseChildNodes(xdoc.ChildNodes)
		End If
		
		Dim sString As String=String.Join(" " & vbCrLf & " ", oValueList)
		MsgBox(sString)
		
		Catch ex As Exception
			Break
		End Try
	End Sub
	
	Sub TraverseChildNodes(ByVal oNodes As XmlNodeList)
		Dim oNode As XmlNode
		For Each oNode In oNodes
			If oNode.Name.ToUpper = "NUMBERING" Then
				'raise numbering index
				iNumber += 1
			End If
			If oNode.HasChildNodes = True Then
				TraverseChildNodes(oNode.ChildNodes)
			Else
				If oNode.Value Is Nothing Then
					oValueList.Add("")
					iNumber=0
				Else
					If iNumber=0 Then
				oValueList.Add(oNode.Value.ToString)
			Else
				oValueList.Add(iNumber & ". " & oNode.Value.ToString)
			End If
				End If
			End If
		Next
	End Sub
End Class

[JBerns]

@Ralf_Krieg,

Thank you so much for the coded solution. I was able to incorporate it successfully into my code and export the drawing note into the spreadsheet.

Unfortunately, a new error now occurs that I am trying to resolve. I am certain it should not having anything to do with extracting the XML info.

After the info from the note is extracted, the text is written to the exported XLSX file.

Once in Excel, I use a macro from a second XLS file for a post-export formatting operation.

So my code opens two XLS files. The code to open two worksheets was working, but now I get an error: The RPC Server is unavailable.

The error occurs after the second file is opened.

Here is my code which writes some data to a few cells, saves, closes, and then launches Excel twice to open the macro document and then the exported document:

' Prompt to open exported file
If System.IO.File.Exists(oExportFileName) Then

    ' Display message box prompt 1
    question1 = MessageBox.Show("Open the exported parts list?" & vbLf & vbLf & oExportFileName, "iLogic Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
    If question1 = vbYes Then

        ' Display message box prompt 2
        question2 = MessageBox.Show("Load the Parts List macro?", "iLogic Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)

        ' Set condition based on first answer
        If question1 = vbYes Then

            Try

            ' Place data in the exported parts list
            ' Customer name
            GoExcel.CellValue(oExportFileName, "BOM", CustCell) = Clipboard.GetText
            
            ' Burn, Machine, Waterjet note
            GoExcel.CellValue(oExportFileName, "BOM", BMWLCell) = oValueList(0)
            
            'Locate "NOTES"
            Dim oNotesIndex As Integer = oValueList.IndexOf("NOTES:")
            If oNotesIndex < 0 Then
                oNotesIndex = 1
            Else
                oNotesIndex += 1
            End If
            
            ' Loop through remainder of string list
            For i = oNotesIndex To (oValueList.Count - 1)
                Dim CellIndex = String.Concat(NoteCol, NoteRow.ToString)
                GoExcel.CellValue(oExportFileName, "BOM", CellIndex) = oValueList(i)
                NoteRow += 2
            Next

            ' Clear remainder of existing notes from template
            For i = 1 To 20
                Dim CellIndex = String.Concat(NoteCol, NoteRow.ToString)
                GoExcel.CellValue(oExportFileName, "BOM", CellIndex) = ""
                NoteRow += i
            Next

            ' Save and close the file
            GoExcel.Save
            GoExcel.Close

            ' Display a message box to cause a delay. This will give time for previous command to close files
            MsgBox("File exporting complete.", , "Export Notice")
            
            ' Conditionally open the macro file
            If question2 = vbYes Then
                ThisDoc.Launch("\\ENG\ENGINEERING\UTILITY\Miscellaneous\GS-Excel_Macros.xlsm")
            End If

            ' Open the export file
            ThisDoc.Launch(oExportFileName)
            
            Catch
                MsgBox("An error occurred writing information to the exported part list.",vbOKOnly,"WARNING")
            End Try
        End If
    Else
        question3 = MessageBox.Show("Delete the exported parts list?" & vbLf & vbLf & oExportFileName, "iLogic Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
        If question3 = vbYes Then
            If System.IO.File.Exists(oExportFileName) = True Then
                System.IO.File.Delete(oExportFileName)
                MsgBox("File Deleted")
            End If
        End If
    End If
End If

On-line searches have not found a solution yet, but still searching.

I added a prompt after the 'Close' and before the first 'Launch' to give time for files to close, but it did not solve the RPC error.

In a new rule I added code just to launch Excel using two files names. That worked without error. I conclude that there must be a timing issue when the exported file is written to,  saved, closed, and then reopened.

I can accept a nuisance error message now that the note is being extracted correctly. Of course it would be better without it.

Thanks again for solving the formatting issue!

Regards,
Jerry

[maxim.teleguz]

how to extract what the format of an object = for instance how would a revision table look like or how would a balloon look like etc... 

[WCrihfield]

Hi @maxim.teleguz.  The RevisionTable object is pretty complex, so it is mostly controlled by a RevisionTableStyle (at RevisionTable.Style).  That type of Style has a lot of its own methods & properties you can look through.  It's not something that can quickly and easily be written out in a single FormattedText note.  However, the individual RevisionTableCell objects within the table do have their own FormattedText (and regular Text) property.  So the cells FormattedText property could potentially contain some XML tags within its value.

Below is a quick/simple example of getting the FormattedText of a RevisionTableCell, showing it to the user, allowing them to edit its value, then setting that edited value back to the cell.

Sub Main
	Dim oRevTable As RevisionTable = ThisApplication.CommandManager.Pick(SelectionFilterEnum.kDrawingRevisionTableFilter, "Select a RevisionTable.")
	If oRevTable Is Nothing Then Exit Sub
	Dim oCell1 As RevisionTableCell = oRevTable.RevisionTableRows.Item(1).Item(1)
	Dim sFText As String = oCell1.FormattedText
	sFText = InputBox("Edit FormattedText", "RevisionTableCell.FormattedText", sFText)
	oCell1.FormattedText = sFText
End Sub

As for a Balloon...I do not think they actually contain any FormattedText, unless maybe you are using a custom type of Balloon that involves the use of a SketchedSymbolDefinition.  The SketchedSymbolDefinition may include some FormattedText, if designed that way.  I am not sure if this is what you were asking about or not, but since this topic seems to be mostly about dealing with FormattedText, it seemed logical.  There are several other similar examples of accessing the FormattedText of other objects too.  I like using an InputBox, because you can supply a 'DefaultValue', which is selectable/editable, and can be copied, rather than just something to look at.