Microsoft KB Archive/142357

= Visual Basic Example Using BuildSQL =

Article ID: 142357

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q142357



SUMMARY
In Microsoft Excel, DDE (dynamic data exchange) can be used in Visual Basic for Applications to communicate with Microsoft Query. The BuildSQL command allows the creation of SQL statements greater than 255 characters.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. When you use the BuildSQL command with an SQL statement that is greater than 255 characters, you can send smaller pieces of information to a Microsoft Excel macro to build the complete SQL statement. The BuildSQL command is available only on the System channel.

The following example runs a query, returns the query string in pieces to an array, builds the SQL statement, runs the new query, and returns the result set to Sheet1. To use the following macro, Microsoft Query must be running when you start the macro. When you use the following macro in Microsoft Excel 7.0, you must create a data source with the name "NWind" using the dBASE files in the MSQuery directory. Sub UsingBuildSQL

' Dimension the variables. Dim s As Variant Dim myrequest As Variant Dim chan As Integer

' Initiate a channel to MSQuery. chan = DDEInitiate("MSQuery", "System")

' Log on to the Nwind datasource. DDEExecute chan, "[Logon('NWind')]"

' Execute a SQL statement. DDEExecute chan, "[Open('Select * From Employee')]"

' Request the ODBC SQL statement in strings of 120 characters ' and places it into an array. myrequest = DDERequest(chan, "ODBCSQLStatement/120")

' Execute the BuildSQL statement for each element returned. For Each s In myrequest DDEExecute chan, "[Buildsql('" & s & "')]" Next

' Execute the SQL statement built by BuildSQL. DDEExecute chan, "[QueryNow]"

' Request the number of rows. NumRows = DDERequest(chan, "NumRows")

' Request the number of columns. NumCols = DDERequest(chan, "NumCols")

' Return the data to Sheet1. DDEExecute chan, "[Fetch('Excel','Sheet1','R1C1:R" & NumRows(1) & _     "C" & NumCols(1) & "','All/Headers')]"

' Terminate the DDE channel. DDETerminate chan

End Sub

