The Excel DDERequest function does not return a string variable when used to request text data from a DDE server, it returns a “Variant Array” data type. Although Excel will allow you to assign variant array data to cells in a spreadsheet, you cannot use Visual Basic (VBA) string functions to manipulate data contained in a Variant Array.
You must first convert the Variant Array Data to a string type variable and then use string functions on the string variable. The process of converting the Variant Array data to string data is extremely simple and is accomplished by assigning element 1 of the variant array to a string variable as in the following code:
Chan = DDEInitiate("WinWedge", "COM1") ' open a DDE channel
MyVariantArray = DDERequest(Chan, "Field(1)") ' request some text data
DDETerminate Chan ' close the channel
MyString$ = MyVariantArray(1) ' convert array data to string
' the DDE Data is now in a true string variable
' now we can perform string functions on the data as in the following line
MySubstring$ = Mid$(MyString$, 3, 5)
Although the above code works perfectly for most text data, there are a few exceptions where the code does not work. The problems that you may encounter are:
- Numbers with leading zeros are brought into Excel with all leading zeros truncated.
- Long strings of numeric data are automatically converted to exponential notation.
Because the DDERequest function in Excel returns a variant array data type, when you convert a number that contains leading zeros to a string type variable (using the method shown above), Excel automatically truncates the leading zeros from the number stored in the string variable. For example if you were requesting text data from a DDE server and the data consisted of the numeric digits “00123454.12354”, the above example would place the string “123454.12354” in the variable MyString$. A similar problem occurs when passing long strings of numeric data to the DDERequest function. If you were to pass the string “11111111111111111111” through the Excel DDERequest function and then convert the data from a variant array to a string, Excel would return the string “1.11111111111111E+19”.
The problem exists because the Excel DDERequest function returns a variant array instead of a string (which is what every other application returns including all other Microsoft applications). The variant data type is a multi purpose data type that is designed to hold any possible data type and automatically convert the data from one type to another depending on the type of variable that the data is being assigned to. The problems described above are side effects of the automatic conversion capabilities of the variant data type.
The only way around the problems described above is to include at least one alpha character in the data so that it cannot logically be converted to a numeric value. For example if the following string “A0001234.123” were pulled into Excel using the DDERequest function, it would remain unchanged when converted from a variant array to a string. Since the data cannot be converted to a number, Excel has no choice other than to leave the data as a string.
In the WinWedge professional edition, you could force a particular data field to include a leading alpha character by placing the character in the “Format Expression” for the field along with some other simple formatting functions. For example, the format string: “A”!& (with quotes) entered as a format expression for a data field in WinWedge would force all data for that field to have a leading capital A character. The exclamation character (!) and the ampersand (&) are string formatting characters that cause the Wedge to include the the data for the field along with the capital A and also to fill the field from left to right with the data. (Please refer to WinWedge Pro version manual for details on all formatting expressions.)
To remove the capital A from the data returned by the DDERequest function in Excel, you could use the following code:
Chan = DDEInitiate("WinWedge", "COM1") ' open a DDE channel
MyVariantArray = DDERequest(Chan, "Field(1)") ' request some text data
DDETerminate Chan ' close the channel
MyString$ = MyVariantArray(1) ' convert array data to a string
MyString$ = Mid$(MyString$,2) ' strip off the leading "A" character