Example of reading data from external databases in MsExcel

This file represents a simple example how to read data from external files of the types *.dbf or *.csv in Excel (the application was created in the version of Microsoft Excel 2000 cz).

- "Data.dbf" or "Data.csv" are the source files for this example

1st Possibilities and limitations:

In general, it is possible to read the data from every database that is supported by Excel for opening. The path to the source file cannot be set as a relative path (the "Settings" sheet, the "B1" cell). External databases (those that are supported) are opened in Excel by the "Open" method. And that's why it is normal that "Sheet name with source data" = file name without the extension (the "Settings" sheet, the "B2" cell). Settings of Excel must be done in the option:

Tools/Macro/Security..., Security level page.

Select "Medium" (After starting the application the dialog box is opened to enable Macros to be run).

or "Low" (Macros are run immediately without questioning). In previous versions of Excel, this option was placed in: Tools/Options...

If the sheet (book) is renamed, then the script (Makro) will fail.

2nd How this application is created

The following process was used:

a) In the Excel environment the following two sheets were used for processing:

"Sheet1" with the button starting reading the data and the area (from the "A2" cell) where the data are supposed to be read.

"Setting" - cells A1 and A2 with the description and cells B1 and B2 whose "value" is used in the script described below

Creating a functional button in Sheet1:

In "Sheet1" press the right mouse button on the blank field of the "Menu" bar and select control toolbox from the offered items.

In the "Control toolbox" box press the "Design mode" button to become active, and then "Command button".

Then mark the area in the sheet by the mouse with the pressed left key into which the button is drawn. The appearance and the descriptive text can be adjusted in the properties by the right mouse button on this object. By mouse double-clicking the object, "Visual Basic Editor" is opened and there the "function is preset" that is called after pressing the key (described below). Close "Visual Basic Editor", close the design mode (deactivate by pressing the button) and save the application.

b) In Visual Basic Editor. Visual Basic Editor is opened from the Tools/Macro/Editor menu (Alt+F11).

Description of Editor and its basic functions:

All sheets of the application are available in the "Project-VBAProject" window (Project Explorer button), then the ThisWorkbook object and "Module1" in the Modules section. By mouse double-clicking the object, the content (scripts) of these objects appears in the edit window. This application has created the function in the ThisWorkbook object of the "Open" event that is called in the moment when the application is opened. (It calls the "Init" function in global "Module1" and the event is operated at the same time if this function fails).

In the Sheet1 object there is a list of functions that are sequentially called if "Button" is pressed in this sheet. The event is again operated if any of the functions fails.

And in the Module1 object there are functions that are referenced after pressing a button or those that control run of the started process.

Description of scripts in the functions of the Module1 module:

Global variables are created in the opening (Public - these are accessible in all functions and objects of this application).

Function "Init" - Creates the references to this application "Sheet1" and "Setting" and assignes the value into the sPath variable from the B1 cell and the value into the sSheet from B2 cell. Further by means of the "Open" method with 1 parameter sPath it opens the source database and it creates the reference to this global object. It create the reference to the sheet of this object in the same way.

Function "Reset" - Releases variables from the memory if the application fails when running.

Function "ReadData" - Reads the data from the database and writes them to the "Sheet1" of this application. It consists from two loops For-Next and Do-Loop-While. For-Next ensures that the data from the source (DB) are read and written from the first to the forth column. Do-Loop-While (in the body of the For-Next) reads and writes into "Sheet1" every value in the row of the column until the row with the "null value" is encountered in the first column of the source database (condition in While).

Function "CloseDb" - Closes the database and releases global variables from the memory.

3rd Help, controlling and debugging scripts:

Help in Visual Basic Editor is opened by the F1 key, it is functional even by context if the cursor is placed on the key word.

Functions can be executed one by one if the cursor is placed in the body of the function and the "Run Sub/User Form" button is activated or by the F5 key.

The script can be executed step-by-step by the "Step Into" key.

The execution of the script is stopped by the "Reset" button.

The breakpoint of the script execution is created on the point where the cursor is placed after pressing the "Toggle Breakpoint" button.

The value of the variable can be watched in the Watch window that is started by the "Watch" button. The required variable whose value can be then watched is moved into it by dragging the mouse during the "step-by-step" execution.

Promotic

Example of reading data from external databases in MsExcel

See also: