Beyond Excel: VBA and Database Manipulation


April 5, 2010  11:24 AM

Additions to the Fields Table for Update Spreadsheets

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

 As promised in the last post, here are descriptions of each column in the fields table.

Fields Table

Fields Table (click to enlarge)

Heading Description
Table Database table/File name. Every entry in this table must have a table name or the word “*None”. “*None” indicates a literal or an Excel Function will placed in this column instead of a value from the database table.
Alias An abbreviation you want to use for the table/file name. This is highly recommended.
Examples of abbreviations might be “E” for an employee master, or “C” for customers. if “*None” is in the Table column, you must not enter an abbreviation.
Field Field/column name. If “*None” is in the table column this must be a literal value like “A”. If this column is to contain an Excel function, this column should contain “”.
Key ”K” designates the field/column is part of the table/file primary key. The Update and Delete routines need to know what the key values are in order to update your database properly.
WARNING! Failure to add all keys can result in: Delete operations removing more; or Update operations changing more records than intended. Do not allow changes to primary key values as this will cause Updates and Deletes to fail. Take care to designate all values that make this record unique.
Heading Spreadsheet column heading to display.
NOTE: These column headings MUST be compatible with your database’s conventions.
S.Ord A number to designate which fields/columns are sorted first, second, third, etc.
S.Seq ”A” designates ascending sort order, “D” designates descending
Freeze ”Y” designates all columns to the left of this column will always display no matter how far the user scrolls to the right.
NOTE: Only 1 field should have this value set.
SQL Func. An SQL function to apply to the field on reading the database
Hide ”H” hides the column entirely. “C” (conditional) grays out repeating values.
Width Enter a number to restrict the column’s width when displayed
Format Any Excel format string to apply to the results
XL Func. An Excel formula to use instead of a table field/column. Result columns can be designated in the Excel formula by enclosing either the “Field/Column” name or “Heading” in brackets {}. “Field/Column” names should be used when possible because “Headings” can change.
NOTE: Table must be set to “*None”; Alias must be empty; and Field must be double quotes
Input
P Designates that only the program can change this column (the user is locked out).
C Designates the user can change this column for new and existing records. 
NOTE: No key fields should use this setting.
A Designates the user can change this column for new records only
Required ”Y” designates blank entries are not allowed
V.Type Validation Type

# Only allow numbers
$ Same as #
1-### Restricts text entries to this many characters
ACD Use for “ACD” columns only (restricts entries to “A”, “C”, “D”, and “X”).
DATE Restricts entries to valid dates
DB Requires entry to be a value in a database table.  Should the entry not be found, frmSelect can be used to help the user find the proper code.  If you use this, you must add programming code to routine “DB_Lookup” to validate the entry (or modify the template to suit). 
TIME Restricts entries to valid time values.
XLC Requires entry to be a value in an Excel “Code” table. “Code” tables consist ONLY of “Code” and “Description” columns.  Should the entry not be found, frmSelect will help the user find the proper code.
XLT Requires entry to be a value in an Excel “Type” table. “Type” tables consist ONLY of “Type”, “Code” and “Description” columns, where only codes of a certain type are valid.  The “type” must also be a column in the entry area.  Should the entry not be found, frmSelect will help the user find the proper code.
YORN Restricts entries to “Y” or “N”
V.Tbl Validation Table: When V.Type is:

DB It is recommended that you put the name of the database file/table containing values to validate against here – however – you can use any string that “DB_Lookup” can test against in a “Select Case” statement to direct execution to your code.
XLC Put the name of the Excel “Code” table range here.  Use XLC when the number of codes is relatively small (under 100?) and extremely static (doesn’t change).  An example might be a listing of status codes such as “A” for Active, “T” for Terminated, “S” for Suspended.  If the list of values is long OR dynamic (changes often), don’t use an Excel range to store values.  Use a database file/table instead along with DB to lookup values.  This will make maintenance of your spreadsheet much simpler.
XLT Put the name of the Excel “Type” table range here along with the column name of the “Type” enclosed in curly brackets.  The “Type” column should be to the left (entered prior) of the “Code” column. The same advice in XLC about the number of codes and dynamic nature of your data applies to this. 
EXAMPLE: Products {Category}.   In this example, only “Products” in the same “Category” as the current record are valid and can be selected.  The “Category” column must be entered and validated before the “Products” column.
Upd.Func. A programmed function to apply to the value before updating. Preprogrammed functions include:

DATE2JULIAN Converts normal dates to YYYYDDD (Julian) format
DATE Prepares the value for storage as a DATE data type in the database
HHMMSS Prepares the value for storage as a TIME data type in the database
HHMM Prepares the value for storage as a TIME data type without seconds
TRIM Prepares the value for storage as character data and removes blanks.
Always use this for character based fields so quotes surround the value in SQL statements
Format String Enter any valid Excel format string to convert numbers (such as social security numbers) to text with formatting (dashes) before storing as character data
Notes Any notes you may want to associate with the entry. The program does nothing with notes.

March 23, 2010  5:09 PM

Updating Databases from Excel – First Looks

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Here is an example of an update spreadsheet.  We will use it in our tutorial. It updates the target stocking level (“Target Lvl”) for Products in the Northwind database.  It could just as easily, with different configuration, update an item master in SQL Server, iSeries DB2, Oracle, or just about any other relational database.
Update Example

Update Example

Normally, update spreadsheets have three buttons, “New”, “Load”, and “Post”.  I’ve trimmed this example to just two buttons for now.  We’ll add the “New” button later.
 
When the user first opens the spreadsheet, it is blank except for the buttons.  The user can click “Load” and select which products to change, or they can load all products as I have done in this example.  As followers of this blog might guess, the “Load” button acts just like our reports except for one formatting difference – the yellow cells. 
 
Yellow cells are the only cells that can be changed.  We use equivalent functionality to Excel’s “Format cell” > “Protection” > “Locked” option, along with the ActiveSheet.Protect Contents:=True to block users from changing anything outside the yellow areas.  We also use these cell qualities to guide keyboard navigation (cursor keys) to the next open cell.
 
Another change you might have noticed is the “ACD” column.  “ACD” stands for “Add, Change, Delete”.  An “X” indicates that a record already exists in the database.  When the user changes the “Target Lvl” column, the appropriate “ACD” cell changes to “C”, indicating the record changed.  When the user clicks the “Post” button, our spreadsheet finds rows with an “ACD” set to “C” and executes an SQL Update statement for us.  Fortunately, we can use the Fields Table to help take the drudgery out of constructing SQL statements.  Here is what the expanded “Fields Table” looks like.
 
Fields Table

Fields Table

 You can click the graphic to display a larger version of it.  The next post will explain the new fields.
 


March 17, 2010  8:19 PM

Updating Databases from Excel – The Basics

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post I encouraged readers to approach updating databases with care and professionalism. Some may wonder why then, use Excel at all if updates require the level of care in traditional programming. The answer is that Excel is a standard means for exchanging data.

As you know, the world is moving to software as a service, cloud computing, and disparate data systems. In other words, much of the data you need to run your business is, or soon will be, on the web. This data is usually in need of cleansing, transformation, denormalization, and integration with your host systems. The good news is just about every website that offers any kind of export, supports Excel, CSV, or XML formats – all of which Excel handles nicely. And by using the functionality available in Excel, you can shorten the chain of objects required to cleans and move data from website to your own server to just one object – no temporary files, no server side programs, no triggers, scheduled events, system administrator involvement – just one Excel spreadsheet.

A real world example is just now being realized in the trucking industry. The government has mandated a new means for scoring drivers and carriers. The new scoring system is called CSA-2010 and is intended to identify which drivers are likely to be involved in an accident. The new scoring system will (in my opinion) determine which drivers continue to work and which carriers continue to do business. The data for CSA-2010 comes from tens of thousands of police officers in the field from various jurisdictions who, when they pull over a trucker for a violation or at a scale, record an inspection result and upload it through a series of systems that are ultimately collected at the federal level. Carriers need this information integrated with their driver performance systems so they can take action to remediate at risk drivers and so they can improve their chances for earning business in an already cut-throat market. The problem is, with so many sources, the data often isn’t clean.

No problem. The data can be exported through XML to Excel where it is extremely easy to find inconsistencies, fix, cross check with other systems (using the data extract methods in the prior posts), and, finally, uploaded onto servers where it becomes part of the overall driver performance system.

The basic process is to read through an Excel range, pluck out values, match them to column/field names, and execute a simple SQL Insert (or Update, or even Delete) statement like:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (Cell(lRow, 1), Cell(lRow, 2), Cell(lRow, 3),...)

Now constructing such Insert (or Update) statements can be tedious so as we have in the past, we will be using the “Fields” table and code to automate most of that for us.


March 11, 2010  8:43 PM

Updating Databases from Excel

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

It seems almost every web portal that lists data includes an export to Excel, CSV, or XML, or combinations thereof.  What we are going to start exploring will enable you to capture that data, massage it, save it, and merge it with existing systems – using nothing more than Excel and ODBC.

We’re moving into controversial waters here.  It has been traditionally accepted that nobody should update a database except through a predefined program – for good reasons.  Updating servers from ODBC and Excel is considered by many auditors to be a violation – for good reasons.  DBA’s cringe at the notion of users updating server databases - for good reasons - because “fools rush in where angels fear to tread” (English poet Alexander Pope’s An essay on criticism, 1709).

In the last few weeks I have seen a few requests in the Question/Answer section of this site by people seeking ways to update server databases, especially the AS/400′s (iSeries) database, from Excel.  “It’s possible,” I explain, “but the answer is far too complex for a Question/Answer forum.”  Indeed it is, because if you want to avoid disaster, you MUST do the kinds of things traditional programmers have always done – check the data for sanity and silly mistakes before posting it to the database.

The techniques we will discuss will be along those lines.  We will see not only how to update the database, but also how to make sure the user put data in the proper columns, and that dates are valid, numeric fields contain numbers, text fields do not exceed field widths, codes and IDs match those in code tables and/or master files. 

We will be building on what we have done so far – so if you are new to this blog, please go back to the beginning and join us after you have built your library of routines given in prior posts.  We’ll be here waiting.

Function or Subroutine Module, Class, or Form Located in Post
Worksheet_BeforeDoubleClick Sheet1 Drill Down Part 2
Get_Data Sheet1 Drill Down Part 2
Prompt Sheet1 Drill Down Part 2
Build_SQL_Group_By modGeneral Group By and Aggregate SQL Functions
Add_XLFormula modGeneral Adding Excel Formulas – Part II
Parse_XLFormula modGeneral Adding Excel Formulas – Part II
Format_Results modGeneral Setting Column Widths or Hiding Them
Sort_Data modGeneral Adding Sorting
Freeze_Pane modGeneral Preserving Column Headers and Key Values
Build_SQL_Select_Fields modGeneral Adding Tables and Columns to the Fields Table
FieldColumn modGeneral Adding Tables and Columns to the Fields Table
Create_Named_Range modGeneral Creating the Fields Table
Fix_Name modGeneral Creating the Fields Table
Build_SQL_ID modGeneral It’s Time to Play
All Code and Properties for… frmPrompt Asking for It
All Code and Properties for… frmDatePicker Looking for a Date?
All Code and Properties for … frmSelect_Multiple Searching for Codes
SQLLoad modGeneral Say Goodbye to QueryTables
SQLRead modGeneral Using ADO to Read a Database
Pivot_Template modGeneral Wrapping things up
Setup_PivotChart modGeneral Building a Library of Routines – Setup_PivotChart
Setup_Pivot modGeneral Building a Library of Routines – Setup_Pivot
Settings modGeneral Building a Library of Routines – Settings
NameExists modGeneral Building a Library of Routines – ?Exists
ShapeExists modGeneral Building a Library of Routines – ?Exists
WorkSheetExists modGeneral Building a Library of Routines – ?Exists
PivotTableExists modGeneral Building a Library of Routines – ?Exists
ChartExists modGeneral Building a Library of Routines – ?Exists
Create_Easy_Button modGeneral Code for “easy” Button


March 4, 2010  6:32 PM

Drill Down – Part 2

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Before we get to coding, let’s look at the final product.  When we click our ‘easy’ button, our spreadsheet prompts us for dates, customers, and products.  If we just take the defaults, everything will be loaded from the NorthWinds database – like this:

Drill Down Before

Drill Down Before

If we look in the ‘Orders’ column we see that some lines have more than one order supporting them.  If we double click anywhere in the second line (say cell A6 for example) our finished spreadsheet will display those two orders so we can know the detail behind our summary.  It will look like this:

Drill Down After

Drill Down After

Now, as mentioned in the previous post, we need to restructure our code a bit.  We need to eliminate the Macro1() in our module, and create three routines in the sheet’s class (for how to get to the sheet’s class review prior post).  So here is the first bit of code – constants that will make modifying this template for use on other databases easier:

Const sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                 "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
Const sData = "Data"
                 

These constants go at the very top of our sheet’s class.   And, as before, you will need to modify the path to your copy of the Northwind database (highlighted in red).

The next bit of code is the sheet’s double click event handler.  When you double click on worksheet ‘Data’, this even automatically fires.  All we need to do is wire it up to do what we want.  In this case, we want to load the detail associated with the Customer ID and Product Code on the line that is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
'   Worksheet_BeforeDoubleClick:  Excel's double click event handler
'   Parameters: Target  Cell that was double clicked
'               Cancel
'   Example:    <Automatically called when user double clicks worksheet>
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
   
    If NameExists(sData) Then
       
        Dim lColDta As Long
            lColDta = Range(sData).Column
        Dim lColCus As Long
            lColCus = lColDta + FieldColumn("Customer ID", sData) - 1
        Dim lColPrd As Long
            lColPrd = lColDta + FieldColumn("Product Code", sData) - 1
       
        Get_Data "Fields_Detail", _
                 Trim(Cells(Target.Row, lColCus)), _
                 Trim(Cells(Target.Row, lColPrd)), _
                 frmPrompt.pFrom, frmPrompt.pTo
    End If
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Worksheet_BeforeDoubleClick - Error#" & Err.Number & vbCrLf _
        & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Sub

 

As you can see, there’s not much to it.  But it requires a new routine ‘Get_Data’.  ‘Get_Data’ is half of our Macro1() – the part that doesn’t ask for any information but instead builds the SQL and submits it.  It looks like this:

Private Function Get_Data(sFields As String, _
                  sID1 As String, sID2 As String, _
                  dFrom As Date, dTo As Date) As Boolean
'   Get_Data:   Processes request from Prompt or Worksheet_BeforeDoubleClick
'   Parameters: sFields Name of the Field Definition table
'               sID1    Customer ID(s)
'               sID2    Product code(s)
'               dFrom   Ordered date range start
'               dTo     Ordered date range stop
'   Example:    If .pOK Then Get_Data "Fields", "8", "'NWC-14', 'NWCA-21'", _
'                                      Int(now())-30, Int(Now())
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
    Get_Data = Failure                  'Assume the Worst
    Dim sSQL As String
           
    sSQL = "SELECT  " & Build_SQL_Select_Fields(sFields) & 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(dFrom, "mm/dd/yyyy") & "# And #" & _
                    Format(dTo, "mm/dd/yyyy") & "# " & vbCr & _
            Build_SQL_ID("O.`Customer ID`", Trim(sID1), False) & vbCr & _
            Build_SQL_ID("P.`Product Code`", Trim(sID2), True) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By(sFields, "*")
    
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"
    If NameExists(sData) Then
        If Range(sData).Rows.Count > 1 Then
            Add_XLFormula sData, sFields
            Freeze_Pane sData, sFields
            Sort_Data sData, sFields
            Format_Results sData, sFields
            If sFields = "Fields_Detail" Then Pivot_Template
        End If
    End If
   
    Get_Data = Success                  'Successful finish
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Get_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

That should look very familiar to you.  The reason we split this from the part of Macro1() that asks for input is because we want only one routine to load data for us – whether the input comes from frmPrompt or from Worksheet_BeforeDoubleClick.

So now all we need to do is add back the part that sets up and displays frmPrompt.  It looks like this:

Private Sub Prompt()
'   Prompt:     Ask user for report parameters
'   Parameters: None
'   Example:    Prompt
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
    With frmPrompt
               
        .pDateLbl = "Ordered Dates"
        .pFromVisible = True
        .pFrom = "01/01/2006"
        .pToVisible = True
        .pTo = Format(Now(), "mm/dd/yyyy")
       
        .pID1Visible = True
        .pID1Lbl = "Customer ID(s)"
        .pTitle1 = "Select Customers"
        .pConnect1 = sConnect
        .pLblCode1 = "Code"
        .pSQLCode1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    ID like '%?%' " & vbCr & _
                     "Order By ID"
        .pLblDesc1 = "Company"
        .pSQLDesc1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    Company like '%?%' " & vbCr & _
                     "Order By Company"
       
        .pID2Visible = True
        .pID2Lbl = "Product Code(s)"
        .pConnect2 = sConnect
        .pLblCode2 = "Code"
        .pSQLCode2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Code` like '%?%' " & vbCr & _
                     "Order By `Product Code`"
        .pLblDesc2 = "Name"
        .pSQLDesc2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Name` like '%?%' " & vbCr & _
                     "Order By `Product Name`"
                    
       .Show                                         'Display the Prompt
        Do While .Visible                            'Wait on user
            DoEvents
        Loop
        If .pOK Then Get_Data "Fields", _
                              Trim(UCase(.pID1)), Trim(UCase(.pID2)), _
                              .pFrom, .pTo
    End With
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Prompt - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Sub

When you’re finished make sure to get rid of Macro1(), and then right click on the ‘easy’ button and assign it to macro ‘Sheet1.Prompt’. 

There’s one more advantage to structuring our code this way – almost all of the code that needs to be modified to use this template on just about any other database – AS/400, SQL Server, Oracle, MySQL, etc – on just about any table or set of tables – is contained in this one sheet.  This makes adapting this very quick and easy.


February 24, 2010  6:40 PM

Drill Down – Part 1

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows.  When we summarized the data, in a sense, we duplicated the functionality of a simple “pivot table.” 

Pivot Tables have one feature we didn’t duplicate.  It’s a feature that always astonishes new Pivot Tables users.  That feature is the automatic “drill down” that happens when a value is double clicked, revealing the underlying detail.  Users love this and it’s not tough to add it to the summarized sheet we created last post.  The secret is in Excel’s Worksheet_BeforeDoubleClick event handler.  Using this event handler, we can respond to the user double clicking on a data row and using information in that data row, pull up the detail supporting it. 

Now because we are wading into deeper VBA waters, it’s appropriate to introduce some more polished coding techniques.  We will be dumping Macro1() in Module1.  Instead we are going to break it up into three routines.

  1. Worksheet_BeforeDoubleClick – This will handle the drill down request
  2. Prompt – Presents the user with our prompt form when they press the “easy” button.
  3. Get_Data – Gets and presents the data based on the parameters from Prompt or Worksheet_BeforeDoubleClick

Worksheet_BeforeDoubleClick cannot reside module1.  It must be placed in the worksheet class.

Code in worksheet classes has some special properties we need to be aware of.  Code in a module is shared across ALL worksheets.  Routines in a worksheet are “local” to that worksheet.   That means, variables declared in, and code written in a worksheet class are unique to that worksheet.  Thus, we can have the same name for a routine in each worksheet with each routine meeting the specific needs of its own worksheet. 

The Worksheet_BeforeDoubleClick is a fine example of this.  Every worksheet has a Worksheet_BeforeDoubleClick routine.  By default, they are empty – so by default, they do nothing.  The Worksheet_BeforeDoubleClick in our Sheet1 will be different.  We will add code so it will “drill down” into the data.  But we ONLY want this behavior in Sheet1.  We don’t want our “drill down” routine to respond to double clicks in our “Fields” table, and we certainly don’t want it to override our Pivot Table’s drill down functionality.  Fortunately for us, a worksheet’s local event handlers ONLY respond to it and no other worksheet.

We also want to take advantage of this “local” nature of routines placed in the worksheet class.  As mentioned earlier, we are going to break up the Macro1() into three separate routines.  These routines will be specific to processing this particular dataset.  However, in some future project, you may want to have several worksheets in a workbook with each worksheet analyzing one set of data that, with the other sets, provides a suite of analytical tools.  By making the routines local, each worksheet can have its own version of Prompt and Get_Data with each version modified as needed to support the unique requirements of its own worksheet.

VBE Drill Down Project

VBE Drill Down Project

To add code to Worksheet_BeforeDoubleClick we must:

  1. Select Sheet1 from Project Explorer
  2. Select Worksheet from the Code Panel’s left drop down
  3. Select BeforeDoubleClick from the Code Panel’s right drop down

Excel will then provide a subroutine shell for us to add our specific code.   And that will be the subject of our next post.


February 19, 2010  5:38 PM

Group By and Aggregate SQL Functions

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

At this point, we have everything we need for about 95% of user reporting and analytical requests.  On rare occasions we need to summarize the data prior to putting it into our Excel spreadsheets.  This happens when the raw detail for an expected time frame exceeds Excel’s normal 65,536 row limit and is more detailed than required for proper analysis.  An example might be a request to review orders for an entire year by customer and product.  In moderately sized companies, such requests could easily exceed 65,536 records.

We can work around this limitation by aggregating records so that several database records are represented in Excel as one row.  We do this with SQL’s aggregate functions which include:

COUNT(*), SUM(field), AVG(field), MAX(field), MIN(field), FIRST(field), LAST(field)

If you use these functions you must add a GROUP BY clause.  I touched on this last month in the post Adding SQL Formulas to the Table.  The basic rule for the GROUP BY clause is:

All fields in the SELECT clause that are NOT one of the SQL Aggregate Functions, MUST be included in the GROUP BY clause. 

GROUP BY clauses are tedious and often a source of errors for the SQL novice.  If you omit or mis-transcribe any SELECT field, your SQL will fail.  That’s why SQL novices typically fail on their first attempts with GROUP BY clauses.  Since the GROUP BY clause is nearly a replica of the SELECT clause, creating them is tedious.  So of course we need to automate that to make our work easier, increase our productivity, and improve the quality of our products.  Here is a routine that does just that (with a Fields Table to use test your results).

Fields Table for GROUP BY example

Fields Table for GROUP BY example

 

Function Build_SQL_Group_By(sFields As String, sTable As String) As String
'   Build_SQL_Group_By: Build SQL "Group By" field list
'   NOTE: SQL Aggregate Functions include:
'         Avg(),Count(),First(),Last(),Max(),Min(),Sum()
'   Parameters:
'       sFields:    Field definition range name.  It must contain:
'
'           Field:     Field/Column names
'           Alias:     File/Table Alias (ex: L for the table LOADS)
'           Table:     Field's Table name
'           SQL Func.: SQL function/expression
'       sTable:     Table being grouped.  "*" indicates all fields from
'                   all tables will be grouped
'   Example:
'       sSQL = sSQL & "Group By " & _
'              Build_SQL_Group_By("Fields", "*")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_Group_By = "" 'Assume Something went wrong
    Dim lRow As Long
    Dim sSQL As String
    Dim sSQLFunc As String  'Value in the SQL Func.
    Dim sFld As String      'Field with Alias
   
    Dim lColTbl As Long
        lColTbl = FieldColumn("Table", sFields)
    Dim lColFld As Long
        lColFld = FieldColumn("Field", sFields)
    Dim lColSQL As Long
        lColSQL = FieldColumn("SQL Func.", sFields)
    Dim lColAls As Long
        lColAls = FieldColumn("Alias", sFields)
   
    If Trim(sFields) = "" Or Trim(sTable) = "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
           'Include fields for indicated table
            If (sTable = .Cells(lRow, lColFld) Or sTable = "*") Then
                sSQLFunc = Trim(UCase(.Cells(lRow, lColSQL)))
               'Include fields that are not an aggregate function
                If sSQLFunc = "" Or _
                   InStr(1, "AVG,COU,FIR,LAS,MAX,MIN,SUM", _
                         Left(sSQLFunc, 3)) = 0 Then
                   'Combine Field with Alias
                    sFld = IIf(Trim(.Cells(lRow, lColAls)) > "", _
                               Trim(.Cells(lRow, lColAls)) & ".", "") & _
                           Replace(.Cells(lRow, lColFld), """", "'")
                   'If SQL Function has a "?", replace with Field
                    If InStr(1, sSQLFunc, "?") > 0 Then _
                        sSQLFunc = _
                            Replace(sSQLFunc, "?", sFld)
                   'If SQL Function, use it instead of Field
                    If sSQLFunc > "" Then sFld = sSQLFunc
                    sSQL = sSQL & IIf(sSQL > "", ", ", "") & sFld
                End If
            End If
        Next lRow
    End With
   
    Build_SQL_Group_By = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Group_By - Error#" & Err.Number & vbCrLf & _
	Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 

Below is the change to our Macro1() highlighted in red.  Pivot_Template has also been removed because this example has no “State” column.

If .pOK Then                                 'OK button used to exit
           
    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) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By("Fields", "*")
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"
   
    If NameExists("Data") Then
        If Range("Data").Rows.Count > 1 Then
            Add_XLFormula "Data", "Fields"
            Freeze_Pane "Data", "Fields"
            Sort_Data "Data", "Fields"
            Format_Results "Data", "Fields"
        End If
    End If
         
End If
 


February 9, 2010  9:23 PM

Adding Excel Formulas – Part II

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post showed the Fields Table column holding Excel Formulas to embed into your database result sets.  We also talked about how to enter formulas that reference database fields.  This is truly powerful stuff that turns basic data extracts into analytical information.  So let’s start adding the code that makes this all happen.

NOTE: If you added Build_SQL_Select_Fields prior to February 08, 2010, you’ll need to change that version to what is now posted in: Adding Tables and Columns to the Fields Table originally posted on 01/09/2010.  It contained an error.  Sorry.

Today’s code includes two functions: Add_XLFormula and Parse_XLFormula.  The change to Macro1() is shown at bottom.  The line to call Add_XLFormula  needs to be before sorting and formatting because the results can be treated just like fields retrieved from the database.  The in code documentation explains how to use the functions well enough – as it should.

 

Function Add_XLFormula(sDataRange As String, sFieldRange As String) As Boolean
      
'   Add_XLFormula: Add XL formula fields to results ONLY after data is loaded
'   Parameters:    sDataRange  = Name of range containing data
'                  sFieldRange = Name of range containing field definitions
'   Example:       bResult = Add_Defaults("Data", "Fields_Data")
'     Date   Init Modification
'   01/12/06 CWH  Initial Programming
   
    Add_XLFormula = Failure       'Assume the Worst
    On Error GoTo ErrHandler
'   Dim Statements
    Dim lCol As Long              'Column in Data Range receiving formula
    Dim lRow As Long              'Current Row in Fields Table
    Dim lRows As Long             'Number of Rows in Fields Table
    Dim Formula As String         'Formula string
    Dim bIsArray As Boolean       'Is this an Array formula?
   
    Dim lColXLF As Long
        lColXLF = FieldColumn("XL Func.", sFieldRange)
   
    With Range(sFieldRange)
        For lRow = 2 To .Rows.Count
            sFormula = Trim(.Cells(lRow, lColXLF))
            If sFormula > "" Then
                'Check first if this is an Array Formula
                 bIsArray = Left(sFormula, 3) = """{=" Or _
                            Left(sFormula, 2) = "{="
                 If bIsArray Then
                     sFormula = Replace(sFormula, "{", "", 1, 1)
                     sFormula = Left(sFormula, Len(sFormula) - 1)
                 End If
                 
                 sFormula = Parse_XLFormula(lRow, sFormula, sFieldRange)
                 With Range(sDataRange)
                     lCol = lRow - 1
                     If Not bIsArray Then
                         Range(.Cells(2, lCol), _
                               .Cells(.Rows.Count, lCol)).FormulaR1C1 = _
                               sFormula
                     Else
                         .Cells(2, lCol).FormulaArray = sFormula
                         Range(.Cells(2, lCol), _
                               .Cells(.Rows.Count, lCol)).FillDown
                     End If
                 End With
            End If
        Next lRow
    End With
           
    Add_XLFormula = Success
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Add_XLFormula - Error#" & Err.Number & vbCrLf & Err.Description & vbCr _
            & sFormula, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
 
End Function
 
Function Parse_XLFormula(lFld As Long, sFormula As String, _
                         sFieldRange As String) As String
   
'   Parse_XLFormula:Create an XL formula using defintion in Fields Table
'   Parameters:     sFormula    A string containing a coded formula
'                   sFieldRange Name of Fields Table range
'   Example:        range("A4").FormulaR1C1 = _
'                       Parse_XLFormula(2, "=RC{MILES}/RC{GALLONS}", "Fields")
'   Notes:          sFormula can contain references to other Fields/Columns
'                   in the data range by placing {} around the Field/Column
'                   name.  This routine will replace these references with
'                   XL's R1C1 notation.  Examples:
'         Input     "=RC{MILES}/RC{GALLONS}"  Same as "={MILES}/{GALLONS}"
'         Result    "=R1C[3]/R1C[2]"          (Representative result)
'         Input     "=Sum(C{MILES})"          "C" w/no "R" gets entire column
'         Result    "=Sum(C[3])"              (Representative result)
'                   You may use the Field Name or the Heading.  I recommend
'                   using the Field Name because if you change the Heading
'                   the formula will break and you must change it as well
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   02/05/10 CWH  Allowed Field or Heading to be used in Formula string
   
    On Error GoTo ErrHandler
    Parse_XLFormula = ""
   
    Dim i As Integer
    Dim lRow As Long
    Dim lRows As Long
    Dim lBeg As Long    'Start of Field Reference
    Dim lEnd As Long    'End of Field Reference
    Dim s As String     'Field Reference string
    Dim sRC As String   'Prefix for Cell Reference
   
    Dim lColFld As Long
        lColFld = FieldColumn("Field", sFieldRange)
    Dim lColHdg As Long
        lColHdg = FieldColumn("Heading", sFieldRange)
       
   'If user put formula in double quotes, remove them
    If Left(sFormula, 1) = """" Then 
        sFormula = Right(sFormula, Len(sFormula) - 1)
        If Right(sFormula, 1) = """" Then _
            sFormula = Left(sFormula, Len(sFormula) - 1)
    End If
    i = 0
       
    With Range(sFieldRange)
        lRows = .Rows.Count
        Do
           'Search for left curly bracket - designating start of a field
            lBeg = InStr(1, sFormula, "{")
            If lBeg > 0 Then
                lEnd = InStr(1, sFormula, "}")
               's equals name of a field, or heading in the Fields Table
                s = Mid(sFormula, lBeg + 1, lEnd - 1 - lBeg)
               'Determine if an RC reference was included
                sRC = UCase(Mid(sFormula, lBeg - 1, 1))
                If sRC <> "R" And sRC <> "C" Then
                    sRC = "RC"  'User didn't specify. Add RC (single cell)
                Else
                    sRC = ""    'User specified so don't add anything
                End If
               'Search Fields Table to determine field # referenced
                For lRow = 2 To lRows
                    If Trim(.Cells(lRow, lColFld)) = s Or _
                       Trim(.Cells(lRow, lColHdg)) = s Then
                        sFormula = Left(sFormula, lBeg - 1) & _
                                   sRC & "[" & lRow - lFld & "]" & _
                                   Right(sFormula, Len(sFormula) - lEnd)
                        Exit For
                    End If
                Next lRow
            End If
            i = i + 1
        Loop Until lBeg = 0 Or i = 20 'Limit to 20 references
    End With
               
    Parse_XLFormula = sFormula
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Parse_XLFormula - 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
        Add_XLFormula "Data", "Fields"
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Format_Results "Data", "Fields"
        Pivot_Template
    End If
End If

 


February 6, 2010  5:37 PM

Adding Excel Formulas

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

It’s great that we can download and display data in Excel where users can sort it, filter it, print it… whatever they want to it.  It’s great that we can summarize that data in interactive pivot tables.  It’s great that we can display those pivot tables in interactive charts.  Those of you that have been following along now have a spreadsheet you can use as a template to do all those things.  But there’s one thing that really makes these techniques a true replacement for traditional paper based, batch run reports – calculations.

Traditional programmers are accustomed to adding complex calculations to the reports they provide end users.  End users often want reports that calculate the age of an invoice, or the percent of sales overall or within a geographic region.  Without complex calculations, these techniques couldn’t replace traditional methods.  Well – get ready to leave traditional methods behind.  Stop killing trees and start replacing all of those paper based reports with Excel because we are adding all of Excel’s rich library of functions directly into our results simply by adding the formula to our Fields Table.  Below is our new Fields Table, complete with “XL Func.” column.

Fields Table with Excel Functions (click to enlarge)

Fields Table with Excel Functions (click to enlarge)

Examine the examples here.  There are three major categories of Excel functions: cell to cell; entire column based; and array formulas. 

Entering Cell to Cell formulas:
Cell to cell is by far the most common formula type.  You’ve probably used them frequently already.  In this example we are calculating the age of the orders by subtracting the order date from today’s date.

'=Int(Now())-int({`Order Date`})
- or its R1C1 equivalent
'=Int(Now()-int(RC[-1])

Instructions:

  • Reserve space in your result set by:
    • Entering *None in the Table column or leave it empty
    • Leaving the Alias column empty
    • Entering “” (two double quotes) in the Field column
    • Entering a suitable column heading in the Heading column 
  • Enter formulas in this table with a leading single quote ‘ or surround them with double quotes “.  This prevents Excel from trying to calculate a result in this table.  We want Excel to calculate results only in our result set.
  • To use result fields in your formulas, type the Field name or its Heading and surround it with curly brackets {}.  Field names don’t change (normally).  Column Headings might.  For that reason I recommend using Field names when possible so if the end user wants a column heading changed from ‘Ship State/Province’ to ‘State’, you don’t have to change the formula as well.  Field names don’t exist for calculated fields so you have to use Heading references for them.   
  • If you don’t use result field references (which I strongly urge), formulas MUST be entered in the R1C1 notation and should use relative  positions.  Relative positions are prefered so formulas don’t have to be changed if fields are added to the result.
    • This is a relative position in R1C1 notation designating one column to the left of the cell receiving the formula: RC[-1]. 
    • This is a fixed position in R1C1 notation designating the third column: RC3.  

Entering Column based formulas:
Column based formula types aren’t so common but still have their uses.  In this example we are summing the total quantity and placing the result in each row.  This is a bit inefficient for the computer, but very efficient for you.  For reports that generate small result sets, this method will probably make more sense than coding an alternative.  To designate an entire column, place a “C” in front of the field reference. 

'=Sum(C{Quantity})
- or its R1C1 equivalent
'=Sum(C[-1])

Entering Array formulas:
Since the introduction of the SumIF command, I haven’t had much use for Array Formulas.  But for older versions of Excel, Array Formulas are the only equivalent.  In this example, we are getting the total for the row’s State.

'{=SUM(
  IF(C{State}={State},
     C{Quantity},0)
  )}
- or its R1C1 equivalent
'{=SUM(
  IF(C[-5]=RC[-5],
     C[-3],0)
  )}

Special Instructions:

  • Surround Array Formulas with curly brackets {}.  These MUST be in the first and last positions of the formula (inside any quotes). 
  • You do not need to format the formulas with line breaks like I do (Alt-Enter).  It’s just my personal preference.

Next Post
I spent a good deal of blog space to explaining how to enter formulas.  Next post will be the coding.  See you then.


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


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: