Problem with Excel DDE

[Guest]

Hi all...

I'm working on an old application that uses DDE to transfer data to an Excel
spreadsheet. It nearly works, except it treats all numeric data as numbers,
which isn't always the case (For example, a value of 0002 should be stored as
0002, but Excel treats it as a number and stores it as 2).

The app is currently using the following macro to get Excel to open the
datafile:

Open("c:\sheet.xls", 1)

sheet.xls is a tab delimited file, and it works okay except for the
conversion problem.

I've dug out a copy of macrofun.hlp, and I think I can fix this by using the
macro "open.text". This basically has parameters equivalent to the Text
Import Wizard. It has this syntax:

OPEN.TEXT(file_name, file_origin, start_row, file_type, text_qualifier,
consecutive_delim, tab, semicolon, comma, space, other, other_char,
{field_info1; field_info2;...})

I'm fine with all the parameters, except for the last "field_info" section.
If I omit it, the file loads. However, I need that section, because that is
what I think I can use to map the correct datatypes onto the columns of data.
According to the description it is:

an array which consists of the following elements: "column number,
data_format"

Thing is, I can't figure out what this should look like in my command. At
the moment I have something like:

OPEN.TEXT("c:\sheet.xls",2, 1,1, 3, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE,
FALSE, {1,2; 2,2;3,2})

.... and it doesn't work. The data is not imported at all. Again, if I omit
the part contained in the {}, it works fine (bar the conversion problem!).
I've also tried {{1,2};{2,2};{3.2}} to no avail.

Can anyone help with what I should have in there?

Alternatively, is it possible to construct visual basic instructions and
pass them through DDE instead?

Thanks!

Richard

 

[keepITcool]

fieldinfo must be a 1 based array

unless your module starts with option base 1
vba generates 0 based arrays.

a handy trick is to use evaluate (or it's bracketed notation...)
to have excel create the 1 based array..

Dim fi
fi = [{1,2;2,2;3,2}]
Workbooks.OpenText _
Filename:="c:\test.txt", _
DataType:=xlDelimited, _
Tab:=True, _
semicolon:=0, comma:=0, space:=0, other:=0, _
fieldinfo:=fi





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


richardm wrote :