Problem with Excel DDE

[richardm]

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.

So the app is 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's what I use to map the correct datatypes onto the columns of data - 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. Again, if I omit the bolded part, it works fine.

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!

 

[69dodge]

Try this: {1,2; 2,2; 3,2}

It's basically a guess on my part, but the help entry for the Request function contains something that looks like that.

 

[richardm]

Thanks! Unfortunately it doesn't seem to like that either. I've now tried numerous combinations and nothing seems to hit the spot. Perhaps it's broken?

 

[69dodge]

http://www.msusenet.com/archive/index.php/t-552775.html ?

 

[varwoche]

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

Nope.

 

[richardm]

Thanks for the responses. I've tried all sorts of combinations and nothing seems to work, so at the moment I'm working on the basis that it's broken and parked the issue. (Interesting that that msusenet guy has exactly the same problem!)

Varwoche - thanks, that'll save me plenty of time.

 

[varwoche]

For the Open.Text argument, have you tried recording a macro and examining the code it generates?

 

[richardm]

Yeah, I tried that and it came up with something completely different to what I was expecting. Stuff like:


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 28/04/2005 by richardm
'

'
Workbooks.OpenText Filename:="C:\demo.txt", Origin:=xlWindows,
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2),
Array(3, 2), Array(4, 2))
End Sub


I did try that Array format in my call, and it spat it out. I also tried putting the whole Subroutine into my call. Nope!