Format Excel based on Column Header

I got tired of constantly formatting SSMS results sets pasted in Excel. So I wrote this Excel Macro to shoulder some the load. This macro works well for me because the result sets are typically the same (as in the column headers of the data is consistent)

Option Explicit
Sub MassFormat()
 Dim ws As Worksheet
 Dim wb As Workbook
 Dim lRow As Long, aCol As Long
 Dim aCell As Range
 Dim WS_Count As Integer
 Dim I As Integer

 For Each ws In ActiveWorkbook.Worksheets
 With ws
 Set aCell = .Rows(1).Find("column_name1")

 '~~> Check if the column with "name" is found
 If Not aCell Is Nothing Then
 aCol = aCell.Column
 .Columns(aCol).EntireColumn.NumberFormat = "#################"
 End If

Set aCell = .Rows(1).Find("column_name2")
 If Not aCell Is Nothing Then
 aCol = aCell.Column
 .Columns(aCol).EntireColumn.NumberFormat = "MM/DD/YYYY HH:MM:SS"
 End If

 End With
 Next ws
End Sub

Some Notes

  • Throw this Macro in PERSONAL.XLSB, then you can assign a hot key to it and/or customize your ribbon for a shortcut
  • Just replace colum_name1 and column_name2 with your column names
  • You will need to make sure and Copy with Headers from SSMS and then paste to Excel.
  • You can of course use this for other stuff in Excel for column specific formatting, I am just using it for tweaking SSMS results sets.
  • We are currently implementing an SSRS solution to allow users to run reports. However I think this macro will serve me well for those adhoc requests for data going forward
  • I realize that this could be a little cleaner and a little smarter. Howerver I hacked this together trying to save development time and get some big impact quickly.
  • You can easily add columns as needed by just adding extra IF blocks with the column and format of your chosing.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s