Invalid chars when reading Excel data via DDERequest

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I would like to read data from an Excel file and add it to a table in Access
2002.
Unfortunately the data I read from Excel is always suffixed with two
'square' characters, so I cannot process it in Access properly.

For example:
If the value of an Excel cell is "ABC", the value that Access returns via
DDErequest is always suffixed with two strange square characters. A statement
in Access that looks for the value "ABC" will never evaluate correctly.

Also, when my code gets to the end of the Excel list (i.e. a blank cell) the
value returned by DDErequest is not null, but again is two square chars.

Can anybody help me process this data correctly?
Is there a char conversion I should apply to the returned value?
Or, is there a better way to read Excel data than DDEInitiate & DDERequest?

Sorry if this is really basic, but I am quite new to this.
Thanks in advance.
-njh

p.s. here's a very basic example of what I'm doing...
**
Dim intchan1 as Double
Dim myRIC as String

intchan1 = DDEInitiate("Excel","Rates.xls")
myRIC = DDERequest(intchan1, R2C1)
**
 
Hi Nick,

The two square boxes almost certainly represent a carriage return and a
line feed, appended by DDE to the cell's contents. (If you request a
range of cells from DDE the values are returned as a string with a CRLF
between each cell's contents.) If using DDE I'd probably do something
like this, which assumes it's passed a single cell reference:

Function DDETest(Address As String)
Dim DDEChannel As Long
Dim TheValue As String

DDEChannel = DDEInitiate("Excel", "C:\Temp\Eof5.xls")
TheValue = DDERequest(DDEChannel, Address)
TheValue = Left(TheValue, Len(TheValue) - 2)
DDETest = TheValue
DDETerminate DDEChannel
End Function

The alternative is to use Automation, controlling Excel to open the
worksheet and get the value. This gives you much more control over what
happens; there's a sample procedure at
http://www.mvps.org/access/modules/mdl0006.htm and much more information
on the web, including http://support.microsoft.com/?id=253235
 
Hi John

This is very helpful, and answers my question perfectly.
I will also look into Automation too, so thanks for the tip.

Many thanks for your time.
-njh
 
Back
Top