Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem viewing Excel 2003 Pivot Chart fields in Excel 2007 | Charts and Charting in Excel | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Discussion (Misc queries) | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
EXCEL FORMAT PROBLEM WHEN SENDING EXCEL SHEET AS MESSAGE BODY IN . | Excel Discussion (Misc queries) |