SQLBind Function in Excel 2007 development

It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLBind and the other ODBC functions in the Xlodbc.xla add-in.

SQLBind specifies where results are placed when they’re retrieved with SQLRetrieve or SQLRetrieveToFile. Use SQLBind to change the column order of the result set from a query, or to place the result set columns in nonadjacent worksheet columns.

This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor.

SQLBind(ConnectionNum , Column , Reference)

Return Value

This function returns an array that lists the bound columns for the current connection, by column number.

If SQLBind is unable to bind the column to the cell in the specified reference, it returns Error 2042.

If ConnectionNum is not valid or if you try to bind a cell that is unavailable, SQLBind returns Error 2015.

If Reference refers to more than a single cell, SQLBind returns Error 2023.

If SQLRetrieve does not have a destination parameter, SQLBind places the result set in the location indicated by Reference .

Remarks

SQLBind tells the ODBC Control Panel Administrator where to place results when they are received by way of SQLRetrieve The results are placed in the reference cell and the cells immediately below it.

Use SQLBind if you want the results from different columns to be placed in disjoint worksheet areas.

Use SQLBind for each column in the result set. A binding remains valid as long as the connection specified by ConnectionNum is open.

Call SQLBind after you call SQLOpen and SQLExecQuery, but before you call SQLRetrieve or SQLRetrieveToFile. Calls to SQLBind do not affect results that have already been retrieved.

Example

This example runs a query on the Northwind database, and then it uses the SQLBind function to display only the fourth and ninth columns of the query result set (the product name and the quantity on order) on Sheet1.

 databaseName = "Northwind"
queryString = _
"SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
Set output1 = Worksheets("Sheet1").Range("A1")
Set output2 = Worksheets("Sheet1").Range("B1")
SQLBind chan, 4, output1
SQLBind chan, 9, output2
SQLRetrieve chan
SQLClose chan 

SolverFinish Function in Excel 2007 development

Tells Microsoft Office Excel what to do with the results and what kind of report to create when the solution process is completed.

Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder.

SolverFinish( KeepFinal , ReportArray )

ReportArray    Optional Variant. The kind of report that Excel will create when Solver is finished: 1 creates an answer report, 2 creates a sensitivity report, and 3 creates a limit report. Use the Array function to specify the reports you want to display — for example, ReportArray:= Array(1,3).

Example

This example loads the previously calculated Solver model stored on Sheet1, solves the model again, and then generates an answer report on a new worksheet.

 Worksheets("Sheet1").Activate
SolverLoad LoadArea:=Range("A33:A38")
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1, ReportArray:=Array(1)