browse by category or date

Very often, my “customers” asked me to retrieve records from database and pass it to them as an Excel Document. Previously, I need to manually set the width and wrap each of the columns.

I found it tedious and time wasting (the manual formatting, not the user’s request :D)

This macro below will automatically set all columns to auto-fit and wrap those columns that are too wide.

To add macro to your Excel, follow these steps:

  1. Click Tools -> Macro -> Record New Macro
  2. Give the Macro a meaningful name and description. The shortcut key is optional. If you want to make the Macro available in any Excel in your PC, store the macro in “Personal Macro Workbook”. Click OK to continue.
    Add New Macro
  3. A small floating window will appear. Click the ‘Stop Recording’ button.
  4. Click Tools -> Macro -> Visual Basic Editor (or press Alt-F11)
  5. Complete the code as shown below
  6. Save it. The macro now will be run whenever you press the shortcut key (if you defined it), or you could find the macro through clicking Tools -> Macro -> Macros (or by pressing Alt-F8)
Sub FormatQueryResult()
'
' FormatQueryResult Macro
' To make the Database query result less irritating to the eyes
'
    Dim i As Integer 'counter
    SendKeys "^a", True 'Send Ctrl-A to select All
        
    'Make the column auto fit
    For i = 1 To Selection.Columns.Count
        ActiveSheet.Columns(i).AutoFit
    Next
    
    'Wrap columns that are too wide
    For i = 1 To Selection.Columns.Count
       If ActiveSheet.Columns(i).Width >= 250 Then
            ActiveSheet.Columns(i).ColumnWidth = 65
            ActiveSheet.Columns(i).WrapText = True
        End If
    Next
End Sub
GD Star Rating
loading...
How To Quickly Format Excel Document for Your Database Query Result, 2.0 out of 5 based on 1 rating

Possibly relevant:

About Hardono

Howdy! I'm Hardono. I am working as a Software Developer. I am working mostly in Windows, dealing with .NET, conversing in C#. But I know a bit of Linux, mainly because I need to keep this blog operational. I've been working in Logistics/Transport industry for more than 11 years.

Incoming Search

ms excel, vba

No Comment

Add Your Comment