How to Retrieve Request Items Using DDERequest to MSQueryLast reviewed: December 1, 1997Article ID: Q149581 |
The information in this article applies to:
SUMMARYWhen you use DDERequest statements to return request items from Microsoft Query, the data is always returned as an array. Because a number of DDERequest statements will return a single dimension array under some circumstances and a two dimension array under other circumstances, failure to retrieve the data properly may result in Subscript Out Of Range errors. This article identifies which DDERequest statements return each of the following:
MORE INFORMATIONThe code samples shown below assume that you are familiar with how to use Microsoft Query. (See the "References" section below for places you can refer to for more information about using Microsoft Query.) Before you attempt to run any of these example subroutines, be sure that Microsoft Query is open, and that you run the subroutine from an active empty worksheet.
Sample Visual Basic CodeMicrosoft 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 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 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/default.asp Single Dimension Array Result of One Element EachThe following DDERequest items will always return a one dimension, single element array:
Sub OneDimArray() ' Open a channel to Microsoft Query using DDE. Chan = DDEInitiate("MSQUERY", "System") ' Enable a user to build his or her own query in Microsoft Query and ' Exit Microsoft Query using "Return to Excel" on the File Menu. DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]" ' Return the request item desired. In this example you return ' the name of the Data Source in use by the active query. MsgBox DDERequest(Chan, "DataSourceName")(1) ' Exit Microsoft Query if this is the only Query open. If other ' queries are open and you want to close them all, use ' "[Exit(True)]". DDEExecute Chan, "[Exit(False)]" ' Terminate the DDE channel. DDETerminate Chan End Sub Two Dimension Column Array ResultThe following DDERequest items will always return a two dimension, single column (vertical array) result:
Sub TwoDimArray() ' Open a channel to Microsoft Query using DDE. Chan = DDEInitiate("MSQUERY", "System") ' Enable a user to build his or her own query in Microsoft Query and ' Exit Microsoft Query using "Return to Excel" on the File Menu. DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]" ' Return the request item desired. In this example you return ' the names of all remote databases available in the ODBC Data ' Sources. LogArray = DDERequest(Chan, "Logon") ' Determine how many elements are in the first dimension of the ' returned array. The second dimension of the array will always be ' one. LogLen = UBound(LogArray, 1) ' Set up a loop to display each database name. For i = 1 To LogLen ' Display each database name in a message box. MsgBox "Logon function - Returns the ODBC.INI Data Source" _ & "Connections" & Chr(10) & Chr(10) & "Logon Connection " _ & i & ": " & LogArray(i, 1) ' Return to "For I" above until all database names have been ' displayed. Next i ' Exit Microsoft Query if this is the only Query open. If other ' queries are open and you want to close them all, use ' "[Exit(True)]". DDEExecute Chan, "[Exit(False)]" ' Terminate the DDE channel. DDETerminate Chan End Sub Multiple-Dimension Array ResultThe following DDERequest items return a one dimension array if the number of data items in the requested list is one, and a two dimension array if the number of data items in the requested list is more than one.
Sub MultiDimArray() ' Open a channel to Microsoft Query using DDE. Chan = DDEInitiate("MSQUERY", "System") ' Enable a user to build his or her own query in Microsoft Query and ' Exit Microsoft Query using "Return to Excel" on the File Menu. DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]" ' Return the request item desired. In this example we are returning ' the names of all the currently connected remote databases. OffArray = DDERequest(Chan, "Logoff") ' Determine how many elements are in the first dimension of the ' array. The second dimension of the array will always be one. OffLen = UBound(OffArray, 1) ' If the first dimension of the array contains only one element... If UBound(OffArray) = 1 Then ' Then display the database name in a message box. MsgBox "Logoff function - Returns the currently" _ & " connected database name" & Chr(10) & Chr(10) _ & "Database: " & OffArray(1) ' However, if there is more than one value in the first dimension ' of the array... Else ' Then loop through all of the values returned. For i = OffLen To 1 Step -1 ' And display each one in a message box. In this case, we are ' displaying the most recently accessed database name first. MsgBox "Logoff function - Returns all currently" _ & " connected remote databases" _ & Chr(10) & Chr(10) & "Connected Database " _ & i & ": " & OffArray(i, 1) ' Return to "For I" above until all database names have been ' displayed. Next i ' End the Block If statement. End If ' Exit Microsoft Query if this is the only Query open. If other ' queries are open and you want to close them all, use ' "[Exit(True)]". DDEExecute Chan, "[Exit(False)]" ' Terminate the DDE channel. DDETerminate Chan End Sub Special Case: Two Dimension Table Array ResultThe following DDERequest item will always return a two dimension array.
If there is only one row (record) in the FieldDef result, the first dimension of the array returns a 5, the number of columns (fields) in the array. If there are two or more records, the first dimension returns the number of records (rows) and the second dimension returns the number of columns. If the result is a single record, you cannot determine the number of array dimensions by counting the elements in the first dimension. If you try to test the second dimension you can get a "subscript out of range" error message. To address this situation you can use the "On Error Resume Next" logic as in the following example to open a channel to Microsoft Query, to build your own query in Microsoft Query and exit to Microsoft Excel, to retrieve the data, to determine if the data is in a one or two dimension array, to insert the requested table into the active worksheet, and to close the channel.
Sub TableArray() ' Open a channel to Microsoft Query using DDE. Chan = DDEInitiate("MSQUERY", "System") ' Enable a user to build his or her own query in Microsoft Query and ' Exit Microsoft Query using "Return to Excel" on the File Menu. DDEExecute Chan, "[UserControl(' &Return to Excel' ,3,true)]" ' Return the request item desired. In this example we are returning ' a table of values that describe the attributes of the database data ' result. FieldArray = DDERequest(Chan, "FieldDef") ' Turn on error handling. In this case, you want to resume with the ' next line of code if an error is detected. On Error Resume Next ' You can now use "IsError" to test to see if trying to access the ' second dimension will produce an error. If IsError(Fieldcols = UBound(FieldArray, 2)) Then ' If you get an error, suspect that there is only one dimension ' in the array (other errors could also occur) and set the number ' of field rows to 1 and the field column count to 5. This code does ' not address any other error condition. Fieldrows = 1 Fieldcols = UBound(FieldArray, 1) ' Once you pass this point, you want turn off the error ' handler (unless you want to write code to handle other ' potential errors) On Error GoTo 0 ' If there was no error, then this is a two dimension array. Else ' Get the number of records from the first dimension. Fieldrows = UBound(FieldArray, 1) ' Get the number of columns from the second dimension. Fieldcols = UBound(FieldArray, 2) ' End the Block If statement. End If ' Resize the worksheet range for the number of rows and columns ' in the table and load the data onto the worksheet. Worksheets("Sheet1").Range("A1").Resize(Fieldrows, Fieldcols) = _ FieldArray ' Exit Microsoft Query if this is the only Query open. If other ' queries are open and you want to close them all, use ' "[Exit(True)]". DDEExecute Chan, "[Exit(False)]" ' Terminate the DDE channel. DDETerminate Chan End Sub REFERENCES"Getting Started with Microsoft Office for Windows 95" Microsoft Query Online Help "Microsoft Query version 1.0 User's Guide," Chapter 9,"Using Dynamic Data Exchange with Microsoft Query"
|
Additional query words: 1.00 2.00 5.0 5.0c 5.00 5.00a 5.00c 7.00
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |