XL: Visual Basic Example Using BuildSQL

ID: Q142357

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 5.0, 5.0c

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

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

REFERENCES

"Microsoft Query User's Guide," version 1.0, Chapter 9, "Using Dynamic Data Exchange with Microsoft Query"

For more information about BuildSQL, click Search on the Help menu in Microsoft Query and type:

   BuildSQL

Additional query words: 5.00 5.00c 7.00 8.00 97 interoperability

Keywords          : kbprg kbdta kbdtacode KbVBA kbhowto 
Version           : WINDOWS:5.0,5.0c,7.0,97
Platform          : WINDOWS

Last Reviewed: May 17, 1999