Problem with Excel DDE
 

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

Problem with Excel DDE
 

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 :

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