I always have trouble with OLE objects and ArcGIS. Frequently, embedded objects (spreadsheets, Word docs, etc) just don’t render in the final output the way that I would like them to. Usually this workflow revolves around a need to present information in a tabular format.
I’m old enough to have lived in the computer world before WYSIWYG was the norm. Back in the old days, any fonts that were used to render in output (screen, paper, etc.) were fixed-width – that is, each character in the font library takes up the same amount of space. When using a font like this, you can use spaces to make characters line up. Examples of fixed width fonts are “Consolas” or “Courier”. I’ll frequently use Python to process text into space-padded strings using this concept, but sometimes I just need a quick and dirty way to convert a table of data into a padded text string. To do this, I wrote this simple Excel function.
To use the function, paste this code into a VBA subroutine. You can then use it as a formula. A typical cell formula would be:
=BuildMonoSpacedTableCell(A17,25,3)
Where:
- A17 – Cell Reference
- 25 Indicates that you want the total width of the output to be 25 characters
- 3 Indicates that you want the text to be RIGHT justified
To use this, simply generate a series of output cells that reference the original data, then concatenate the results together into a series of rows of text. Paste the output into whatever you are using, and make sure you set the font type to a fixed-width font.
Enjoy!
[raw]
Function BuildMonoSpacedTableCell(sIn As String, iCellWidth As Integer, iJust As Integer) As String
'iJust values:
'1 - left
'2 - center
'3 - right
Dim sOut As String, i1 As Integer
If iJust > 3 Or iJust < 1 Or Len(sIn) > iCellWidth Then
Exit Function
End If
If iJust = 1 Then
sOut = sIn & Space(iCellWidth - Len(sIn))
ElseIf iJust = 3 Then
sOut = Space(iCellWidth - Len(sIn)) & sIn
Else
i1 = Int((iCellWidth - Len(sIn)) / 2)
i2 = iCellWidth - Len(sIn) - i1
sOut = Space(i1) & sIn & Space(i2)
End If
BuildMonoSpacedTableCell = sOut
End Function
[/raw]
