Beyond Excel: VBA and Database Manipulation

Jan 9 2010   12:04PM GMT

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)

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: