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 tab.
Select "Medium" (After starting the application the modal window 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 will be called after pressing the key (described below). Close "Visual Basic Editor", close the design mode (deactivate by clicking 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 (the "
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" button.
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.