Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 18, 2001

Automate Excel Spreadsheets From SQL Queries

By Danny Lesandrini


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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM