XL: Visual Basic Example Using BuildSQL

• 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

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.

Microsoft provides examples of Visual Basic for applications procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

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