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 VBA SQLXL

Buy now

Download now

SQL*XL online help



SQLXL Object




The SQLXL object encapsulates all the functionality inside the SQL*XL addin. Whenever the addin is loaded in Excel the object is available by the global name SQLXL.

To automatically load and use SQL*XL when your spreadsheet opens use the SQLXL.OnLoad property to set a callback procedure (see below). You can use the callback procedure to e.g. automatically login or execute queries etc.


Name Data Type Description
OnLoad As String

Callback procedure to be called by SQL*XL once it is fully loaded in and initialised.

Example: SQLXL.OnLoad = "AutoLoad.xls!MyProc"

This will call the MyProc procedure in the AutoLoad.xls workbook. Make sure you create MyProc in a new module and make it public:
Public Sub MyProc()

SQL As clsSQL The SQL object. Manages all handling of SQL commands


Name Parameters Description
ExecutePLSQLStatement Text As String Executes a PLSQL statement. You can also use this to execute DDL and DML
InsertRecordset Table As String
Columns As String
DataRange As Range
PromptOnError As Boolean
CommitFrequency As Long
Orientation As Integer
Feedback As Boolean

Inserts a block of data into the database. This is the function being executed when the ok button is pressed on the multi row insert dialog.

Note that the columns parameter is a comma separated list with the column names in the table.

Note that Orientation is either litHorizontal or litVertical.

Targets Index As Integer

Returns: Target
The Targets property is a list of output targets for SQLXL. As index you can use the predefined target constants: litExcel, litWord and litOutlook. The return value is the target object itself.



InsertRecordset as recorded by the macro recording facility:

Sub Macro1()
	SQLXL.InsertRecordset Table:="my_table", _
		Columns:="col1,col2,col3", _
		DataRange:=Range("$A$2:$C$10"), _
		PromptOnError:=False, SortToStatus:=True, _
		CommitFrequency:=5, Orientation:=1, _
		Silent:=True, Feedback:=True
End Sub

InsertRecordset example using a named DataRange. To create a named range select the cells on the worksheet and use insert name to define a new name:

Sub Macro2()
	SQLXL.InsertRecordset Table:="my_table", _
		Columns:="col1,col2,col3", _
		DataRange:=Range("my_named_range"), _
		PromptOnError:=False, SortToStatus:=True, _
		CommitFrequency:=10, Orientation:=1, _
		Silent:=True, Feedback:=True
End Sub

InsertRecordset inserting all data on the active worksheet. Use this scenario if you have an unknown number of rows. It is assumed that the data starts on row 2 in column 1 (A2). It is also assumed that no other data is typed on the sheet (it is a completely empty sheet apart from the data you want to insert)

Sub Macro3()
	Dim rngAllData As Range
	Dim lAllRows As Long
	Dim lAllColumns As Long

	lAllRows = ActiveSheet.UsedRange.Rows.Count
	lAllColumns = ActiveSheet.UsedRange.Columns.Count
	Set rngAllData = Range(Cells(2, 1) , Cells(lAllRows, lAllColumns))
	SQLXL.InsertRecordset Table:="my_table", _
		Columns:="col1,col2,col3", _
		DataRange:=rngAllData, _
		PromptOnError:=False, SortToStatus:=True, _
		CommitFrequency:=5, Orientation:=1, _
		Silent:=True, Feedback:=True
End Sub

SQL*XL ribbon in Excel