As stated before you can use SQLOpen to connect to any ODBC data source Examples are:
Connecting to a SQL Server
=SQLOpen("SELECT * FROM Customers WHERE Country ='USA'", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT ContactName ContactTitle Adress FROM Customers", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT * FROM Customers", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT * FROM Customers", "DSN=Northwind")
=SQLOpen ("SELECT * FROM Customers", ""Provider=sqloledb; Data Source=local; Initial Catalog=Northwind; Integrated Security=SSPI"")
Using Username and Password:
=SQLOpen ("SELECT * FROM Customers", ""Provider=sqloledb; Data Source=local; Initial Catalog=Northwind; UserId = myUserName; Password=myPassword"")
Connecting to Access Database
=SQLOpen ("SELECT TOP 100 [Au_ID|Au_ID] , [Author] , [Year Born] FROM Authors", Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb")
Connecting to a Text File / CSV
SQLOpen ("SELECT TOP 100 [Au_ID|Au_ID] FROM Authors", Driver= {Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc, csv, tab, txt;)
SQLOpen ("SELECT TOP 100 [AU_ID|AU_ID] FROM Authors", Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c: \txtFilesFolder\;Extended Properties="text; HDR=Yes; FMT=Delimited";)
SQLOpen ("SELECT TOP 100 [AU_ID|AU_ID] FROM Authors", Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c: \txtFilesFolder\;Extended Properties="text; HDR=Yes; FMT=Fixed";)
Connecting to an Excel File
=SQLOPEN ("SELECT * FROM \[ Sales$ \] ", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & A1 & "; Extended Properties=Excel 8.0 ;")
Where [ A1 ] in the excel file has the address you would like to connect e.g. C:\MyExcelFile.xls
Where [ Sales$ ] is the workbook sheet you would like to connect
If the value returned by SQLOpen = 0, this means either the Sql query has errors or the database location is not found.
Once a database connection is established and a query is executed, SQLGet function can be used to parse the results into individual cells
=SQLGet (A1)
Returns the whole record set as an array. This assumes that there is a SQLOpen statement in cell A1.
=SQLGet (A1, 1, 1)
Returns only a single result which is the upper left corner. This assumes that there is a SQLOpen statement in cell A1.
=SQLGet (A1, 2, 1)
Returns the second column with column including column names. This assumes that there is a SQLOpen statement in cell A1.
Getting All the Table Information through Arrays & SQLGet
To see all the columns from the database you could also use feature Microsoft Excel provides known as Arrays.
Example:
To see all the records from employee table assuming there is a SQLOpen Statement in CELL A1 we can write a SQLGet Statement such as
{SQLGet (A1, ,,1)}
Please note: To see all the columns make sure you select all the cells which will be filled by the query in the excel file also make sure after writing the SQLGet Statement you need to press cntrl+Shift+Enter in order to create excel arrays.
SQLGet to return the record set as a Calculated Array
Let's say we have a table where we store Total Sales from Orders Placed by the customers to get the total from all the sales we could use the following SQLGet Statement assuming Cell A1 has our SQLOpen Statement.
Example:
=SUM (SQLGet (A1))
The above statement will return sum of the A1 column as passed in the SQLOpen Statement as Sql query.
Embedding SQLGet Function into Excel Formulas
You also have the ability to embed the SQLGet Statements in to already existing Excel Formulas
Example:
Lets say you want to calculate by mixing some values from the database with already present values in the excel file for e.g. in Cell B and you would like to have a total of CELLB1 and some value from the database in CELLC1
We can write such a statement in CellC1 assuming there is a SQLOpen statement in CELLA1
=Sum (B1+SQLGet (A1, 2, 1, TRUE))
At run time the SQLGet Statement will be filled by the value you queried through Sql query and available to your application for necessary processing.