Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help


SQL*XL: Techniques: Execute SQL*XL from Excel macros (VBA)

Execute SQL*XL from Excel macros (VBA)


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 be Book1.

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
	End If
	SQLXL.Database.Connect "scott", "tiger", "beq-local",litOO4O
	If SQLXL.Database.Connected = True Then
		MsgBox "Connected"
		MsgBox "Connection Failed"
	End If
End Sub

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 = 
End Sub

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 system date.

Last but not least we need a function that connects to the database and gets the data. Type the code in listing 3:

Public Sub GetItNow()
End Sub

Listing 3: Putting it all together: create a global macro to be called from the Excel sheet.

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.

See also:

SQL*XL ribbon in Excel