One of the important functionalities of Microsoft Excel is the capability of connecting to outside data sources and retrieve data into an Excel file. SpreadSheetWEB provides the same functionality through custom worksheet functions of SQLOpen and SQLGet. SQLOpen function creates a dynamic connection to the desired database server or file while SQLGet uses the connection created by SQLOpen to retrieve the data.
You can use SQLOpen to gather data from
- Microsoft Access Files
- Microsoft SQL Servers through ODBC driver
- Microsoft SQL Servers through OLEDB driver
- Any database through ODBC ( including text files,*.csv etc )
SpreadSheetWEB SQLOpen and SQLGet provide us with this functionality. SQLOpen and SQLGet are custom worksheet functions. SQLOpen function creates a dynamic connection to the desired database server or file while SQLGet uses the connection created by SQLOpen to retrieve the data.
The Function headers are:
- SQLOpen( QueryString,ConnectionString)Query String: Is the Sql command which will be executed.Connection String: Is a string containing database connection information. Although connection strings varies depending on the database type, it usually contains information about the server, database, connection type, username and password.
- SQLGet(ConnectionNumber,RowNumber,ColumnNumber,IncludeHeaderColumns,Return Extended Column Info )
Connection Number is the number returned by SQLOpen. It is best to use a cell reference to where SQLOpen resides because the number returned by SQLOpen is dynamic.
Row Number is the index to the row of the record set that is requested. If omitted all rows are returned as an array.
Column Number is the index to the column of the record set that is requested. If omitted all columns are returned as an array. If both Row Number and Column Number are omitted, whole record set will be returned as an array.
Include Header Columns indicates if name of the columns should be included with the returned results. Note that all indexes will shift one depending on the value of this parameter.
Return Extended Error Info indicates if the function should return a string describing the reason of the error if the function is not successful. Default behavior of this function is to return #VALUE! Error if the function is not successful.
If connection number is < 1 or > maximum valid connection number returned by SQLOpen #VALUE! is returned or "Illegal parameter. Connection number, column number or row number is not valid." If the record set of the specified connection number doesn't exist #VALUE! Is returned or "Either table is empty or table does not exist." If a row number or column number is specified which is < 0 or > record set/columns #VALUE! is returned or "Illegal parameter. Connection number, column number or row number is not valid." The error is returned for every cell if row number or column number is not specified and an array is returned.