Variable 'fieldinfo' array in Excel opentext

[SmallCraig]

This is a question I've considered for some time, but the need hasn't been so pressing until now.

In Excel, the "opentext" method accepts an array of "fieldinfo", which tells the open method how to treat each column of data imported. I have the potential need to make that array variable in nature, and pass to the Excel object the number of columns I'm expecting it to process.

I've tried setting up a string of "Array(a,1),Array(b,1)...", then placing the string in the statement:

strFieldArray = "Array(1, 1),Array(2, 1)"
    
For intLoop = 1 To pWeeks
    
    strFieldArray = strFieldArray & ",Array(" & CStr (intLoop + 2) & ",2)"
    
Next

Workbooks.OpenText _
            Filename:=pInputFile, _
            Origin:=xlWindows, _
            StartRow:=1, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote,
            ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:= _
            False, Comma:=False, Space:=False,
            Other:=True, OtherChar:="#", _
            FieldInfo:=Array(strFieldArray)

This code falls over on the "opentext".

Can this ever work? Does it make a difference that I'm calling this from a VB program (just imagine that the correct object details precede the method names)?

I'd appreciate any input on this issue.

Thanks loads,

SmallCraig

 

[SkipVought]

SmallCraig,,

I put you array values in a range (named aryRange) and used this code sucessfully...

    Selection.TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlFixedWidth, _
        FieldInfo:=Range("aryRange"), _
        TrailingMinusNumbers:=True

Skip,

http://www.TheOfficeExperts.com

 

[SmallCraig]

Skip,

Thanks for your reply.

I tried what I thought you meant, but met with no success. So, a couple of questions:

When you say a "range", do you mean a "named range", rather than a range as in

Dim aryRange As Range

Also, I was attempting an "OpenText" rather than a "TextToColumns". Is the principle the same, or will I need to open the file with no translation, then do the "TextToColumns" thing?

Again, thanks for your answer.

SmallCraig

 

[SkipVought]

SC,

The answers are Yes, Yes, Yes.


When you say a "range", do you mean a "named range", rather than a range as in

Dim aryRange As Range

Either will work

FieldInfo:=Range("aryRange")
or
Dim aryRange as Range
FieldInfo:=aryRange

Also, I was attempting an "OpenText" rather than a "TextToColumns". Is the principle the same, or will I need to open the file with no translation, then do the "TextToColumns" thing?

FieldInfo is handled the same in both.



Skip,

http://www.TheOfficeExperts.com