Beyond Excel: VBA and Database Manipulation

Dec 31 2009   9:08AM GMT

Creating the Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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

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

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

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

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

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

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

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

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

    Dim lRow As Long

    Dim lRows As Long

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

In the next post we will start using the table.

 Comment on this Post

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

REGISTER or login:

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

Forgot Password

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

Your password has been sent to: