Beyond Excel: VBA and Database Manipulation


October 6, 2009  7:04 PM

Code for “easy” Button

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Put an “easy” button in your spreadsheets to facilitate launching any macro.

Welcome new readers.  We’ve been talking about how to bring data into XL.  This post introduces the first of many standard routines I use to make assembling real data analysis tools in minutes.  It’s a little function to enhance the user experience by making buttons that look similar to Staple’s “easy” button.  Who couldn’t use an “easy” button?  

easy button

easy button

Option Explicit
Global Const Success = False
Global Const Failure = True 
 
Function Create_Easy_Button(sText As String, sMacro As String, _
                            x As Long, y As Long) As Boolean
'   Create_Easy_Button:     Create a clickable button resembling
'                           Staples' "easy" button
'   Parameters:     sText:  A short word for the button like
'                           "easy", "Load", "Post", "Save", or "New"
'                   sMacro: The macro name to attach to this button
'                   x:      Button's horizontal position
'                   y:      Button's vertical position
'   Example:        Create_Easy_Button "easy", "Macro1", 10, 8
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming Copyright Craig Hatmaker 10/07/2009
    On Error GoTo ErrHandler            '
    Create_Easy_Button = Failure        'Assume the Worst
    If ShapeExists(sText & "_Button_Base") Then _
        ActiveSheet.Shapes(sText & "_Button_Base").Delete
    If ShapeExists(sText & "_Button_Text") Then _
        ActiveSheet.Shapes(sText & "_Button_Text").Delete
    If x = 0 Then x = 10
    If y = 0 Then y = 8
    With ActiveSheet.Shapes.AddShape(msoShapeOval, x, y, 35, 35)
        .Name = sText & "_Button_Base"
        .Fill.ForeColor.RGB = RGB(200, 0, 0)        'Dark Red center
        .Placement = xlFreeFloating
        .OnAction = sMacro
        With .Line 'White border
            .ForeColor.RGB = RGB(255, 255, 255)
            .Weight = 3
        End With
        With .Shadow
            .Visible = True
            .OffsetX = 2
            .OffsetY = 2
            .Transparency = 0.5
            .ForeColor.RGB = RGB(10, 10, 10)
        End With
        With .ThreeD
            .BevelTopType = 3
            .BevelTopDepth = 20  'Rounded top
            .BevelTopInset = 19  'Rounded Top
            .ContourWidth = 0    'No line around the base
            .Depth = 2
            .ExtrusionColorType = 1
            .FieldOfView = 45
            .LightAngle = 300    'Light from above and to the left
            .Perspective = 0
            .PresetLighting = 15
            .PresetMaterial = 6  'Plastic
        End With
    End With
    With ActiveSheet.Shapes.AddTextbox( _
         msoTextOrientationHorizontal, x - 1, y - 2, 35, 35)
        .Name = sText & "_Button_Text"
        With .TextFrame
            .MarginBottom = 0
            .MarginLeft = 0
            .MarginRight = 0
            .MarginTop = 0
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
            .Characters.Text = sText
            With .Characters.Font
                .Bold = True
                .Size = 16
                .Name = "Calibri"
                .Color = RGB(255, 255, 255)
                .Shadow = True
            End With
        End With
        .Line.Visible = False
        .Fill.Visible = False
        .TextEffect.PresetTextEffect = 2
        .Placement = xlFreeFloating
        .OnAction = sMacro
    End With
   
    Create_Easy_Button = Success         'Successful finish
ErrHandler:
    On Error GoTo 0   
    If Err.Number <> 0 Then MsgBox _
        "Create_Easy_Button - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Function
Function ShapeExists(sName As String)
'   ShapeExists:    See if a Shape Exists
'   Parameters:     sName - Shape Name to be checked
'   Example:        If not ShapeExists("EasyButton") then _
'                       Create_Easy_Button "easy", "Show_Prompt", 10, 8
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    ShapeExists = False     'Assume not found
   
    Dim objName As Object
   
    For Each objName In ActiveSheet.Shapes
        If objName.Name = sName Then
            ShapeExists = True
            Exit For
        End If
    Next
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "ShapeExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

October 6, 2009  7:03 PM

Improving the User Interface – Adding a Button

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Adding Buttons

Adding Buttons

So far we’ve extracted data from a database, populated cells in XL, recorded a macro and written a bit of VBA.  Today we address the user interface (UI) by adding something simple like a button.  The button will be used to start our macro.

Until now, we started macros by pressing Alt-F8 to bring up the Macro dialog, select the macro we wanted to run, and then, run it.  Now that’s not too difficult, but it’s more steps than it needs to be.  We can reduce all that to one simple click of the mouse by placing a button on the spreadsheet and associating it with your macro.

To add a button, navigate XL’s menu to Insert > Shapes (or prior to Office 2007 Insert > Picture >AutoShapes) and select any shape you like.  I selected a basic rectangle.  Your cursor will change to a crosshair.  Move the crosshair to the upper left corner of where you’d like your button, then click and drag down and to the right to extend the shape to a desired size.  Format it as you like.  Next, right click on the shape and select the Assign Macro option.  Select Macro 1 and click OK.  Now try out your new button.  If everything went well, you will see the parameter prompt for the state, and after entering that parameter, data will fill your spreadsheet.

Congratulations!  You have literally made pulling data from a database into XL as simple as one click of a mouse.

BONUS!  If the office supply store “Staples” is in your area, then you’ll recognize their “easy” button.   You can add your own “easy” button from code and change the wording to anything you want.  I typically add three buttons to worksheets that update data in the database: “new”, “load”, “post”.  But for spreadsheets that only pull data, I use “easy”.  See “Code for the “easy” Button” next.


October 1, 2009  10:02 AM

Intro to XL Objects in VBA

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In our last post we looked at running a query that accepts user input to determine the result set loaded into XL.  I want to emphasize that although we used an Access database as our source, these same tools and processes work with Oracle, SQL Server, AS/400/iSeries/IBM i/Power i/System i (however you prefer to call it) databases and many others.  If you need help connecting to your database, post a comment with what you’re trying to connect to and I’ll try to address it or I’ll point you to good resources.

There are lots of opportunities for improvement on that last post including adding summaries, charts, filters, sorts, etc.  The first improvement I’d like to make is to include the clearing of any previous query into Macro 1 and thereby, eliminate one more step.  Using the spreadsheet from before (if you didn’t save it, just recreate it using the prior post’s instructions.  It only takes a few minutes and it’s good practice), edit Macro 1 (use Alt-F11 to bring up the VBE).  Add this line just before the first line of code (but after the Sub Macro1() line):

Cells.ClearContents

This line is equivalent to right clicking the “Select All” box and selecting Clear Contents (If you turned on the Macro Recorder and performed those actions you’d get this line of code in a new subroutine).   If you’re new to VBA, this will probably look strange to you so let me break it down. 

Objects:
VBA manipulates XL’s objects .  One of the most common objects in an XL spreadsheet is a Range.  A Range object can contain one or more cells.  Cells is a special Range object that contains ALL cells in a spreadsheet.  Cells is the XL object in that line of code. 

Methods:
VBA can invoke XL’s object methodsMethods are actions that are predefined and exposed (made available to us) for a given object.  To invoke any of an object’s methods, use the syntax: <Object>.<Method>.  ClearContents is the Cells’ method in that line of code. 

Properties:
VBA can read XL’s object properties.  To read an object’s property into a variable use the syntax: <Variable> = <Object>.<Property>.  Example:  One of the Cells object’s properties is AllowEdit. AllowEdit indicates if the range can be edited on a protected worksheet.  So to read the Cells’ AllowEdit property into variable A, write:

A = Cells.Address

VBA can set some of XL’s object properties.  To set an object’s property use the syntax: <Object>.<Property> = <Value>.  Example: One of the Cells object’s properties is Value. Value is the contents of Cells.  So to set the Cells’ Value property to “A”, write:

Cells.Value = “A”

Include the Cells.ClearContents line in your Macro 1 and then re-run it using Alt-F8 as explained in the prior post and observer the results.  Congratulations, you’ve just reduced the amount of steps needed to retrieve different results.


September 24, 2009  4:18 PM

Automating Queries

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
We’ve been looking at using MS Query from XL to get at data stored on mainframes, servers, or you PC.  Hopefully you have experimented with MS Query.  MS Query represents what I refer to as a “manual method” because it involves no coding.  So let’s look at how we can automate what we’ve learned so far.

XL makes this insanely easy through use of the Macro Recorder. If you don’t know how to do this, rather than me “reinvent the wheel” so-to-speak, and explain it myself, let’s leverage available resources on the net.   At the bottom of this post are references for this.  Find the “Recording Macros” reference that is appropriate for your version of XL, view the tutorial and come back here.

XL Select All Box

XL Select All Box

Welcome back!
Now that you know how to record a macro, start the macro recorder.  Now follow the steps in my prior post to start MS Query and collect some data.  Once the data is placed into your spreadsheet, stop the macro recorder.  Congratulations, you’ve just “written” a macro.  Macros can be easily rerun.  So let’s prove it to ourselves by clicking the “Select All” box (see the XL Select All picture) and then hit delete to wipe out the data we just downloaded.  A message box will warn that you are about to delete a range that contains a query.   Click “Yes”.   Now use Alt-F8 to bring up the Macro dialog.  Click on “Macro 1” then click “Run”.  The data returns.

We have reduced 9 simple steps (see previous post) to just 1.  While that’s efficient, it’s not useful because all it does is bring back the same data.  To make it useful, we need it to respond to our changing needs.  In our example we selected California’s records. What if we want to use the same query for a different state?  Let’s fix our macro to do just that.

Visual Basic Editor

Visual Basic Editor

Hit F11 to see the code.   Your screen should look something like the picture labeled Visual Basic Editor (VBE).  If some of the windows are not displayed, use the menu’s View > Project Explorer (or whichever window is missing) to bring it up.  Make sure you see “Module 1” in the Project Explorer (you may have to expand “Modules” to see it).  Click “Module 1” to select it.  If the code isn’t displayed use the menu’s View > Code to force it to the code window.  There are two key pieces of code I want you to study.

The Connection String:
Connection strings in MS Query begin with “ODBC;”.  In our example the full string on my PC is:

“ODBC;DSN=MS Access Database;DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;DefaultDir=C:\Users\chatmaker\Documents;DriverId=2″

Connection strings are critical to fully automating data with XL.  They are often the most frustrating part.  But they are also easily revealed by MS Query.  We’ll discuss connection strings in later posts.

The SQL Statement:
Find your SQL statement in the code.  Your SQL statement should look similar to:

“SELECT Orders.`Order ID`, Orders.`Customer ID`, Orders.`Order Date`, Customers.`First Name`” & Chr(13) & “” & Chr(10) & “FROM `C:\Users\chatmaker\Documents\Northwind 2007.accdb`.Customers Customers, `C:\Users\chatmaker\Documents”, _
“\Northwind 2007.accdb`.Orders Orders” & Chr(13) & “” & Chr(10) & “WHERE Orders.`Customer ID` = Customers.ID AND ((Customers.`State/Province`=’CA’))”

Change the state at the end of your SQL statement from “’CA’” to “?” so it looks like:

((Customers.`State/Province`=?))

The “?” tells Query to ask for this value.  Hit Alt-F11 again to return to XL (or click the little XL icon in the upper left corner of the VBE tool bar).  Click the Select All box and delete everything again.  Hit Alt-F8 to bring up the Macro dialog and run Macro 1 just as you did before.  This time the “Enter Parameter Value” dialog box appears.  Enter “NY” (not the quotes) and click OK (or press Enter).  New data appears.  Now we’re getting somewhere!

References:
Online VBA Tutorials: www.totorialized.com/tutorials/MS-Excel
Recording Macros Prior to Excel 2007: http://www.youtube.com/watch?v=k_fWcffRAG4
Recording Macros Excel 2007: http://www.youtube.com/watch?v=KKJj78LoFaA
Creating a Query Using Query Wizard Prior to Excel 2007: http://www.youtube.com/watch?v=vkFQM-Fb3-Q


September 22, 2009  9:06 AM

Creating a Query

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

So far we’ve discussed MS Query and its uses.  Today we’re going to see just how easy it is to use in 9 simple steps.

Step 1 – Find Data
You probably have a copy of a sample MS Access database called “Northwind” on your PC.  We will use it for this exercise.  The most fool proof method for finding that database is to use “search” to find “*.mdb” (Prior to Office 2007) or “*.accdb” (Office 2007 version) starting in C:\ and including all subdirectories.  If “Search” doesn’t find it, download Northwind from Microsoft (See references below).

Step 2 – Launch MS Query
From XL’s Office 2007 menu: Click Data > From Other Sources > From Microsoft Query
Prior to Office 2007: Click Data > Get External Data (or Import External Data) > New Database Query

Step 3 – Select Database
Make sure “Use the Query Wizard to create/edit queries” is unchecked.  Choose “MS Access Database” as your Data Source and click “OK”.  Select the Northwind database from the location you identified in step 1.

Step 4 – Add Tables
Double click “Orders” and “Customers” to add the tables to MS Query then close the “Add Tables” dialog.  To remove a table from MS Query click on a table and hit delete.  To add tables click the “Add Tables” icon (grid with a plus sign) or from the menu select Tables > Add Tables.

MSQuery

MSQuery

Step 5 – Create Joins
In some versions of MS Query, obvious joins are created for you.  In our example we need the “ID” field/column from the “Customers” table to be linked to the “Customer ID” field/column in the “Orders” table.  To do this, simply click and drag either field to its match in the other table.

Step 6 – Select Fields/Columns
From the “Orders” table double click “Order ID”, “Customer ID”, and “Order Date”.  From the “Customers” table double click “First Name”.  (You don’t need every field in the picture).

Step 7 – Filter Results
From the menu select Criteria > Add Criteria
Select Field: “Customers.State/Province”; Operator: “equals”; and type “CA” into Value:
Click Add and Close.

Step 8 – View SQL
This isn’t necessary at this point, but I want you to see the SQL MS Query created for you.  If you’re new to SQL, this is a great way to overcome any anxiety you might have about learning SQL as you can always use MS Query to build simple SQL statements for you (we will be using them exclusively later on).  If you’re an SQL veteran you probably find the code verbose and ugly.  You can change the SQL here if you like.

Step 9 – Return Results to XL
Click the “Return to XL” icon (open door with an arrow) or from the menu select: File > Return data to Microsoft Excel.

Congratulations!  You’ve just put your data into the most widely accepted, most widely loved, and one of the most powerful database analytical tools available.

References: Northwind Sample Database:
Location: http://support.microsoft.com/kb/824265
Download: http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN
Useful Information: http://www.wilsonmar.com/northwind.htm


September 18, 2009  3:53 PM

MS Query – Example Use

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

So far I’ve talked about how to use MS Query.  It’s easy.  It’s useful.  It’s free.  It’s included with every version of XL.  It works with just about any database provided you have the (usually free) ODBC driver.  Though we won’t be using MS Query in any of the spreadsheets we could deliver to end users – it is still very useful in the hands of the right end users.  Here’s an example.

Last week, our B2B (Business to Business) specialist was alerted to a problem.  A payment from an important customer was delayed because an EDI 214 (a B2B document that conveys shipment status) failed to reach them.  Our B2B team is very good at what they do so this was a big surprise, and though this was the first occurrence, our B2B specialist wasn’t about to let this incident go without a thorough investigation.  Her first concern was damage control.  She needed to know if there were other transactions like this.  She’s not a developer, but she’s not afraid of technology.  So using MS Query she connected to our IBM Server’s DB2 database, joined a few files, and quickly determined there was indeed only one transaction that failed to generate the proper 214.

She’s happy because she’s certain the problem is isolated.  I’m happy because instead of two people, our B2B Specialist and a developer getting tied up, she was able to handle this quickly on her own using a simple, free tool included in her copy of XL.

Now some DBA’s might be shocked at the idea of letting anyone have SQL access to the database.  After all, they could run a query that would bring the machine to its knees, right?  Possibly.  But in the last 10 years I haven’t seen that happen.  Maybe we’ve just been lucky.  Maybe our machines are oversized.  Maybe our operating systems are smart enough to isolate CPU hogs.  On rare occasions, I’ve seen long running, full table scan queries that frustrated the user who submitted them, but I’ve not seen those queries throttle the CPU and choke out other users.  If that ever does happen, we know we can quickly kill the rogue session and restore CPU cycles to the masses.  In the mean time, we will enjoy productivity improvements and end user job satisfaction increases – at no cost to the company at all.

In my opinion, the risk/benefit ratio for this approach is solidly in the favorable column.


September 17, 2009  6:44 AM

Data Sources

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
In my last post, I shared how you can explore just about any database using MS Query, a useful, friendly, and free tool included with every version of XL.  To use it with your database, you must point it to the appropriate data source. 
Choose Data Source

Choose Data Source

When you start MS Query the first thing it asks for is the “Data Source” name.  A “Data Source” name is required.  At a minimum, a “Data Source” name points MS Query or ODBC to a driver that understands the application’s standards (such as Oracle, DB2, Access, Excel, or MS SQL Server) that your data conforms to.  

If you don’t see your required database in the list, you can create it by selecting <New Data Source> and clicking OK.  That brings up the “Create New Data Source” dialog. 

Create Data Source

Create Data Source

Step 2 is perhaps the most daunting.  Sometimes there are more than one driver listed that support your database.  In that case, it usually doesn’t much matter which one you choose.  Sometimes there are no drivers that support your database.  In that case, you need to load that database’s ODBC driver on your PC. 

If you don’t know where to find your ODBC driver, first contact your DBA or Systems Administrator.  If they don’t have it, search the database provider’s website under product support for driver downloads.  If it’s not there, contact the database provider’s sales or support team.  If all else fails, Google your database name with “ODBC driver” to see if someone else provides it.  I have found a few old, non-relational databases with third party ODBC drivers for sale; but, the dominant database providers don’t charge.  Microsoft includes theirs and several once prominent PC databases with their operating systems.  Oracle’s is freely available from their website.  IBM’s comes with their PC client or server CDs.  Several powerful free databases such as mySQL give the driver away.


September 10, 2009  7:28 AM

Manually Linking to Data – MS Query

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Before tackling automated methods for incorporating data into XL, we should look at a surprisingly useful and free component included with every version of XL, MS Query.  MS Query provides a graphical interface into databases.  You can use it to explore database tables, examine columns and contents, join tables, filter tables, sort tables, reveal the SQL statement built behind the scenes, and load results into XL.

MSQueryUnfortunately, MS Query is not always installed (Use XL’s Help and search for Install Query, or see Installing Microsoft Query) so you may have to find your XL installation disks for this one.  When we get to delivering applications to end users we won’t use MS Query, we will only use SQL.  So it doesn’t matter if MS Query is installed on their machines.  Having it on your machine helps you determine what information you need in your report or application and what the SQL needs to be.  If you’re new to SQL, you will find it immensely useful in learning how to join two or more tables and how to deal with certain field types such as dates.

MS Query is found (assuming it has been installed):

Office 2000: Under the “Data” menu, “Get External Data” option, select “New Database Query” sub-menu option.

Office 2002: Under the “Data” menu, “Import External Data” option, select “New Database Query” sub-menu option.

Office 2007: On the “Data” tab, in the “Get External Data” group, click “From Other Sources” icon, and select the “From Microsoft Query” option.

NOTE: If you’re using short menus, you may need to click the menu’s down arrows to see these options.

Before MS Query starts it asks you to select a data source and that will be the topic of our next post.

Preview:
[kml_flashembed movie="http://www.youtube.com/v/I1qvqMiGyhc" width="480" height="292" wmode="transparent" /]
Click here for northwind database download: Northwind 2003.zip

Next Post: Data Sources


September 10, 2009  6:59 AM

Forward

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Everyone knows Microsoft Excel is a powerful database manipulation engine, but data lives in databases and only a few Excel experts know how to exploit this natural synergy.

If you are one of tens of thousands of business developers asked each day to:

  • Produce reports from systems such as your ERP, EAM, HRIS, CPFR, CRM, TMS, etc.
  • Write small applications such as Time and Attendance Entry, Help Desk Requests and Time Tracking, PC Inventories, etc.
  • Create data extracts and transformations for system interfaces -

-you can exploit Excel to deliver paperless, interactive, graphical reports in minutes not hours or days and write small applications in hours, not days or weeks.  You will exceed your customers’ expectations.  You will save your customers time.  They will realize benefits sooner.

As developers our value is in the worth of the tools we provide.  When our customers get an idea for a tool to help them in their work, the last thing they want is to wait for it.  They want it delivered now.  They want to be able to use it now.  They want it to be flexible.  They want it to be graphical.  Microsoft Excel is often a great means to meet their ends.

Your customers already have XL so there’s no procurement cycle.  They already know XL so there’s no learning curve.  They already love XL so there’s no adoption hurdle.  All of this is valuable to your customers.  What’s left is for you to provide what only developers can – code.

Coding is essential to this process.  There are no end user based solutions here other than your final product.  Your skills are required.  Your skills need to include VBA (the macro language included free in just about all Microsoft applications).  If you don’t know VBA, don’t worry.  You won’t find it difficult and the examples posted here will make it easy.  If you don’t know SQL, don’t admit it.  It’s easy.  It’s powerful.  It’s standard.  It’s everywhere.  And there are many excellent, free resources on the web.  As we go, I will point out resources I use.  Perhaps others will share their favorites as well.

If you and your customers own Microsoft Excel (and who doesn’t these days) and your database provider included an ODBC driver (the most prevalent in the market do) then you are good to go.  There is absolutely nothing else to buy.

Everyone knows Microsoft Excel is a powerful database manipulation engine, but data lives in databases and only a few Excel experts know how to exploit this natural synergy.  Soon – you will be one of them.

Preview

Function or Subroutine Module/Class Located in Post
Add_XLFormula modGeneral Adding Excel Formulas – Part II
All Code and Properties for … frmSelect_Multiple Searching for Codes
All Code and Properties for … frmSelect Check Entry – Form Select – Theory and Demo
All Code and Properties for… frmPrompt Asking for It
All Code and Properties for… frmDatePicker Looking for a Date?
Build_SQL_Group_By modGeneral Group By and Aggregate SQL Functions
Build_SQL_ID modGeneral It’s Time to Play
Build_SQL_Insert_Fields modSQL Building a Library of Routines for Updating – #3
Build_SQL_Insert_Values modSQL Building a Library of Routines for Updating – #3
Build_SQL_Select_Fields modGeneral Adding Tables and Columns to the Fields Table
Build_SQL_Update_Values modSQL Building a Library of Routines for Updating – #2
Build_SQL_UpdDlt_Where_Clause modSQL Building a Library of Routines for Updating – #2
Cell_Checked modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Error modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Lock modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Unchecked modTableUpdate Check Entry – Marking Cells as having passed or failed
Cell_Unlock modTableUpdate Check Entry – Marking Cells as having passed or failed
Chars_Last_Position modGeneral Building a Library of Routines for Updating – #4
ChartExists modGeneral Building a Library of Routines – ?Exists
Check_Entry Sheet1 Check Entry – The Controlling Routine
Check_For_Normal_Entry_Errors modTableUpdate Check Entry – Common Validations
Create_Easy_Button modGeneral Code for “easy” Button
Create_Named_Range modGeneral Creating the Fields Table
Cust_Edit Sheet1 Check Entry – Dealing with Field Level Exceptions
Date2Julian modGeneral Building a Library of Routines for Updating – #1
DB_Lookup modTableUpdate Check Entry – DB_Lookup
Field Definitions Table for Updates Additions to the Fields Table for Update Spreadsheets
FieldColumn modGeneral Adding Tables and Columns to the Fields Table
Fields_Field_Column modTableLoad Check Entry – Marking Cells as having passed or failed
Fix_Name modGeneral Creating the Fields Table
Format_Results modGeneral Setting Column Widths or Hiding Them
Freeze_Pane modGeneral Preserving Column Headers and Key Values
Get_Data Sheet1 Drill Down Part 2
Get_Range_Dimensions modTableLoad Check Entry – The Controlling Routine
Initialize_Globals modTableLoad Check Entry – Initializing Globals
Julian2Date modGeneral Building a Library of Routines for Updating – #1
NameExists modGeneral Building a Library of Routines – ?Exists
Parse_XLFormula modGeneral Adding Excel Formulas – Part II
Pivot_Template modGeneral Wrapping things up
PivotTableExists modGeneral Building a Library of Routines – ?Exists
Post modTableUpdate Building a Library of Routines for Updating – #7
Post_Click Sheet1 Check Entry – Post_Click Event
Prompt Sheet1 Drill Down Part 2
Settings modGeneral Building a Library of Routines – Settings
Setup_Pivot modGeneral Building a Library of Routines – Setup_Pivot
Setup_PivotChart modGeneral Building a Library of Routines – Setup_PivotChart
ShapeExists modGeneral Building a Library of Routines – ?Exists
Sort_Data modGeneral Adding Sorting
SQL_Add_Update_Functions modSQL Building a Library of Routines for Updating – #1
SQLConnection modSQL Building a Library of Routines for Updating – #6
SQLLoad modGeneral Say Goodbye to QueryTables
SQLRead modGeneral Using ADO to Read a Database
Table_Lookup modGeneral Check Entry – Table Lookup
Table_Lookup_by_Name modGeneral Check Entry – Table Lookup
Update_Entries modTableUpdate Building a Library of Routines for Updating – #6
Update_Entry modTableUpdate Building a Library of Routines for Updating – #5
Worksheet_Activate Sheet1 Check Entry – Initializing Globals
Worksheet_BeforeDoubleClick Sheet1 Drill Down Part 2
Worksheet_Change Sheet1 Check Entry – Worksheet_Change – Code
WorkSheetExists modGeneral Building a Library of Routines – ?Exists
XL_Lookup modGeneral Check Entry – XL_Lookup & Dynamic Arguments


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: