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 .Columns("A:IV").AutoFit End With Next ws End Sub
- 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.