SQL*XL online help

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:

Methods:

 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