Using Excel to Create Tabular Style Output with Fixed-Width Fonts

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]