Automate Excel Spreadsheets From SQL Queries

Simplify the Presentation Layer

My last article described a utility I created to import sql query metadata into an Access database for cataloging and searching. By applying some standards to the naming and formatting of saved SQL queries, you will be able to reduce the amount of new sql created by leveraging that already written and tested. That’s a good business practice, but it’s only half of the solution.

The other half has to do with running the query and dumping the results into a user friendly format, such as a Microsoft Excel spreadsheet. The download from last week includes all the code for both articles and if this subject interests you, I suggest you get the code and step through it. I’ll be focusing on the Excel Automation portion of the code without explaining in detail how to create a recordset based on a .sql file. But, all that code is exposed in the utility.

Create Record Source

Without going into great detail about the actual code involved, I’ll outline the steps required to create the recordset we need to build our Excel spreadsheet.

• Collect SQL Server Login Parameters (Server, Database, UID and PWD)
• Get the path to the .sql file with the Query SQL
• Validate existence of both the file and the SQL Server
• Use VBA to extract the text from your saved query .sql file.
• Create a Microsoft Access SQL Passthrough Query
• Set Query’s Connect String, SQL, Timeout and Return Records properties
• Flag any DELETE and UPDATE queries. (Dissallow or run with password only)
• Execute the Access Passthrough Query into a DAO Recordset object

Once we have a recordset, we’re ready to begin creating our Excel spreadsheet.

OK, So I Cheated … but You Can Too!

This is where I come clean. I’m not an Excel guru. I know very little about programming with Excel, but I know where to get an automatic code generator for Excel … and it’s free.

Excel comes with it’s own code generator!

Use the Macro generator to write your code for you. Before you perform any functions in your spreadsheet, such as font or column formatting, go to the Tools menu. Select Record New Macro from the Macro option. A small toolbar with VCR style buttons appears. Simply perform all your steps and press the stop button when finished. Then return to the Tools >> Macro menu and this time, choose Macros. Locate the macro you just created and click Edit to invoke the IDE. Here, you can examine the Excel VBA code necessary to format your new page. This is how I discovered the commands used in the code below.

Create, Popuplate and Format the Spreadsheet

‘************* BEGIN CODE HERE *************‘ Remember, we already created our recordset above‘ The recordset will be referred to below as rs

Dim appExcel As Excel.Application
Dim wbkNew As Excel.Workbook
Dim wksNew As Excel.Worksheet 

‘ Create the Excel Applicaiton, Workbook and Worksheet

 Set appExcel = Excel.Application
 Set wbkNew = appExcel.Workbooks.Add
 Set wksNew = appExcel.Worksheets("Sheet1")
 
 appExcel.Visible = True
 

‘ The first thing I do to the worksheet is to set the font.‘ Not all are required, but I included them as examples.

 With wksNew
    Cells.Font.Name = "Arial"
    Cells.Font.Size = 8
    Cells.Font.Strikethrough = False
    Cells.Font.Superscript = False
    Cells.Font.Subscript = False
    Cells.Font.OutlineFont = False
    Cells.Font.Shadow = False
    Cells.Font.Underline = xlUnderlineStyleNone
    Cells.Font.ColorIndex = xlAutomatic
     
    

‘ My first row will contain column names, so I want to freeze it

    .Rows("2:2").Select
    ActiveWindow.FreezePanes = True
 
    

‘ … and I want the header row to be bold

    .Rows("1:1").Font.Bold = True
     
    

‘ … and I want it to print on every page when in Print Preview

    
    ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
    ActiveSheet.PageSetup.PrintTitleColumns = ""

    

‘ Here, we set more Print Setup properties

    PageSetup.LeftHeader = sHeader 

‘ Custom header (name of SQL Query)

    PageSetup.CenterHeader = ""
    PageSetup.RightHeader = ""
    PageSetup.LeftFooter = "&F" & vbCr  

‘ File name and a hard return

    PageSetup.CenterFooter = "&D"  

‘ Today’s Date

    PageSetup.RightFooter = "&P of &N"  

‘ Page x of y

    PageSetup.PrintHeadings = False
    PageSetup.PrintGridlines = False
    PageSetup.PrintComments = xlPrintNoComments
    PageSetup.PrintQuality = 600
    PageSetup.CenterHorizontally = False
    PageSetup.CenterVertically = False
    PageSetup.Orientation = xlPortrait
    PageSetup.Draft = False
    PageSetup.FirstPageNumber = xlAutomatic
    PageSetup.Order = xlDownThenOver
    PageSetup.BlackAndWhite = False
    PageSetup.Zoom = 80  

‘ Reduce to 80% when printing‘ Create column headers‘ By looping through the field collection, we don’t need to know how‘ many columns the recordset contains. The spreadsheet will contain‘ as many columns as the recordset has fields, automatically!

    For i = 0 To iFldCount - 1
         

‘ Excel often views Zip codes as Integers, dropping leading zeros.‘ You can check for zip fields and force leading zeros‘ The commercial at (@) formats a column as text.

         If InStr(1, rs.Fields(i).Name, "zip") > 0 Then
             .Cells.NumberFormat = "@"
             .Cells(1, i + 1).Value = Format(CStr(rs.Fields(i).Name), "00000")
         Else
             .Cells.NumberFormat = "@"
             .Cells(1, i + 1).Value = rs.Fields(i).Name
         End If
         

‘ You could add a check for Date field and format the column accordingly.

    Next
     
    

‘ Data records start on the 2nd row

    j = 2
 
    

‘ Loop through recordset adding rows to Excel‘ This was covered above, but bears repeating:‘ By looping through the field collection, we don’t need to know how‘ many columns the recordset contains. The spreadsheet will contain‘ as many columns as the recordset has fields, automatically!

    Do Until rs.EOF
         For i = 0 To iFldCount - 1
             .Cells(j, i + 1).Value = rs.Fields(i).Value
         Next
         j = j + 1
         rs.MoveNext
    Loop
     
    

‘ We’re finished creating the rows. Now save the file‘ (Assuming the file exists, you must delete it first)

    Kill sExcelFileAndPath
    .SaveAs sExcelFileAndPath
      
    

‘ I don’t know what these to, but the Macro generated them …‘ …and I’m afraid to delete them 🙂

    .Visible = xlSheetHidden
    .Visible = xlSheetVisible

    

‘ My users appreciate when I resize the columns to fit the data.

    .Cells.Select
    .Cells.EntireColumn.AutoFit
     
    

‘ Set the focus back at the first cell

    .Range("A1").Select
 End With

‘ I don’t know what these to, but the Macro generated them …‘ …and I’m afraid to delete them 🙂

appExcel.DisplayFullScreen = True
appExcel.DisplayFullScreen = False
wksNew.Select
 

‘ Cleanup Excel objects

Set wksNew = Nothing
Set wbkNew = Nothing
Set appExcel = Nothing

Conclusion:

This tool has simplified my life considerably. If you’ve ever tried to grab results from SQL Server Query Analyzer and paste them into an Excel spreadsheet, you know that the column headings don’t come with you! Also, zip codes with leading zeros become integers … without leading zeros. My end users don’t like it when I trunkate leading zeros on zip codes … go figure! Also, since the Excel header and footer are generated automatically (and dynamically) it saves me time putting the document into a professionally presentable form. So, download the code and give it a try.

See All Articles by Columnist Danny Lesandrini