Import/Export XL Tables from/to Databases

Learn to import/export XL’s tables from/to databases[i] like MS Access or SQL Server using the PDF link at bottom.A New Paradigm

Traditionally we think of workbooks as a combination of user interface (UI), data and processes (formulas or VBA). This is great for single use applications. But what if our model or application has multiple uses?

Multiple Use Scenario

Let us imagine we developed an installment loan calculator. We use it for a car purchase and save the workbook. Then we want to use it for a home improvement loan. We open the workbook, remove the car data and enter our home improvement data. We also find an error in one of our calculations or macros so we fix it. We must also fix the car purchase workbook too.

The Problem

Traditional XL thinking requires more work to reuse a model. We have to remove old data being careful not to remove formulas. We have to save a new copy of the workbook or lose the previous one. We have to open, correct, and save all previous workbooks to apply fixes or improvements. This is wasteful.

A Better Approach

By separating data from UI and process we can more easily reuse and maintain our models. This is NOT hard to do. I do this by putting user inputs into a table. This table feeds all formulas, pivot tables, charts, etc. I can save the table independent of the workbook. And I can load any saved table to apply a totally different data set to my model. And if my model needs improvements, I apply them to my one and only model.

The Solution: Import/Export Add-in

You might ask how I “save the table independent of the workbook” because XL doesn’t have an Import/Export function. With this add-in, it does. This Import/Export add-in saves (exports) XL tables to an MS Access database (MS Access application not required). It could just as easily save XL tables to SQL Server, Oracle, DB2, XL workbooks or many other databases simply by changing entries in an XL table called Connections.

This function also loads (imports) XL tables from a database.

Result

Easily run models with multiple datasets. Never save workbooks unless adding improvements. Only make improvements in one place.

[i] Database –an organized collection of data (http://en.wikipedia.org/wiki/Database).