Using FieldInfo parameter of OpenText Excel Command - Keeping Leading Zeros

Dawn M

Member
OpenEdge 10.2b
Windows XP
Excel 2007

I have a csv file that I am importing into an Excel spreadsheet using the OpenText command. This spreadsheet has one column that can contain a either text (ABC11111X) or can have numbers with leading zeros (003847646).

The OpenText command removes the leading zeros when importing the data. I need it to keep these zeros when loaded.

Progress claims that you can pass an array to a COM object in OE10 and has even given an example in their KB (https://progress.my.salesforce.com/articles/Article/000028089?popup=true). However, this code won't even compile for me ("Only individual array elements allowed in expressions (361)").

There should be a way to send the FieldInfo the arrays it seeks. I've tried "Array(Array(1, 2), Array(2, 2))", both hard-coded and in a variable. My program compiles but doesn't work. (I've used the Array string in previous macros with success.)

Has anyone been able to find a way to use OpenText and not truncate leading zeros?

(I must open the csv file using the OpenText command.)

Any guidance will be greatly appreciated.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If you are writing ABL code, I am curious as to why you say you must use the OpenText method. Is this due to some in-house development policy that prevents you from getting text into a worksheet in some other way?

Also, it would be helpful if you posted the code for your method call, and the error you get when it runs.
 

Dawn M

Member
Rob, it's actually a report generated with embedded SpeedScript. The reason why we are using OpenText is speed and because that's what my boss wants.

I get the 361 compile error I noted above when I try to pass a Progress array as a parameter the OpenText function. When I use a hard-coded text string or Progress variable, the program runs bu I get the message

"Error occurred while accessing component property/method: Open Text. OpenText method of Workbooks class failed".
 

Dawn M

Member
After quite few hours researching the issue, I've finally found a solution that works. When creating the .txt data file, if you append =" in front of the field and a " at the end, Excel will pull it in correctly.

In Progress, it would be:

assign outputField = "=~"" + field-name + "~"".

In the .txt data file (comma-delimited), it would show as:

"DATA1","=""0012345""","DATA2"

Problem solved.
 
Top