SQL*XL API: SQLXL Object

Description:

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.

Properties:

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

Methods:

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.

Examples:

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

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.