Background:
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:
1. Numbers with leading zeros are brought into Excel with all leading
zeros truncated.
2. Long strings of numeric data are automatically converted to exponential
notation.
How To Fix The Problem:
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
Getting past the 40 field limit in WinWedge |
