Execute SQL*XL from Excel macros (VBA)

SQL*XL home

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
		SQLXL.Database.DisConnect
	End If
	
	SQLXL.Database.Connect "scott", "tiger", "beq-local",litOO4O
	If SQLXL.Database.Connected = True Then
		MsgBox "Connected"
	Else
		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 = 
    SQLXL.Targets(litExcel)  
	SQLXL.SQL.Statements(1).Execute  
End Sub

---

Listing 2: Execute a query into a new worksheet using SQL*XL commands