SQL*XL's internal commands can be used from within the macro editor of Excel. A step by step approach is used
to show how a set of small macros can be developed. These macros will achieve 2 goals: connecting to the database,
fetching data from the database. The easiest way to get started programming SQL*XL commands with VBA is to record
a macro in Excel and look at the generated code.
To follow this example through start Excel and load SQL*XL.
To start, let's have a look at Excel's Visual Basic editor. Execute the menus Tools|Macros|Visual Basic Editor
(or press Alt-F11). A new window will be launched titled: Microsoft Visual Basic - Module1. This is Excel's native
Visual Basic environment.
Let's have a look at the project explorer. This tree view is usually displayed as a docked window is titled Project
- VBAProject. If you do not see it (usually at the left hand side of the screen) execute menu item View|Project
Explorer. All the root nodes denote physical Excel files (.xls or .xla files). Within each node there is a child
node called Microsoft Excel Objects and within that you'll see a child node for each sheet and a node called ThisWorkbook.
Double click on the ThisWorkbook node and a blank screen will open. Code written in this screen will be saved in
the spreadsheet file (.xls) described by the root node. If you started with the default blank spreadsheet it will
To use SQL*XL commands Visual Basic needs to know where SQL*XL is. Execute the menu Tools|References and use the
Browse button to select the file sqlxl.xla. Close all dialog boxes when done. Whenever you type SQLXL. a listbox
appears after the dot was typed with all the functions you can use within SQLXL.
In the ThisWorkbook code window a new procedure will be coded to connect to the database. Type the code in listing
1 into the window:
Private Sub Connect()
If SQLXL.Database.Connected = True Then
SQLXL.Database.Connect "scott", "tiger", "beq-local",litOO4O
If SQLXL.Database.Connected = True Then
MsgBox "Connection Failed"
Listing 1: Connect to the database using SQL*XL
In short the code in listing 1 will check whether SQLXL was already connected to the database. If it is connected
the function DisConnect is used to disconnect. In the second if block the connection to the database is made using
the command Connect. Replace the username "scott", password "tiger" and database "beq-local"
strings with the one's you want to use. When connection is successful the code will display a messagebox with the
text "Connected" and if it fails it will display the text "Connection Failed".
Next, a second subroutine will be developed to get the contents of the emp table into a new spreadsheet. Type the
code in listing 2:
Private Sub GetData()
Dim wb As Workbook
Dim ws As Worksheet
'put it into a new workbook
Set wb = Workbooks.Add
Set ws = wb.Worksheets(1)
SQLXL.SQL.setText "select * from emp"
Set SQLXL.SQL.Statements(1).Target =
Listing 2: Execute a query into a new worksheet using SQL*XL commands
The code in listing 2 declares 2 variables of type workbook and worksheet. The first variable wb will be populated
by Excel's Workbooks.Add command which will create a new workbook (.xls file). The second variable ws is pointed
at the first worksheet (tab) within the new workbook. Change the string "select * from emp" with a query
you like to see executed, a query always works is "select sysdate from dual" which will retrieve the
Last but not least we need a function that connects to the database and gets the data. Type the code in listing
Public Sub GetItNow()
Listing 3: Putting it all together: create a global macro to be called from the
This procedure will be visible from the workbook, that's why it is declared as public. The other 2 routines
will be invisible and hence they are declared as Private. The GetItNow procedure call our two other routines. First
it will connect to the database. Secondly it will get the data.
To execute the code close the Visual Basic Editor and return to the Excel spreadsheet. To run the macro execute
the menu Tools|Macros|Macros... Select the macro ThisWorkbook!GetItNow and press the Run button. A dialog will
great you saying "Connected". Click OK and the data will be fetched and shown.
Tip: To learn more about SQL*XL's internal commands, use the Object Browser (press F2 in the Visual Basic Editor)
and select SQL XL in the library list.
Copyright (C) 1995-2014 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.