Beyond Excel: VBA and Database Manipulation


February 2, 2010  5:57 PM

Setting Column Widths or Hiding Them

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Sometimes we want to include large fields in our results but don’t want them hogging screen realestate.  We can limit those results using the “Width” colum in our Fields Table (See below).  If you couple this with the Format “W” (Wrap), you can display comments and notes without pushing all other data off the screen.
 
Sometimes we want results that are only occasionally used.  We can elect to hide those columns with the Format Table’s Hide column.  If the user wishes to display those rarely used columns, they can use Excel’s Column Unhide or AutoFit commands. 
 
Sometimes we include data in our results required for some calculation, such as aging an invoice, but we don’t care to see the underlying data.  We can hide them as well – and in our next post we will be adding Excel formulas to the Fields Table.
Fields Table with Width and Hide Columns

Fields Table with Width and Hide Columns

Once again, all we need do is add the appropriate columns to our Fields Table (don’t forget to rename it with Shift-Ctrl-N) and sprinkle in a little VBA code.  This time, there are no changes required to Macro1().  We will be changing the Format_Results function only.

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
   
'   Format_Range:   Apply numeric formatting or alignment
'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   02/02/10 CWH  Added Column Width and Column Hide functionality
   
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
   
    Dim s As String
    Dim lColFmt As Long         '"Format" in Fields Table
    Dim lColWid As Long         '"Width"  in Fields Table
    Dim lColHid As Long         '"Hide"   in Fields Table
    Dim lRow As Long
    Dim lRows As Long
   
    lRows = Range(sDataRange).Rows.Count
       
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                'Formatting...
                s = Range(sFieldRange).Cells(lRow, lCol)
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select

            End With
            With Range(sDataRange).Columns(lRow -1)
               'Set Column Width ...
                s = Trim(Range(sFieldRange).Cells(lRow, lColWid))
                If Val(s) > 0 Then .ColumnWidth = Val(s)
               'Hide Column...
                s = UCase(Trim(Range(sFieldRange).Cells(lRow, lColHid)))
                If s = "H" Then .EntireColumn.Hidden = True
            End With


        Next
    End With
           
    Format_Results = Success    'Normal end, no errors
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

January 25, 2010  6:06 PM

Adding Formatting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Now it’s time to add basic formatting.  Formatting is important.  Accountants must see currency values formatted their special way.  Dates must be presented according to regional standards.  Fortunately, adding formatting to your results is as easy as adding one more column to our Fields table and one more routine to our library.   See below:

Fields Table w/Format

Fields Table w/Format

You can add any one of the following formatting to your results:

Value Result
L Left align – the default for character results
R Right align – the default for numeric results
W Wrap text to fit
mm/dd/yyyy This is a typical date format. You can also use any data format string allowed by Excel
#,###/00 This is a typical number format. You can also use any number format string allowed by Excel
$#,##0_);[Red]($#,##0) This is a typical currency format in the US. You can also use any currency format string allowed by Excel
You get the idea – anything allowed by Excel as a format works

Below is the new routine to add to your library and the modifications to Macro1().

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
   
'   Format_Range:   Apply numeric formatting or alignment
'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
   
    Dim s As String
    Dim lCol As Long
    Dim lRow As Long
    Dim lRows As Long
   
    lRows = Range(sDataRange).Rows.Count
       
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            s = Range(sFieldRange).Cells(lRow, lCol)
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select
            End With
        Next
    End With
           
    Format_Results = Success    'Normal end, no errors
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

Macro1() new line in red:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Format_Results "Data", "Fields"
        Pivot_Template
    End If
End If


January 21, 2010  5:58 PM

Adding Sorting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Another common requirement for reports is sorting.  While this can be done in SQL with an ORDER BY clause, I like to use Excel to sort because the desktop PC usually has a much higer CPU-Power-to-Number-of-Users ratio than a database server.  Today, we add sort columns to the Fields table.

Fields Table w/Sort

Fields Table w/Sort

 Once again, we need to add the columns to our Fields table, redefine it (using Ctrl-Shift-N), add a routine, and modify Macro1(). 

Function Sort_Data(sDataRange As String, sFieldRange As String) As Boolean
'   Sort_Data:          Sorts data based on settings in the field definition table
'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = range containing data's field definitions
'   Example:            bResult = Sort_Data("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    Sort_Data = Failure         'Assume the Worst
    On Error GoTo ErrHandler
    Settings "Save"             'Save current settings
    Settings "Disable"          'Disable events, screen updates, and calculation
          
    Dim i As Integer            'Generic Counter
    Dim lColOrd As Long         'Sort Order Column in FieldRange (1st, 2nd, etc.)
    Dim lColHdg As Long         'Column Headings Column in FieldRange
    Dim lColSeq As Long         'Sort Seq. Column in FieldRange (Asc, Desc)
    Dim lRow As Long            'Row in FieldRange
    Dim sField() As String      'Sort Field
    Dim sSequence() As String   'Sort Sequence
   
    With Range(sFieldRange)
        ReDim sField(.Columns.Count - 2)
        ReDim sSequence(.Columns.Count - 2)
        lColOrd = FieldColumn("S.Ord", sFieldRange)
        lColHdg = FieldColumn("Heading", sFieldRange)
        lColSeq = FieldColumn("S.Seq", sFieldRange)
        For lRow = 2 To .Rows.Count
            If Val(.Cells(lRow, lColOrd)) > 0 Then
                sField(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColHdg)
                sSequence(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColSeq)
            End If
        Next
    End With
   
    With Range(sDataRange)
        Worksheets(.Worksheet.Name).Activate
        .Cells(2, .Column).Select
        For i = UBound(sField, 1) To 0 Step -1
            If sField(i) > "" Then
                Selection.Sort _
                    Key1:=sField(i), _
                    Order1:=IIf(UCase(Left(sSequence(i), 1)) = "A", _
                                xlAscending, xlDescending), _
                    DataOption1:=xlSortTextAsNumbers, _
                    Header:=xlYes, _
                    OrderCustom:=1, _
                    MatchCase:=False, _
                    Orientation:=xlTopToBottom
            End If
        Next i
    End With
    Sort_Data = Success
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Sort_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    Settings "Restore"     'Restore previous settings
    On Error GoTo 0
End Function

Add the Sort_Data routine (highlighed in red) to Macro1() as shown:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Pivot_Template
    End If
End If


January 18, 2010  6:15 PM

Preserving Column Headers and Key Values

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

When retrieving large amounts of data, we need to preserve displaying column headers and key values.  Key values should be on the left of your extract.  With that in mind, today we add column “Freeze” to our Fields Table. 

Fields Table with Freeze Column

Fields Table with Freeze Column

“Freeze” tells our routines which columns to keep in view no matter where the user scrolls to.  If you don’t want any, I recommend putting a “Y” in your first field.  This will preserve the column headings at minimum.  You can only indicate one field (or none) to freeze.

Add “Freeze” as shown to your fields table and remember to resize the named range “Fields” by placing your cursor on cell “A3″ and pressing Ctrl-Shift-N.

Below is the “Freeze Panes” routine and a minor modification to our Macro1().

 

Function Freeze_Pane(sDataRange As String, sFieldRange As String) As Boolean
'   Freeze_Pane:        Locks columns and rows from scrolling
'                       (Used to keep column headers and key data displayed)
'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = Range containing data's field definitions
'   Example:            bResult = Freeze_Pane("Data", "Fields_Data")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Freeze_Pane = Failure          'Assume the Worst
    On Error GoTo ErrHandler
    Settings "Save"     'Save current settings
    Settings "Disable"  'Disable events, screen updates, and calculation
       
    Dim lCol As Long
    Dim lRow As Long
   
    Worksheets(Range(sDataRange).Worksheet.Name).Activate
    ActiveWindow.FreezePanes = False
   
    With Range(sFieldRange)
        lCol = FieldColumn("Freeze", sFieldRange)
        For lRow = 2 To .Rows.Count
            If UCase(Trim(.Cells(lRow, lCol))) = "Y" Then
                Range(sDataRange).Cells(2, lRow).Select
                ActiveWindow.FreezePanes = True
            End If
        Next
    End With
    Settings "Restore"     'Restore previous settings
    Freeze_Pane = Success
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Freeze_Pane - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function 

Modifications to Macro1().
Find the line beginning with If NameExists and replace it with this:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Pivot_Template
    End If
End If


January 14, 2010  6:02 PM

Adding SQL Formulas to the Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post we linked the Fields Table to some code that translated the table into an SQL Select clause.  Most often, that’s enough.  But sometimes we need to manipulate the data before loading it into our result spreadsheet.  We can do that by adding SQL functions like MID( ), ROUND( ), SUM( ), or in the case below, a simple concatenate

Fields Table w/SQL Func.

Fields Table w/SQL Func.

In this example, I have added a column “SQL Func.”, hit Ctrl-Shift-N (to resize the named range “Fields” to include the new column), and entered a simple formula to combine the customer’s last name with the first name.  The result will look like “Hatmaker, Craig”.

NOTE: Microsoft’s Access and SQL Server concatenate with the “+” (plus) sign.  DB2 and Oracle use “||”.  Oracle also uses CONCAT(a, b) as does MySQL.

NOTE: Some SQL functions aggregate multiple rows of data, like MAX( ), MIN( ), COUNT( ), etc.  These SQL functions require a GROUP BY clause.  If you use one of them, you must add the proper GROUP BY clause to your SQL Select Statement or it will fail. 

Now here’s the nice part.  There’s no coding this time.  This feature/functionality (without the aggregating functions) was included in the code from the last post.


January 9, 2010  12:04 PM

Adding Tables and Columns to the Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In my last post I provided some simple tools to manage Excel tables.  This was in preparation for creating and incorporating a “Fields Table” into our reporting system.  The advantages of the Fields table are enumerated in the prior posts.  So lets start using it.

The first portion of our Fields table are the basic elements of any database function:

  • Database tables and their aliases
  • Table columns and their column headers. 
Basic Fields Table

Basic Fields Table

By putting them in a table, we no longer have to write that tedious portion of our SQL statement AND it facilitates collaborative development with our users who can sit down with us and readily understand how changing the row order can rearrange the column order and changing the heading value changes the column headings in their final product.  They feel involved.  They feel empowered.  They gain a sense of ownership.  You gain their trust.

NOTES:

  • If your Table or Field has spaces, you must enclose it in brackets or “`”.   
  • If you change the Heading, you must also change the field names in your Pivot Tables macro to match.
  • Aliases are optional, but if you use one in your “From” clause, you MUST use the same one here. 

Instructions:

  1. Create a new tab on your spreadsheet and label it “Tables”
  2. Populate the cells as you see in the picture above (formatting is optional).
  3. If you didn’t do it last time, attach the macro “Create_Named_Range” to Ctrl-Shift-N using the “Macro” option from the “View” or “Developer” menus (Office 2007) then . . .
  4. Put the cursor in cell A3 and press Ctrl-Shift-N to name the range “Fields”.  Click OK.
  5. Go to the VBE (Alt-F11) and copy the code below and add it to your module.

Below you will find:

  • Build_SQL_Select_Fields - reads the fields table and creates a portion of our SQL Select statement
  • FieldColumn – used by Build_SQL_Select_Fields to find the column of a field in an Excel table  
  • Macro1() – Modified to use Build_SQL_Select_Fields .

Function Build_SQL_Select_Fields(sFields As String) As String

'   Build_SQL_Select_Fields: Build from an XL table a string to
'                            use as an SQL "Select" list of fields
'   Parameters:
'       sFields     Name of range containing field definitions
'   The "sFields" table range MUST contain:
'       Table:      Database File/Table name
'       Field:      Database Field/Column name
'   The "sFields" table range MAY contain:
'       Alias:      Database File/Table Alias (ex: L for table LOADS)
'                   WARNING:  Do not omit Alias if alias used in FROM clause
'       Heading:    Column heading for field in Excel report
'       SQL Func.:  It may contain a valid SQL function such as:
'                   SUM(?)          Totals the values in this field.
'                   TRIM(?)         Removes spaces
'                   SUBSTR(?,#,##)  Takes a substring from the field's value
'                   "?" is required.  It identifies where Field/Column name goes
'   Example:        sSQL = "Select  " & Build_SQL_Select_Fields(sFieldRange) & vbCr
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   01/21/10 CWH  Changed "As" section to remove requirement for
'                 brackets and quotes in Heading column for headings with spaces
    On Error GoTo ErrHandler
    Build_SQL_Select_Fields = ""    'Assume Something went wrong
   
    Dim lRow As Long
    Dim lCol As Long
    Dim lColA As Long
    Dim sSQL As String
    Dim sCRTB As String
       
    sCRTB = vbCr & vbTab & vbTab
   
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Add a comma if this not the first field
            sSQL = sSQL & IIf(sSQL > "", ", " & sCRTB, "")
           
           'Add Alias
            lCol = FieldColumn("Table", sFields)
            If lCol > 0 Then sSQLField = Trim(.Cells(lRow, lCol))
           'Use File/Table name if Alias didn't exist
            If sSQLField = "" Then
                lCol = FieldColumn("Table", sFields)
                If lCol > 0 Then sSQLField = .Cells(lRow, lCol)
                If UCase(Trim(sSQLField)) = "*NONE" Then sSQLField = ""
            End If
           
            'Add the Field/Column to the string
            sSQLField = sSQLField & _
                IIf(sSQLField > "", ".", "") & _
                .Cells(lRow, FieldColumn("Field", sFields))
           
            'Replace double quotes with single quotes
            sSQLField = Replace(sSQLField, """", "'")
           
            'Embed result in "Select Function" if specified
            lCol = FieldColumn("SQL Func.", sFields)
            If lCol > 0 Then _
                If InStr(1, .Cells(lRow, lCol), "?") > 0 Then _
                    sSQLField = _
                        Replace(.Cells(lRow, lCol), "?", sSQLField)
           
            'Add "As" if a column heading is specified
            lCol = FieldColumn("Heading", sFields)
            If lCol > 0 Then
                s = Trim(.Cells(lRow, lCol))
                If s > "" Then
                    If InStr(1, s, " ") > 0 Then s = "[" & s & "]"
                    sSQLField = sSQLField & " as " & s
                End If
            End If           
            sSQL = sSQL & sSQLField
        Next lRow
    End With
    Build_SQL_Select_Fields = sSQL
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Select_Fields - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Public Function FieldColumn(sField As String, sRange As String) As Integer
      
'   FieldColumn:    Determine a field's relative column number within a named range
'   Parameters:     sField - The Column Heading we're looking for
'                   sRange - The name of the range where it should be
'   Example:        lCol = FieldColumn("Name", "Customers")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    FieldColumn = 0     'Assume not found
   
    Dim c As Range
   
    If Trim(sField) > "" Then
        Set c = _
            Worksheets(Range(sRange).Worksheet.Name).Range(sRange).Rows(1).Find( _
            sField, , , xlWhole, xlByColumns, xlNext, False)
        If Not c Is Nothing Then FieldColumn = c.Column - Range(sRange).Column + 1
    End If
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "FieldColumn - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

Find the SQL= line in Macro1() and change it to this (red portion is to highlight change only):

           
sSQL = "SELECT  " & Build_SQL_Select_Fields("Fields") & vbCr & _
       "FROM    Customers C, Orders O, " & vbCr & _
       "        `Order Details` D, Products P " & vbCr & _
       "WHERE   O.`Customer ID` = C.ID " & vbCr & _
       "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
       "  AND   D.`Product ID`  = P.ID " & vbCr & _
       "  AND   O.`Order Date` Between #" & _
                Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                Format(.pTo, "mm/dd/yyyy") & "# " & vbCr & _
        Build_SQL_ID("O.`Customer ID`", Trim(.pID1), False) & vbCr & _
        Build_SQL_ID("P.`Product Code`", Trim(.pID2), True)


December 31, 2009  9:08 AM

Creating the Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

I hope you had a Merry Christmas and will have a great new year.  My heart goes out to the drivers and office workers of Arrow Trucking.  ABC News reported on Christmas day that Arrow Trucking went bankrupt.  Management cancelled driver’s fuel cards leaving them stranded miles from home.  Management handled it poorly.  And while the economy is a huge factor in this tragedy, we, as employees need to remember that our company’s success or failure depends greatly on how efficiently we do our job.  That’s what this blog is all about.  It’s about delivering real value at the lowest cost.  I hope you will continue to follow this blog and employee these methods.  If you have better methods, please share through comments.  Thanks.

Last post I introduced an Excel table that holds the definitions for our reports.  Using a table instead of code simplifies and speeds adapting our report to other needs, facilitates collaboration with our customers, and concentrates the bulk of changes into an easy to modify form.  Its value is tremendous.  The cost to setup initially is significant.  Fortunately we don’t need to add everything all at once.  We can cut the table into chunks and derive value from those chunks fairly quickly.

The first chunk will concentrate on four of the first few columns:

Column Description
Table Name of the database table that contains the field (see “Field” column)
Alias An abbreviation of the table name. Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS. This shortens the select statement (improving readability) while identifying which field comes from which table.
Field Name of the database field/column in the table (see “Table” column)
Heading Column heading for the field displayed in the worksheet.

We will use these columns to build a portion of our SQL Select clause for us.

Before we get started, I want to introduce a little utility that will help us as we adjust the table because we’re going to be adjusting it a lot.  The utility is a macro that helps create a named range from data entered on a spreadsheet.  It’s called: Create_Named_Range. 

Create_Named_Range is roughly the equivalent of highlighting an area on the spreadsheet and using Excel’s “Define Name” menu option.  It adds two niceties.  It assumes the table’s name is in the cell two rows above the table and finds the area for you.  I tie the macro to Ctrl-Shift-N so all I have to do is enter the table into my spreadsheet, label it, put the cursor in the upper left cell of the table, and press Ctrl-Shift-N.  The macro discovers my table’s area and names it with the table’s label.

Create_Named_Range requires a function called Fix_Names.  Fix_Names replaces or removes any illegal characters from a string so Excel can accept it as a name.  I’ve included both macros below:

Public Sub Create_Named_Range()
'   Create_Named_Range: Create a named range
'   Parameters:     None
'   Example:        This is meant to be attached to Shift-Ctrl-N. 
'                   The user positions the cursor in the upper
'                   left corner of a table, presses Shift-Ctrl-N,
'                   a prompt asks for the range's name
'                   (it will default to any entry in the cell 2 rows up),
'                   then this routine finds the bottom and right
'                   coordinates for the table.  The left most column
'                   MUST contain data in every row and the top must row
'                   MUST contain data in every column.
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Dim lCol As Long
    Dim lCols As Long

    Dim lRow As Long

    Dim lRows As Long

    Dim sName As String
   
    sName = InputBox("Enter Range Name:", _
                     "Create Named Range", _
                     IIf(Selection.Row > 2, _
                         Selection.Cells(-1, 1), _
                         "Data"))
    sName = Fix_Name(sName)
    With Selection
       'Find last column
        lRow = 1
        lCol = 1
        Do While .Cells(lRow, lCol) <> ""
            lCol = lCol + 1
        Loop
        lCols = lCol - 1
       'Find last Row
        lRow = 1
        lCol = 1
        Do While .Cells(lRow, lCol) <> ""
            lRow = lRow + 1
        Loop
        lRows = lRow - 1
        Names.Add sName, Range(.Cells(1, 1), .Cells(lRows, lCols))
    End With
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Create_Named_Range - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, _
        Err.HelpContext
    On Error GoTo 0
End Sub
 
Public Function Fix_Name(sName As String) As String
'   Fix_Name:       Conforms a string so it can be used as a
'                   field/column name for database functions
'   Parameters:     sName - The string to be conformed
'   Example:        sColumnName = Fix_Name("1st deposit %")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Fix_Name = sName
   
    Dim l As Long
           
    sName = Replace(sName, "#", "_NUM")
    sName = Replace(sName, "$", "_AMT")
    sName = Replace(sName, "%", "_PCT")
    sName = Replace(sName, "=", "")
    sName = Replace(sName, "-", "_")
    sName = Replace(sName, ",", "")
    sName = Replace(sName, ".", "_")
    sName = Replace(sName, ":", "")
    sName = Replace(sName, "/", "")
    sName = Replace(sName, "\", "")
    sName = Replace(sName, "'", "")
    sName = Replace(sName, " ", "_")
    Select Case Left(sName, 1)
        Case Is = 1
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "ST", 3, 1)
            sName = "FIRST" & Right(sName, l)
        Case Is = 2
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "ND", 3, 1)
            sName = "SECOND" & Right(sName, l)
        Case Is = 3
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "RD", 3, 1)
            sName = "THIRD" & Right(sName, l)
        Case Is = 4
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "FOURTH" & Right(sName, l)
        Case Is = 5
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "FIFTH" & Right(sName, l)
        Case Is = 6
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "SIXTH" & Right(sName, l)
        Case Is = 7
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "SEVENTH" & Right(sName, l)
        Case Is = 8
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "EIGHTH" & Right(sName, l)
        Case Is = 9
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "NINETH" & Right(sName, l)
    End Select
    Fix_Name = sName
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Fix_Name - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

In the next post we will start using the table.


December 19, 2009  2:23 PM

The Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Fields Table

Fields Table

The day before yesterday, one of my internal customers asked for a listing of an external customer’s orders.  Orders had to be selected based on dates, various subsidiary customer accounts, order statuses, etc.  and totalled along specific lines.  Our external customer wanted us to provide this to them because their own systems professionals couldn’t, or wouldn’t.  My internal customer said he needed it quick and that meant he really, really needed it the next day.  We handed it to him within a few minutes.  My customer beamed how happy he was that we could promise these reports and deliver.  It’s a competitive advantage.  When our external customers evaluate who to procure services from, this level of customer service may be the convincing factor that tips their business our way.

Last post I talked about how to take our reporting to the next level using Excel Tables.  Today, I’m introducing that table – the “Fields” table.  At right is a thumbnail of the table.  If you click it, you can see it in its entirety (It’s too wide for this blog’s format).  It contains most of the parameters necessary to describe data elements for a data extracts.  Here are descriptions of the various columns:

Column Description
Table Name of the database table that contains the field (see “Field” column) 
Alias An abbreviation of the table name.  Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS.  This shortens the select statement (improving readability) while identifying which field comes from which table. 
Field Name of the database field/column in the table (see “Table” column)
Key Identifies fields/columns as part of the table’s unique key 
Heading Column heading for the field displayed in the worksheet. 
S.Ord. (Sort Order) Identifies which fields are sorted on first, second, third, etc.  There is no limit to the number of sort fields. 
S.Seq. (Sort Sequence) Identifies “Ascending” or “Descending” sort sequence for the column.
Freeze Identifies which field to freeze Excel’s window pane on.  This is usefull for keeping column heading and key values displayed at all times, no matter which cell you’re in. 
Sel.Func. (Selection Clause Function) Identifies an SQL formula to use on the field.  Use a “?” to identify where the Field appears in the formula. 
Hide Use “H” to hide the column completely.  Use “C” to “conditionally” grey out repetitive values.  This is useful for extracts that contain many records for the same key value.  Duplicate key values can be greyed out leaving the first value in the list to stand out. 
Width Restricts a column to a specific width.  This is useful for improving readibility when long text fields are used. 
Format For numeric fields you can specify any numeric format understood by Excel.  For character fields you can specify alignment: L = Left, R=Right, C=Centered, W=Wrap Text.
Default For reports, this would be better labeled as “Excel Formula”.  Use this to embed calculated columns into your result. 
Input Not used for reports.  For update spreadsheets this identifies the conditions under which this field may be changed: A=Adding records, C=Changing existing records or adding records, P=By program control only (users are locked from making any changes)
Required Not used for reports.  Identifies fields that cannot be left blank.  A common synonym is “mandatory.” 
V.Type (Validation Type) Not used for reports.  Identifies rules to apply to insure values entered are valid.  
V.Tbl (Validation Table) Not used for reports.  Identifies an Excel or database table to use to validate values. 
Upd. Func. (Update Function) Not used for reports.  Identifies an SQL function to use when updating values in tables
Notes Use this the same way you would use program comments. 

I put this table in a worksheet called “Tables”.   This table is a powerful RAD (Rapid Application Development) type tool.  Once I get the basics of the report up, I can then bring in my customer who will almost always want to change the output based on either something they didn’t communicate, or based on a realization they had when they saw the results of what they asked for.  I no longer dread those moments.  I actually look forward to them.  Because while we’re talking, I see the excitement on their faces as within seconds their desires are realized – simply by adding or adjusting a row in this table.  I can easily add columns to their results by adding rows to this table – without changing my code.  I can change the initial sort to anything they want – without changing my code.   I can change column headings, add formats, and even add calculated columns – all without changing any of my code – and all within a matter of seconds.  My customers are thrilled.

In the next post we will start adding code to take advantage of this table in order to generate much of our SQL Select statement for us.


December 15, 2009  6:13 PM

75% Done

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

As far as writing reports in Excel is concerned, using just what we’ve discussed up til now get’s us about 75% of where I want to take us.  The next 25% is more tedious to work through, but the advantages are worth it.

Quick Change SQL
The first advantage is simplifid SQL management.  This is achieved by listing database tables and fields/columns in an Excel table (called Fields), then using code to assemble the SQL for us.  With the field names in an Excel table, we can sit down with the user and quickly customize their report.  We can easily change the column order, include more fields, drop fields, change the column headings, change sort sequences, add formulas, add formats, and chose a field to freeze the panes on – all without coding – well – that is – without additional coding beyond the library of routines needed to support this.

Excel Formulas
Adding formulas is huge.  It plays to Excel’s unique strengths.  What if our customer wants to know what day of the week orders hit most?  That isn’t in the database.  The date is.  But to get the day of the week requires a formula, one that happily already exists in Excel.  What if our customer wants to know the aging of his receivables.  The age isn’t in the database.  But the date is.  And happily, subtracting the database date from today is a snap in Excel. 

One of the routines we will add facilitates inserting complex Excel formulas from our Fields table into our retrieved records.

SQL Formulas
I find that SQL formulas work best for making odd date fields standard.  I’ve seen Julian dates, seconds elapsed since some beginning date dates, year/month/day dates all crammed together in one numeric field, date and time stamp dates when all you want is the date, and the AS/400′s (iSeries, Power i, …)  system dates with a century flag.  Users don’t like them.  They want standard dates.  Excel doesn’t like them.  Excel’s formulas only work with standardized dates.  

One of the routines we will add facilitates inserting complex SQLformulas from our Fields table into our retrieved records.

Formats
Excel has rich formatting capabilities.  This can really improve the readability of our results.

We will add routines to apply numeric formatting, left/right/center align data, wrap data, hide columns, limit column widths, and conditionally grey out repetitive data in rows from values in our Fields Table.

Sorting
Often SQL sorts data just fine.  But there are two reasons not to use it.   First, why burden your database server with that task when your PC’s processor is dedicated to you alone?  Let Excel do it.  Second, what if the sort order you want is based on a formula result?  Excel can sort that too.

We will add routines to sort in any combination of orders and with as many columns as you have fields identified in our Fields Table.

Freezing
When working with large databases, it is important to keep the column headings always in view.  You may also want to keep some of the row keys in view as well.  Excel’s “Freeze Pane” function handles this nicely.

We will add a routine to apply freezing to any field in our Fields table.

Future Use
When we get into updating databases from Excel, (assuming your auditors allow such heresy) we will need some additional functionality from our Fields table, like:

  • Lock certain fields from changes
  • Make some fields required
  • Validate fields against tables
  • etc.

Next post will show what this table looks like, then we’ll get down to adding more routines to our library to deliver on its promise.


December 10, 2009  6:03 PM

It’s Time to Play

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post, we integrated frmPrompt into Macro1(), but we didn’t take full advantage of what frmPrompt offers.  We limited values to just one for each parameter.  Today we are going to add the ability to filter our database using wildcards or lists.

We will be adding a new function called Build_SQL_ID.  Build_SQL_ID looks at the value from frmPrompt and determines if our user intended to:

  • Filter at all
  • Filter on just one value
  • Filter on a list
  • Filter based on a wildcarded value

I hope you can see the power this gives our customers to mine information from their data.

We will be changing the SQL statement slightly to let Build_SQL_ID add the appropriate comparison.  We’ll look at Build_SQL_ID first, then be sure to check the changed SQL statement at the end.  Add Build_SQL_ID to your module and replace the SQL statement in Macro1() with this new one.

After completing this, click your easy button to bring up frmPrompt, key in different values for customers or products, select multiple values from lists, use wildcards, or leave the fields blank alltogether.  See how it changes your pivot table and chart.  It’s time to play.  Enjoy.

Public Function Build_SQL_ID(sField As String, sValue As String, bAddQuotes As Boolean) As String
'   Build_SQL_ID: Create a field's comparison string
'                 If sValue = "*ALL" then no comparison string is desired
'                 If sValue contains a wild card then "LIKE" must be used
'                 Otherwise "IN" must be used
'   Parameters:   sField     Table's column name
'                 sValue     The value to filter results on
'                 bAddQuotes Set to True for character values
'   Example:      sSQL = "Select * " & _
'                        "From   Employees " & _
'                        "Where  Status = 'ACTIVE' " & _
'                         Build_SQL_ID("STATE", "'MN', 'VA', 'ND'")
'     Date   Init Modification
'   11/10/09 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_ID = ""
   
    If Trim(sField) > "" And Trim(sValue) > "" Then
        If bAddQuotes Then sValue = "'" & _
            Replace(Replace(sValue, "'", ""), ",", "','") & "'"
        Build_SQL_ID = _
            IIf(UCase(sValue) = "*ALL", "", _
                IIf(InStr(1, sValue, "?") > 0 Or InStr(1, sValue, "%") > 0, _
                   "  And   " & sField & " like " & sValue & " ", _
                       "  And   " & sField & " in (" & Trim(sValue) & ") " _
                    ) _
                )
    End If
ErrHandler:
  
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_ID- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

 

New SQL statement

            sSQL = "SELECT  O.`Order ID`, O.`Customer ID`, " & vbCr & _
                   "        O.`Order Date`, C.`First Name`, " & vbCr & _
                   "        O.`Ship State/Province`, D.Quantity, " & vbCr & _
                   "        P.`Product Name` " & vbCr & _
                   "FROM    Customers C, Orders O, " & vbCr & _
                   "        `Order Details` D, Products P " & vbCr & _
                   "WHERE   O.`Customer ID` = C.ID " & vbCr & _
                   "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
                   "  AND   D.`Product ID`  = P.ID " & vbCr & _
                   "  AND   O.`Order Date` Between #" & _
                            Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                            Format(.pTo, "mm/dd/yyyy") & "# " & vbCr & _
                    Build_SQL_ID("O.`Customer ID`", .pID1, False) & vbCr & _
                    Build_SQL_ID("P.`Product Code`", .pID2, True)
 


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: