posted June 09, 2002 11:18 PM            

' VBA code with 'FieldInfo' parameter. 
Workbooks.OpenText Filename:="c:\pw\hyene\exe\hyene3.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, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ 
        Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))

' My PB code with 'FieldInfo' parameter 
vFieldInfo = FieldInfo()
OBJECT CALL oExcelApp.WorkBooks.Opentext(_
            Filename=vFilename, _
            DataType=vDataType, _
            TAB=vTab,_
            FieldInfo=vFieldInfo)  

Thanks...

Kristian Hyllestad
SPINE AS (www.spine.no)

Info from MS documentation:
....
FieldInfo
Optional Variant. An array containing parse information for
individual columns of data. The interpretation depends on
the value of DataType.
When the data is delimited, this argument is an array
of two-element arrays, with each two-element array
specifying the conversion options for a particular column.
The first element is the column number (1-based), and the
second element is one of the xlColumnDataType constants listed
in the following table, specifying how the column is parsed.

Constant Description
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip column

The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

.....

posted June 11, 2002 05:43 PM            

Found also the code below for VC4 which seems reasonable fear to convert for an experienced
basic programmer (so I'm excluded!!). I have tried to convert the code but I'm not even able to use the
SafeArrayCreate with success. So I quit for now.

  // Process the fieldinfo array (1st column = standard, 2nd column = text)
  VARIANT vaCol1, vaCol2, vaFieldInfo;
  VariantInit (&vaCol1);      // Array (1,1) in VB
  VariantInit (&vaCol2);      // Array (2,2) in VB
  VariantInit (&vaFieldInfo); // Array (Array (1,1), Array (2,2)) in VB
  // Initialize the bounds for the arrays  SAFEARRAYBOUND sabOuter[1];	//
outer array corresponds to Array (Array(..), ...)  SAFEARRAYBOUND
sabInner[1];  // inner array corresponds to Array (1,1) and Array (2,2)
  // Set up the bounds for the first index
  // All array indices run from 1..2
  sabInner[0].cElements = sabOuter[0].cElements = 2;
  sabInner[0].lLbound   = sabOuter[0].lLbound   = 1;
  // Process inner arrays
  vaCol1.vt = vaCol2.vt = VT_ARRAY | VT_I2;
  vaCol1.parray = SafeArrayCreate (VT_I2, 1, sabInner);
  vaCol2.parray = SafeArrayCreate (VT_I2, 1, sabInner);
  long l;
  short val;
  for (l = 1; l<3; l++) {
    val = 1;
    SafeArrayPutElement (vaCol1.parray, &l, &val);  // col. 1 = (1,1)
    val = 2;
    SafeArrayPutElement (vaCol2.parray, &l, &val);  // col. 2 = (2,2)
  }
  // Process outer array
  vaFieldInfo.vt = VT_ARRAY | VT_VARIANT;
  vaFieldInfo.parray = SafeArrayCreate (VT_VARIANT, 1, sabOuter);
  l = 1l;
  SafeArrayPutElement (vaFieldInfo.parray, &l, &vaCol1);
  l = 2l;
  SafeArrayPutElement (vaFieldInfo.parray, &l, &vaCol2);
  TRY {
    workbooks.OpenText (
      pszFileName,    // Filename
      vOrigin,        // Origin
      vStartRow,      // StartRow
      vDataType,      // DataType
      nTextQualifier, // TextQualifier
      v1,             // ConsecutiveDelimiter
      vTab,           // Tab
      vSemi,          // Semicolon
      vComma,         // Comma
      vSpace,         // Space
      vOther,         // Other
      vOtherChar,     // Other char
      vaFieldInfo,    // Fieldinfo array
      v3              // TextVisualLayout
    );
  }
  CATCH (CException, e) {
  // do some exception handling here...
  // ...
  }
  END_CATCH

Google link:
http://groups.google.c om/groups?hl=no&lr=&threadm=7e08m0%24dkl%241%40nnrp1.dejanews.com&rnum=2&prev=/groups%3Fhl%3Dno%26lr%3D%26q%3Dfieldinfo%2Bopentext

------------------
Kristian Hyllestad
SPINE AS ( www.spine.no )
NORWAY

posted June 11, 2002 06:19 PM            

Try creating the array as a normal PowerBASIC array, and then assign the whole array to the Variant. It should be a whole lot shorter than the Delphi code.

DIM A(lo,hi) AS LONG
...
vVnt = A()

posted June 12, 2002 01:00 AM            

TYPE typeArrayInner
    aInner(0,0) AS LONG
END TYPE
...
    DIM ca(1) AS typeArrayInner
    ca(0).aInner(0,0) = 1  'Array(Array(1,2),Array(2,2))
    ca(1).aInner(0,0) = 2  'Array(Array(1,2),Array(2,2))
    ca(0).aInner(0,1) = 2  'Array(Array(1,2),Array(2,2))
    ca(1).aInner(0,1) = 2  'Array(Array(1,2),Array(2,2))
    vFieldInfo = CA()
    OBJECT CALL oExcelApp.WorkBooks.Opentext(_
            Filename=vFilename, _
            DataType=vDataType, _
            TAB=vTab, _
            FieldInfo=vFieldInfo)

Delphi code performing OpenText found on Google.

  procedure ConvertCVStoXLS;
  var MyExcel: Variant;
      TempArray: OleVariant;
      lvs_FileName : string;
  begin
  MyExcel := CreateOleObject('Excel.Application');
  TempArray := VarArrayCreate([0,3],varVariant);
  TempArray[0] := VarArrayOf([1,2]);
  TempArray[1] := VarArrayOf([2,2]);
  TempArray[2] := VarArrayOf([3,2]);
  MyExcel.Workbooks.OpenText(FileName:=lvs_FileName, Comma:=True,
                             FieldInfo:=TempArray);

posted June 12, 2002 06:22 AM            

In your code you are assigning a UDT array to the variant (VT_USERDEFINED + VT_ARRAY), and the object is not able to determine or utilize the array type in a meaningful way.

Therefore, I suggest you could try making the PB array a variant array, and the assigning that array to the scalar variant (VT_VARIANT + VT_ARRAY) you are passing to the object.

Maybe something like this (pseudocode only!):

DIM vFieldInfo AS VARIANT  ' scalar variant
DIM ca(1:2,1:2) AS VARIANT ' variant array
ca(1,1) = 1 ' arbitrary values for example purposes
ca(2,1) = 2
ca(1,2) = 2
ca(2,2) = 2  
vFieldInfo = ca() ' assign the array to the variant
OBJECT CALL...

posted June 12, 2002 07:23 AM            

You are a genii and thank you so much. It works!
Probably used >20 hours on this subject without getting closer.

...
    DIM ca(1:3,1:2) AS VARIANT ' variant array
    ca(1,1) = 1 ' arbitrary values for example purposes
    ca(2,1) = 2
    ca(3,1) = 3
    ca(1,2) = 2
    ca(2,2) = 2
    ca(3,2) = 2
    vFieldInfo = ca() ' assign the array to the variant
    SET oExcelApp = NEW ExcelApplication IN $PROGID_ExcelApplication
    IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN
        MSGBOX "Excel could not be opened." & $CR & FORMAT$(ISOBJECT(oExcelApp)) & "-"& FORMAT$(ERR) &"-"& _
            "Please check that Excel and VBA are installed."
        FUNCTION = 0
    ELSE
        FUNCTION = 1
    END IF
    LET vBool = 1
    OBJECT LET oExcelApp.Visible = vBool
    vFilename = @lpszFilename
    vDataType = %xlDelimited
    vTab = 1
    OBJECT CALL oExcelApp.WorkBooks.Opentext(_
            Filename=vFilename, _
            DataType=vDataType, _
            TAB=vTab,_
            FieldInfo=vFieldInfo)
....