Export Balloonreport to .txt

[Jesper_S]

Hi.

Got an iLogic code that gives me a list (In a message box) of in what View a Balloon is on every sheet of the drawing.

Is there a way to get this list somehow written out to a .txt file or even better, an Excel document.

Here is the iLogic.

Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

' Set a reference to the active sheet.
Dim oActiveSheet As Sheet
oActiveSheet = oDrawDoc.ActiveSheet


Dim oBalloon As Balloon
Dim oBalloonValueSet As BalloonValueSet

Dim oSheet As Sheet
For Each oSheet In oDrawDoc.Sheets
	If oMesssage = "" Then
		oMesssage = oSheet.Name
	Else
		oMesssage = oMesssage & vbLf & oSheet.Name
	End If
	oSheet.Activate
	For Each oBalloon In oDrawDoc.ActiveSheet.Balloons
		oViewName = oBalloon.ParentView.Name
		oItem = oBalloon.BalloonValueSets.Item(1).Value
		oMesssage = oMesssage & vbLf & "Balloon # " & oItem & " is on " & oViewName
	Next
	oMesssage = oMesssage & vbLf & "___________________"
Next

oActiveSheet.Activate 'return to original sheet
MessageBox.Show(oMesssage, "iLogic")

I don't even know how to start getting it out to a .txt file or Excel.

Thanks in advance.

[WCrihfield · Accepted solution]

See how this works for you, for writing it out to a text file.

You may or may not want to change the text files name and location.

Dim oDDoc As DrawingDocument = ThisDrawing.Document
Dim oActiveSheet As Sheet = oDDoc.ActiveSheet
Dim oBalloon As Balloon
Dim oBalloonValueSet As BalloonValueSet
Dim oSheet As Sheet
Dim oTxtWriter As System.IO.StreamWriter = System.IO.File.CreateText(ThisDoc.PathAndFileName(False) & ".txt")
Dim oMesssage, oViewName, oItem As String
oMesssage = ""
For Each oSheet In oDDoc.Sheets
	If oMesssage = "" Then
		oMesssage = oSheet.Name
	Else
		oMesssage = oMesssage & vbCrLf & oSheet.Name
	End If
	oTxtWriter.WriteLine(oMesssage)
	oSheet.Activate
	For Each oBalloon In oDDoc.ActiveSheet.Balloons
		oViewName = oBalloon.ParentView.Name
		oItem = oBalloon.BalloonValueSets.Item(1).Value
		oTxtWriter.WriteLine("Baloon # " & oItem & " is on " & oViewName)
	Next
	oTxtWriter.WriteLine("")
	oTxtWriter.WriteLine("___________________")
Next
oActiveSheet.Activate 'return to original sheet
oTxtWriter.Close()
ThisDoc.Launch(ThisDoc.PathAndFileName(False) & ".txt")

[JhoelForshav · Accepted solution]

Hi @Jesper_S 

Try this to create excel document 🙂

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

Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

' Set a reference to the active sheet.
Dim oActiveSheet As Sheet
oActiveSheet = oDrawDoc.ActiveSheet


Dim oBalloon As Balloon
Dim oBalloonValueSet As BalloonValueSet

Dim oExcelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application

oExcelApp.Visible = True 'Remove this Line if you don't want excel to be visible

Dim oExcelDoc As _Workbook = oExcelApp.Workbooks.Add()
Dim oExcelSheet As Worksheet = oExcelDoc.Worksheets.Item(1)
Dim oSheet As Sheet
Dim oCell As Range = oExcelSheet.Range("A1")
For Each oSheet In oDrawDoc.Sheets
	oSheet.Activate
	oCell.Value = oSheet.Name
	
	Dim i As Integer = 1
	For Each oBalloon In oDrawDoc.ActiveSheet.Balloons
		oViewName = oBalloon.ParentView.Name
		oItem = oBalloon.BalloonValueSets.Item(1).Value
		oCell.Offset(i).Value = "Balloon # " & oItem & " - " & oViewName
		i+=1
	Next
	oCell.EntireColumn.AutoFit()
	oCell = oCell.Next
Next
oActiveSheet.Activate 'return to original sheet

''Save and close excel
'oExcelDoc.SaveAs("Input Filename Here")
'oExcelDoc.Close
'oExcelApp.Quit

[WCrihfield]

Not bad.

Here's my version.  I thought about not posting it because you already had a similar solution, but this does a few things differently, that you both might be interested in.

This one creates column headers, to organize the data, so it might be more useful later.

It also used absolute cell addresses, which use their index Integers, instead of the alphanumerical addresses.

If uses a different, and simpler method to make all the columns 'AutoFit'.

Then asks if you want to save the resulting Excel file, and incorporates the SaveAs dialog box for ultimate control over where to save it and what to name it.

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

Dim oDDoc As DrawingDocument = ThisDrawing.Document
Dim oActiveSheet As Sheet = oDDoc.ActiveSheet
Dim oBalloon As Balloon
Dim oSheet As Sheet

'Create New instance of the Excel application
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass
oExcel.DisplayAlerts = False
oExcel.Visible = True
'Create a new Workbook (new file)
Dim oWB As Workbook = oExcel.Workbooks.Add()
'Get the first Worksheet (there will always be at least one in a new document)
Dim oWS As Worksheet = oWB.Worksheets.Item(1)
Dim oCells As Range = oWS.Cells
'Create column headers
'First number is Row index, second number is Column Index
oCells.Item(1, 1).Value = "SHEET"
oCells.Item(1, 2).Value = "BALOON #"
oCells.Item(1, 3).Value = "VIEW"

'Start writing data on row 2
Dim i As Integer = 2
For Each oSheet In oDDoc.Sheets
	If oSheet.DrawingViews.Count > 0 Then
		oSheet.Activate
		For Each oBalloon In oSheet.Balloons
			oCells.Item(i,1).Value = oSheet.Name
			oCells.Item(i, 2).Value = oBalloon.BalloonValueSets.Item(1).Value
			oCells.Item(i, 3).Value = oBalloon.ParentView.Name
			i = i + 1
		Next
	End If
Next
oWS.Columns.AutoFit
oActiveSheet.Activate
oAns = MsgBox("Do you want to save this Excel file?", vbYesNo + vbQuestion, "SAVE THIS?")
If oAns = vbYes Then
	Dim oFileDlg As Inventor.FileDialog = Nothing
	iLogicVb.Application.CreateFileDialog(oFileDlg)
	oFileDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
	oFileDlg.DialogTitle = "SPECIFY LOCATION AND NAME FOR THE NEW FILE."
	oFileDlg.InitialDirectory = IO.Path.GetDirectoryName(oDDoc.FullFileName)
	oFileDlg.CancelError = True
	On Error Resume Next
	oFileDlg.ShowSave
	If Err.Number <> 0 Then
		MsgBox("File not saved!",vbOKOnly + vbExclamation, "Save Dialog Was Canceled!")
	ElseIf oFileDlg.FileName <> "" Then
		oFileName = oFileDlg.FileName
		oWB.SaveAs(oFileName)
		MsgBox("The Excel file was saved to:  " & oFileName, vbOKOnly, "File Saved!")
	End If
End If

'oWB.Close
'oExcel.Quit

[Jesper_S]

Looks really good. 

I will try this on Monday when I'm back at work.

The purpose of this is actually to try and sort and filter the balloons in the excel to make it easier to review the drawing and to locate the balloons on the drawing.