As stated before you can use SQLOpen to connect to any ODBC data source Examples are:
Using Username and Password:
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
Returns the whole record set as an array. This assumes that there is a SQLOpen statement in cell A1.
Returns only a single result which is the upper left corner. This assumes that there is a SQLOpen statement in cell A1.
Returns the second column with column including column names. This assumes that there is a SQLOpen statement in cell A1.
To see all the columns from the database you could also use feature Microsoft Excel provides known as Arrays.
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
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.
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.
The above statement will return sum of the A1 column as passed in the SQLOpen Statement as Sql query.
You also have the ability to embed the SQLGet Statements in to already existing Excel Formulas
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
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.