Beyond Excel: VBA and Database Manipulation

Sep 10 2009   6:59AM GMT

Forward

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Tags:
excel

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

 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: