Import text file FieldInfo:=Array

Hi,

Is there a way to use "FieldInfo:=Array(Array(1, 2), Array(43, 2))"
when importing data instead of the individual array elements ie

"FieldInfo:=Array(Array(1, 2), Array(2, 2)...." And to make the
FieldInfo:=Array convert the information to TEXT instread of general?

Example code
**************************************************************************
Sub AIR_Report()
'
'===========================================================
Dim V As Variant 'This segment of dims is for split
Dim s As String
Dim newDate As String
Dim j As Long
'===========================================================
Dim X As Integer
Dim k As Integer
Dim kk As Integer
Dim aDate As Integer
Dim CntDels As Integer 'counts deleted rows
Dim lenX As Integer
Dim txt As Variant
Dim xCell As Range
'Dim fname As String
Dim vDate(1 To 4) As Integer
'========= Message ==================================
Dim msg, Style, Title, Response
'========== remove returns ==========================
Dim WS As Worksheet

'FieldInfo:=Array(Array(1, 2), Array(43, 2)) Indavidual cols formated
as general
'used to simplify data entry - doesn't work because it imports data as
general and not txt

Workbooks.OpenText Filename:=Fname, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
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), Array(5, 2), Array(6, 2), Array(7,
2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2),
Array(14, 2), Array(15, 2), Array( _
16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20,
2), Array(21, 2), Array(22, 2), _
Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2),
Array(27, 2), Array(28, 2), Array( _
29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33,
2), Array(34, 2), Array(35, 2), _
Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2),
Array(40, 2), Array(41, 2), Array( _
42, 2), Array(43, 2)), TrailingMinusNumbers:=True

*****************************************************************************

>
> Is there a way to use "FieldInfo:=Array(Array(1, 2), Array(43, 2))"
> when importing data instead of the individual array elements ie
>
> "FieldInfo:=Array(Array(1, 2), Array(2, 2)...." And to make the
> FieldInfo:=Array convert the information to TEXT instread of general?
>

You can skip columns, that is don't import them, by making the second
argument of Array xlSkipColumn, like

Array(3,xlSkipColumn)

will not import column 3.

If you want to import all the columns, but only specifically format a few of
them, you can do that to. Any columns you don't specify will be imported as
general. If you want a column as something other than general, you have to
identify it. The columns can be identified in any order.

To make column 4 text: Array(4, xlTextFormat)

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

> > "FieldInfo:=Array(Array(1, 2), Array(2, 2)...." And to make the
> > FieldInfo:=Array convert the information to TEXT instread of general?

>
> If you want to import all the columns, but only specifically format a few of
> them, you can do that to. Any columns you don't specify will be imported as
> general. If you want a column as something other than general, you have to
> identify it. The columns can be identified in any order.
>
> To make column 4 text: Array(4, xlTextFormat)

**** to clarify, if I want all the columns text then I would use
FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat)....

And if you know all the fields are Text, you can build an array that fieldinfo
can use:

''''more of your other code...
Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)
For iCtr = 1 To 256
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 2
Next iCtr

Workbooks.OpenText Filename:=Fname, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=myArray, _
TrailingMinusNumbers:=True


Greg wrote:
>
> > > "FieldInfo:=Array(Array(1, 2), Array(2, 2)...." And to make the
> > > FieldInfo:=Array convert the information to TEXT instread of general?
>
> >
> > If you want to import all the columns, but only specifically format a few of
> > them, you can do that to. Any columns you don't specify will be imported as
> > general. If you want a column as something other than general, you have to
> > identify it. The columns can be identified in any order.
> >
> > To make column 4 text: Array(4, xlTextFormat)
>
> **** to clarify, if I want all the columns text then I would use
> FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat)....

--

Dave Peterson