Beyond Excel: VBA and Database Manipulation


March 17, 2010  8:19 PM

Updating Databases from Excel – The Basics



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba

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


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: