Integrating Database to Excel Using VBA and Custom Excel Menu

ALL INDUSTRIES

SOFTWARE SOLUTION DEVELOPER: Francis Lim

Project Categories:  Advanced Excel & VBA Programming, Application Solution Architecture, Database Architecture, Data Checking Variation, Data-Mining, Financial Data Analysis, SQL.

It is fairly tedious to query repetitive and extract data from database to be reproduced in multiple worksheets manually. This can be quite frustration whereby you’ve many dimension sets of data members to query the database, export data and import into Excel to perform multidimensional analysis of business data and calculation, data checking variation, trend analysis, and sophisticated data modeling for your business and financial management reporting. Hence, it also increases the multiple copies of workbooks loaded through data extraction and variation of versions.

Microsoft Excel is the standard desktop spreadsheet applications with a very high acceptance rating and used by millions of users. Well, good news is that Excel can be programmed to connect to your source database. Excel is a very powerful spreadsheet application, and you can do incredible things with it. With a good foresight, you can build an efficient, standardize, reuse and update to multiple Excel worksheets simultaneously from the retrieved Source Database like Microsoft Access, Microsoft SQL Server, MySQL or Oracle.

With a degree of control you can build connection between the source database to Excel using the built-in macro language: Visual Basic for Applications (VBA).

What is EXCEL VBA?

VBA (Visual Basic for Applications) is the name of the programming language which is used to control Microsoft Office products or any other program that is VBA compatible. VBA programs are also referred as Macros. Apart from Microsoft Excel, major Microsoft Office products like Word, PowerPoint, Access, and Outlook come with standard VBA and can be programmed.

VBA in conjunction with Microsoft Excel, a powerful analysis tools that can construct automated tools with hundreds or thousands of important tasks at once, tasks like data processing, model analysis, automating Excel's features and chores, chart creation, financial data checking, report generation, dashboard creation, Structured Query Language (SQL) Scripts Generator, controlling other programs, communicating with databases, the list is endless. Beyond just simple programs, Microsoft Excel and VBA can also be used to construct complex dashboard driven analysis and data processing tools that competing any program on the market today.

Custom Menu with Excel VBA

It is possible to customize Excel in many different ways. However, to make your Excel spreadsheets have that professional look and feel, one can add their own custom menus to an existing Excel toolbar. This is best done via VBA. If you have a version of Excel 2010 or greater then you can now add new tabs to the Excel ribbon. When you create a tab, you can place items on it, including buttons for your user forms. You can also add buttons to existing tabs. For example, you could add a new button to the Home tab that displays your user form when the button is clicked.

Example of an External Database: Oracle Data Source

Example of Microsoft Excel VBA Connect Dialog login into External Oracle Database

Example of Microsoft Excel VBA programmed form with Interface to Oracle Data Source for building SQL Query Script to run Financial Management Reports

Example of a Custom Menu Microsoft Excel Financial Management Reports connect to Oracle Data Source