There was a question on the Autodesk Inventor Customization newsgroup this morning that caught my interest. The question was about creating a report, (in their case editing a column in the BOM), that lists all of the balloons in a drawing along with what sheet they’re on and the zone on the sheet they’re located within,
The macro below does this. The output is simple since it just writes out the result as a file, but you’ll likely want to edit the program to do something else with the result like incorporate it into the BOM, create a custom table, or some other thing I would never have thought of. The macro assumes that the border was created as a default border. With a default border it’s a simple matter to get the number of zones on a sheet and determine whether to use numbers or characters for the zone labels. If you’re using a border you’ve created then you’ll need to edit the program and specify this information.
I tested the macro using the Engine MKII sample assembly shown below. The drawing that comes with that sample has three sheets and balloons on two of the sheets.
Here are the contents of the report created by the macro.
==== Balloon Report ====
Sheet: Sheet:1
Balloon: 8, (7, B)
Balloon: 7, (1, A)
Balloon: 11, (7, B)
Balloon: 12, (7, B)
Balloon: 23, (7, A)
Balloon: 19, (7, A)
Balloon: 28, (7, A)
Balloon: 26, (7, A)
Balloon: 18, (8, A)
Balloon: 20, (8, A)
Balloon: 110, (3, B)
Balloon: 81, (3, A)
Balloon: 112, (4, A)
Sheet: Sheet:2
Balloon: 18, (2, B)
Balloon: 19, (3, A)
Balloon: 9, (5, C)
Balloon: 1, (5, D)
Balloon: 3, (5, D)
Balloon: 8, (5, C)
Balloon: 5, (5, D)
Balloon: 7, (5, C)
Balloon: 6, (5, C)
Balloon: 4, (5, D)
Balloon: 31, (5, B)
Balloon: 29, (2, B)
Balloon: 30, (2, B)
Balloon: 28, (2, B)
Balloon: 24, (2, B)
Balloon: 27, (2, B)
Balloon: 25, (2, B)
Balloon: 26, (2, B)
Balloon: 12, (5, B)
Balloon: 11, (5, B)
Balloon: 10, (5, B)
Balloon: 16, (2, C)
Balloon: 18, (3, D)
Balloon: 17, (3, D)
Balloon: 14, (2, C)
Balloon: 13, (2, C)
Balloon: 9, (2, C)
Balloon: 15, (3, D)
Balloon: 20, (3, A)
Balloon: 23, (2, A)
Balloon: 22, (3, A)
Balloon: 21, (3, A)
Sheet: Sheet:3
Here’s the VBA macro. To use it, just copy and paste the code below into a VBA code module, have a drawing open, and run it. If you’re not familiar with VBA, instructions to do this are available here.
Public Sub BalloonReport()
' Get the active drawing document.
Dim drawDoc As DrawingDocument
Set drawDoc = ThisApplication.ActiveDocument
' Open a file to write the results into.
Dim filename As String
filename = "C:\Temp\BalloonReport.txt"
Open filename For Output As #1
' Print out the header for the report, both to the
' output file and the immediate window in VBA.
Debug.Print ""
Debug.Print "==== Balloon Report ===="
Print #1, "==== Balloon Report ===="
' Iterate through the sheets.
Dim drawSheet As sheet
For Each drawSheet In drawDoc.Sheets
' Print out the sheet name.
Debug.Print " Sheet: " & drawSheet.Name
Print #1, " Sheet: " & drawSheet.Name
' Determine the zones within this sheet. This assumes that
' the border was created as a standard border where the
' zones are explicitly defined. If this is a custom border
' then some other mechanism will need to be created to
' define the zones.
Dim border As DefaultBorder
Set border = drawSheet.border
Dim XZoneWidth As Double
Dim YZoneWidth As Double
XZoneWidth = drawSheet.Width / border.HorizontalZoneCount
YZoneWidth = drawSheet.Height / border.VerticalZoneCount
Dim XZoneIsNumber As Boolean
If border.HorizontalZoneLabelMode = kBorderLabelModeNumeric Then
XZoneIsNumber = True
Else
XZoneIsNumber = False
End If
Dim YZoneIsNumber As Boolean
If border.VerticalZoneLabelMode = kBorderLabelModeNumeric Then
YZoneIsNumber = True
Else
YZoneIsNumber = False
End If
' Iterate through the balloons on this sheet.
Dim drawBalloon As Balloon
For Each drawBalloon In drawSheet.Balloons
' Because the border is numbered in the X direction from
' right to left and the sheet coordinate system is from
' left to right, this reverses the X coordinate so the
' zone computation will be correct.
Dim newXPosition As Double
newXPosition = drawSheet.Width - drawBalloon.position.X
' Determine the X zone.
Dim XZone As Integer
XZone = Int(newXPosition / XZoneWidth)
' Get the X zone as a string.
Dim XZoneValue As String
If XZoneIsNumber Then
XZoneValue = XZone + 1
Else
XZoneValue = Chr(XZone + 65)
End If
' Determine Y zone.
Dim YZone As Integer
YZone = Int(drawBalloon.position.Y / YZoneWidth)
' Get the Y zone as a string.
Dim YZoneValue As String
If YZoneIsNumber Then
YZoneValue = YZone + 1
Else
YZoneValue = Chr(YZone + 65)
End If
' Iterate through each value set associated with this balloon.
Dim valueSet As BalloonValueSet
For Each valueSet In drawBalloon.BalloonValueSets
' Print out the results for this value set.
Debug.Print " Balloon: " & valueSet.value & _
", (" & XZoneValue & ", " & YZoneValue & ")"
Print #1, " Balloon: " & valueSet.value & _
", (" & XZoneValue & ", " & YZoneValue & ")"
Next
Next
Next
' Close the output file.
Close #1
MsgBox "Report written to: """ & filename & """"
End Sub