This file represents a simple example how to read data from external files of the types *.dbf
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:
, Security level
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:
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 button 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 left mouse button 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.
In Visual Basic Editor. Visual Basic Editor is opened from the Tools/Macro/Editor
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 left mouse button 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 the button or those that control run of the started process.
Description of scripts in the functions of the Module1
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 variables sPath 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.
- Reads the data from the database and writes them to the "Sheet1"
of this application. It consists from two loops 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 keyword.
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
The script can be executed step-by-step by the "Step Into
The execution of the script is stopped by the "Reset
The breakpoint of the script execution is created on the point where the cursor is placed after pressing the "Toggle Breakpoint
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.