Writing drawing Prompted Entries to Excel

[H4TSDESQ]

Hey All.. 

I'm looking for a more automated way of taking some Prompted Entry values when a sheet is created and having it exported out to an Excel file. Currently we have to have someone manually copy the info here into an excel file.

I came across this post which can do the inverse (writes data to those fields).

From the Trenches with Autodesk Inventor: iLogic - TitleBlock Project Data From Excel

Could someone point me in the right direction?

[daltonNYAW9]

Heres an example:

First, loop through all text boxes in your title block looking for 'Prompted entrys'
Then add those values to a matrix. In my example i have 100 rows and 2 columns
Finally export these values to an excel file. This will require more customization on your part, but there are plenty of examples online tfor help.

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Dim oDDoc As DrawingDocument = ThisDoc.Document

Dim excelarray(99, 1) As String
Dim activerow As Integer = 0
Dim oTitleBlock As TitleBlock = oDDoc.ActiveSheet.TitleBlock
For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes
	If oTextBox.FormattedText.EndsWith("</Prompt>")
		excelarray(activerow, 0) = oTextBox.Text.ToString
		excelarray(activerow, 1) = oTitleBlock.GetResultText(oTextBox).ToString
		activerow += 1
	End If
Next


Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Add()
Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Sheets("Sheet1"), Excel.Worksheet)

xlWorksheet.Range(xlWorksheet.Cells(1, 1), xlWorksheet.Cells(activerow, 2)).Value = excelarray
xlWorksheet.SaveAs("C:\Temp\test file.xlsx")

xlWorkbook.Close()
xlApp.Quit()

xlApp = Nothing
xlWorkbook = Nothing
xlWorksheet = Nothing

[H4TSDESQ]

Any tips? 

[daltonNYAW9]

Hmm never had that happen. Try one of these:

https://help.autodesk.com/view/INVNTOR/2023/ENU/?guid=GUID-A65240D6-4854-42B9-B9A6-15453ACB5BC0

https://stackoverflow.com/questions/28066719/unable-to-cast-com-object-of-type-microsoft-office-inte...

[C_Haines_ENG]

Do you want excel to open for the user or do you just want to write the data to excel? Definitely an easy way and a hard way to go about this. 

[H4TSDESQ]

Assuming that the format is what I desire (prompted entry names as the first row & the data beneath) having the excel file opened once complete is not necesary.

[C_Haines_ENG]

Excel is a tempered beast, doesn't love to work. If you need more advanced methods of collecting excel objects, like getting an open document instead of creating a new one every time, let me know.

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document

	Dim WS As Excel.Worksheet = StartExcel
	Dim Row As Integer = 1, Col As Integer = 1

	Dim oTitleBlock As TitleBlock = oDoc.ActiveSheet.TitleBlock

	For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes
		If oTextBox.FormattedText.EndsWith("</Prompt>")
			WS.Cells(Row, Col).Value = oTextBox.Text.ToString
			WS.Cells(Row, Col + 1).Value = oTitleBlock.GetResultText(oTextBox).ToString
			
			Row += 1
		End If
	Next

    WS.Cells.EntireColumn.AutoFit

End Sub

Function StartExcel

	Dim XlApp As Excel.Application = CreateObject("Excel.Application")
	Dim WB As Excel.Workbook = XlApp.Workbooks.Add
	Dim WS As Excel.Worksheet = WB.Worksheets(1)
	WS.Name = "ENTRY"

	XlApp.Visible = True

	WB.Application.WindowState = WB.Application.WindowState
	AppActivate(WB.Application.Caption)

	Return WS

End Function

[H4TSDESQ]

I feel like there might be an issue with my Inventor installation as I'm getting the same type of error message as the previous suggestion.

[C_Haines_ENG]

Ok so here's all the troubleshooting steps that I know:

1. Do you have excel installed on your computer (you need this if you aren't using Go-Excel)
2. Have you opened excel on your computer at least once that session? Sometimes the COM object cant be grabbed if no instance of Excel can be detected. Try leaving an instance of excel open on your desktop and run the rule again.
3. In your iLogic Configuration setting what engine do you have Excel using? Check this link https://help.autodesk.com/view/INVNTOR/2024/ENU/?guid=GUID-A65240D6-4854-42B9-B9A6-15453ACB5BC0
4. Can you take a screenshot of your code in Inventor, want to make sure you copied the Header

Other than that, Its hard to help. That code works great on my computer.

[H4TSDESQ]

Trying a different PC and Inventor year (2023), and running into the same issue. 

1) Yes, Excel is installed

2) Yep, currently have it open right now.

3) At first go the Engine was set to Internal, changing to COM results in the same error

Tried the code by daltonNYAW9 again, and it gave the same error (different line but it was the Dim xlWorkbook As Excel.Workbook snippet)

[C_Haines_ENG]

Frustrating....

Try removing the "X as X" statements so you'd be left just with XLApp = CreateObject("Excel.Application") and so on. That error usually means that the "Imports" section is not grabbing anything useful.

Try running this code with an instance of an excel workbook open. This will let us know if binding to excel works at all.

Sub Main
	
	XLApp = GetObject(, "Excel.Application")
	
	MsgBox(XLApp.Caption)
	
End Sub

I see some pop-ups of people running into the binding issue, so its likely your .dll file is corrupted or missing. What version of excel are you using? Some users report this issue is caused by having multiple versions of excel installed, or not fully uninstalled. See This thread 

[H4TSDESQ]

This was the result of the test code:

Excel version:

[C_Haines_ENG]

Fantastic! That means excel will bind, and you likely just have a bad install of Excel. Either way I think we can just revise the previous code to work without defining anything. Will be much harder to code for you as you wont get any Intellisense but such is life. Give this code a go:

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document

	WS = StartExcel
	Dim Row As Integer = 1, Col As Integer = 1

	Dim oTitleBlock As TitleBlock = oDoc.ActiveSheet.TitleBlock

	For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes
		If oTextBox.FormattedText.EndsWith("</Prompt>")
			WS.Cells(Row, Col).Value = oTextBox.Text.ToString
			WS.Cells(Row, Col + 1).Value = oTitleBlock.GetResultText(oTextBox).ToString
			
			Row += 1
		End If
	Next

    WS.Cells.EntireColumn.AutoFit

End Sub

Function StartExcel

	XlApp = CreateObject("Excel.Application")
	WB = XlApp.Workbooks.Add
	WS = WB.Worksheets(1)
	WS.Name = "ENTRY"

	XlApp.Visible = True

	WB.Application.WindowState = WB.Application.WindowState
	AppActivate(WB.Application.Caption)

	Return WS

End Function

[H4TSDESQ]

Progress! 

So that code does successfully work, but it looks like it only writes the current sheet. Is there any way to get it to spit out all the sheets with the prompted entry name as the header on the column?

[C_Haines_ENG · Accepted solution]

This should hopefully work.

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document

	WS = StartExcel
	Dim Col As Integer = 2

	For i = 1 To oDoc.Sheets.Count

		Dim oTitleBlock As TitleBlock = oDoc.Sheets(i).TitleBlock

		For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes
			
			If oTextBox.FormattedText.EndsWith("</Prompt>")
				
				Logger.Info(oTextBox.Text.ToString)
				
				WS.Cells(1, Col).Value = oTextBox.Text.ToString
				
				WS.Cells(i + 1, 1).Value = "SHEET " & i
				
				WS.Cells(i + 1, Col).Value = oTitleBlock.GetResultText(oTextBox).ToString
				
				Col += 1
				
			End If
		Next
		
		Col = 2

	Next

	WS.Cells.EntireColumn.AutoFit

End Sub

Function StartExcel

	XlApp = CreateObject("Excel.Application")
	WB = XlApp.Workbooks.Add
	WS = WB.Worksheets(1)
	WS.Name = "ENTRY"

	XlApp.Visible = True

	WB.Application.WindowState = WB.Application.WindowState
	AppActivate(WB.Application.Caption)

	Return WS

End Function

[C_Haines_ENG]

If you want to do some extra formatting you should reference Microsofts EXCEL VBA API. 

You can change borders, fonts, alignments, numbering formats, etc. If you get stuck you can always ask for help. 

[H4TSDESQ]

This is the one right here! 😁

Did a test file with a couple dummy drawings and it came out in excel perfectly.

I'll have to rearrange the Fields in Inventor to the order that I want the Excel to be, but this is great. Thank you!

Side note:

Is there any way to get a prompted entry to be the Name in the browser?

[C_Haines_ENG]

On its Own:

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document

	For Each oSheet As Sheet In oDoc.Sheets

		Dim oTitleBlock As TitleBlock = oSheet.TitleBlock

		For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes

			If oTextBox.FormattedText.EndsWith("</Prompt>") And oTextBox.Text.ToString = "<Sheet Number>"

				oSheet.Name = oTitleBlock.GetResultText(oTextBox).ToString
			
			End If

		Next

	Next

End Sub

Integrated with the Excel script:

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document

	WS = StartExcel
	Dim Col As Integer = 2

	For i = 1 To oDoc.Sheets.Count

		Dim oTitleBlock As TitleBlock = oDoc.Sheets(i).TitleBlock

		For Each oTextBox As Inventor.TextBox In oTitleBlock.Definition.Sketch.TextBoxes

			If oTextBox.FormattedText.EndsWith("</Prompt>")

				If oTextBox.Text.ToString = "<Sheet Number>"
					
					oDoc.Sheets(i).Name = oTitleBlock.GetResultText(oTextBox).ToString
					WS.Cells(i + 1, 1).Value = oTitleBlock.GetResultText(oTextBox).ToString

				Else

					WS.Cells(1, Col).Value = oTextBox.Text.ToString

					WS.Cells(i + 1, Col).Value = oTitleBlock.GetResultText(oTextBox).ToString
					
					Col += 1

				End If

			End If
		Next

		Col = 2

	Next

	WS.Cells.EntireColumn.AutoFit

End Sub

Function StartExcel

	XlApp = CreateObject("Excel.Application")
	WB = XlApp.Workbooks.Add
	WS = WB.Worksheets(1)
	WS.Name = "ENTRY"

	XlApp.Visible = True

	WB.Application.WindowState = WB.Application.WindowState
	AppActivate(WB.Application.Caption)

	Return WS

End Function

[H4TSDESQ]

Awesome! Both of these work flawlessly. Thank you so much !

[C_Haines_ENG]

I noticed you mentioned that you would need to change the order of the fields in Excel. I can update the rule to do that automatically if you tell me the order you want. I can also setup some formatting like borders and bolded items or even a table if you'd like. 

You can attach an image of the "final result" and I can work off that.