Workbooks.OpenText method, FieldInfo parameter

Hi,

I am struggling to understand the FieldInfo parameter in the
Workbooks.OpenText method. I have a csv file that I am opening, I have no
problem dumping the whole file into a sheet but when I try to use the
FieldInfo parameter to specify which columns to ignore and which should be
treated as text (to preserve a leading 0) I get lost. Can anyone point me to
a worked example or the like, or explain to me in laymans terms how to go
about it?

Regards,

When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.

If you rename your .csv to .txt (or almost anything else), then your macro will
work ok.



SB wrote:
>
> Hi,
>
> I am struggling to understand the FieldInfo parameter in the
> Workbooks.OpenText method. I have a csv file that I am opening, I have no
> problem dumping the whole file into a sheet but when I try to use the
> FieldInfo parameter to specify which columns to ignore and which should be
> treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> a worked example or the like, or explain to me in laymans terms how to go
> about it?
>
> Regards,

--

Dave Peterson

I love continuity!

Thanks Dave. I have 22 columns to bring in, now my understanding is that if
I create an array like

Dim ColumnFormats(1 To 22, 1 To 2) As Variant

Then put 1 to 22 in position 1 column 1 (using option base 1) and then
xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
relevant position and then include FieldInfo:=ColumnFormats in the parameter
list

Then it should all work, or have I got myself confused?

Regards,


"Dave Peterson" wrote:

> When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
>
> If you rename your .csv to .txt (or almost anything else), then your macro will
> work ok.
>
>
>
> SB wrote:
> >
> > Hi,
> >
> > I am struggling to understand the FieldInfo parameter in the
> > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > problem dumping the whole file into a sheet but when I try to use the
> > FieldInfo parameter to specify which columns to ignore and which should be
> > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > a worked example or the like, or explain to me in laymans terms how to go
> > about it?
> >
> > Regards,
>
> --
>
> Dave Peterson
>

ok,

a little more understanding happening here, the list of parameters are
referred to by an index number and not a string. Still not quite there yet.
Here is my code. I have 22 columns in the text file (not CSV) and I have 4
different data types that I want to specify.

Option Explicit
Sub importdata()
Dim myFileName
Dim ColumnsDesired
Dim DataTypeArray
Dim x
Dim ColumnArray(0 To 21, 0 To 3)

myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If myFileName = False Then
MsgBox "Try Later"
Exit Sub
End If

' fill the column and data type information
ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22)
DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
9, 9, 9, 9, 9)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x

' open the file
Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
Comma:=True, FieldInfo:=ColumnArray

End Sub


"SB" wrote:

> I love continuity!
>
> Thanks Dave. I have 22 columns to bring in, now my understanding is that if
> I create an array like
>
> Dim ColumnFormats(1 To 22, 1 To 2) As Variant
>
> Then put 1 to 22 in position 1 column 1 (using option base 1) and then
> xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
> relevant position and then include FieldInfo:=ColumnFormats in the parameter
> list
>
> Then it should all work, or have I got myself confused?
>
> Regards,
>
>
> "Dave Peterson" wrote:
>
> > When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
> >
> > If you rename your .csv to .txt (or almost anything else), then your macro will
> > work ok.
> >
> >
> >
> > SB wrote:
> > >
> > > Hi,
> > >
> > > I am struggling to understand the FieldInfo parameter in the
> > > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > > problem dumping the whole file into a sheet but when I try to use the
> > > FieldInfo parameter to specify which columns to ignore and which should be
> > > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > > a worked example or the like, or explain to me in laymans terms how to go
> > > about it?
> > >
> > > Regards,
> >
> > --
> >
> > Dave Peterson
> >

You have a couple of problems in your code.

First, the easy one:
Dim ColumnArray(0 To 21, 0 To 3)
should be:
Dim ColumnArray(0 To 21, 1 to 2)

And those columnArrays have to be valid--0 isn't a valid choice.

You can use 1-10 (either as numbers or as xlconstants)

XlColumnDataType can be one of these XlColumnDataType constants.

(I think in earlier versions of the help, they actually gave the numbers instead
of xl's constants):

1 xlGeneralFormat. General
2 xlTextFormat. Text
3 xlMDYFormat. MDY Date
4 xlDMYFormat. DMY Date
5 xlYMDFormat. YMD Date
6 xlMYDFormat. MYD Date
7 xlDYMFormat. DYM Date
8 xlYDMFormat. YDM Date
10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
9 xlSkipColumn. Skip Column

I don't think I could guess what you want for each field:

DataTypeArray _
= Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0, 9, 9, 9, 9, 9)

but those 0's can't be there.



SB wrote:
>
> ok,
>
> a little more understanding happening here, the list of parameters are
> referred to by an index number and not a string. Still not quite there yet.
> Here is my code. I have 22 columns in the text file (not CSV) and I have 4
> different data types that I want to specify.
>
> Option Explicit
> Sub importdata()
> Dim myFileName
> Dim ColumnsDesired
> Dim DataTypeArray
> Dim x
> Dim ColumnArray(0 To 21, 0 To 3)
>
> myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> If myFileName = False Then
> MsgBox "Try Later"
> Exit Sub
> End If
>
> ' fill the column and data type information
> ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> 15, 16, 17, 18, 19, 20, 21, 22)
> DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
> 9, 9, 9, 9, 9)
>
> ' populate the array for fieldinfo
> For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> ColumnArray(x, 1) = ColumnsDesired(x)
> ColumnArray(x, 2) = DataTypeArray(x)
> Next x
>
> ' open the file
> Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> Comma:=True, FieldInfo:=ColumnArray
>
> End Sub
>
> "SB" wrote:
>
> > I love continuity!
> >
> > Thanks Dave. I have 22 columns to bring in, now my understanding is that if
> > I create an array like
> >
> > Dim ColumnFormats(1 To 22, 1 To 2) As Variant
> >
> > Then put 1 to 22 in position 1 column 1 (using option base 1) and then
> > xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
> > relevant position and then include FieldInfo:=ColumnFormats in the parameter
> > list
> >
> > Then it should all work, or have I got myself confused?
> >
> > Regards,
> >
> >
> > "Dave Peterson" wrote:
> >
> > > When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
> > >
> > > If you rename your .csv to .txt (or almost anything else), then your macro will
> > > work ok.
> > >
> > >
> > >
> > > SB wrote:
> > > >
> > > > Hi,
> > > >
> > > > I am struggling to understand the FieldInfo parameter in the
> > > > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > > > problem dumping the whole file into a sheet but when I try to use the
> > > > FieldInfo parameter to specify which columns to ignore and which should be
> > > > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > > > a worked example or the like, or explain to me in laymans terms how to go
> > > > about it?
> > > >
> > > > Regards,
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Thanks Dave,

pointed me in the right direction. I assumed that the list of datatypes were
in numerical order and then when I read that xlSkipColumn was number 9 that
the number had to start from 0. All the 0's in that array were to ignore
columns.

Once again, thanks for your help.

Final code for anyone else that is interested is

Option Explicit
Sub importdata()
Dim myFileName
Dim ColumnsDesired
Dim DataTypeArray
Dim x
Dim ColumnArray(0 To 21, 1 To 2)

' open the file

myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If myFileName = False Then
MsgBox "Try Later"
Exit Sub
End If

' fill the column and data type information
ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22)
DataTypeArray = Array(1, 9, 3, 1, 1, 2, 1, 1, 1, 9, 9, 9, 1, 1, 1, 9, 1,
9, 9, 9, 9, 9)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x

' open the file
Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
Comma:=True, FieldInfo:=ColumnArray

End Sub


"Dave Peterson" wrote:

> You have a couple of problems in your code.
>
> First, the easy one:
> Dim ColumnArray(0 To 21, 0 To 3)
> should be:
> Dim ColumnArray(0 To 21, 1 to 2)
>
> And those columnArrays have to be valid--0 isn't a valid choice.
>
> You can use 1-10 (either as numbers or as xlconstants)
>
> XlColumnDataType can be one of these XlColumnDataType constants.
>
> (I think in earlier versions of the help, they actually gave the numbers instead
> of xl's constants):
>
> 1 xlGeneralFormat. General
> 2 xlTextFormat. Text
> 3 xlMDYFormat. MDY Date
> 4 xlDMYFormat. DMY Date
> 5 xlYMDFormat. YMD Date
> 6 xlMYDFormat. MYD Date
> 7 xlDYMFormat. DYM Date
> 8 xlYDMFormat. YDM Date
> 10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
> 9 xlSkipColumn. Skip Column
>
> I don't think I could guess what you want for each field:
>
> DataTypeArray _
> = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0, 9, 9, 9, 9, 9)
>
> but those 0's can't be there.
>
>
>
> SB wrote:
> >
> > ok,
> >
> > a little more understanding happening here, the list of parameters are
> > referred to by an index number and not a string. Still not quite there yet.
> > Here is my code. I have 22 columns in the text file (not CSV) and I have 4
> > different data types that I want to specify.
> >
> > Option Explicit
> > Sub importdata()
> > Dim myFileName
> > Dim ColumnsDesired
> > Dim DataTypeArray
> > Dim x
> > Dim ColumnArray(0 To 21, 0 To 3)
> >
> > myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> > If myFileName = False Then
> > MsgBox "Try Later"
> > Exit Sub
> > End If
> >
> > ' fill the column and data type information
> > ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> > 15, 16, 17, 18, 19, 20, 21, 22)
> > DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
> > 9, 9, 9, 9, 9)
> >
> > ' populate the array for fieldinfo
> > For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> > ColumnArray(x, 1) = ColumnsDesired(x)
> > ColumnArray(x, 2) = DataTypeArray(x)
> > Next x
> >
> > ' open the file
> > Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> > Comma:=True, FieldInfo:=ColumnArray
> >
> > End Sub
> >
> > "SB" wrote:
> >
> > > I love continuity!
> > >
> > > Thanks Dave. I have 22 columns to bring in, now my understanding is that if
> > > I create an array like
> > >
> > > Dim ColumnFormats(1 To 22, 1 To 2) As Variant
> > >
> > > Then put 1 to 22 in position 1 column 1 (using option base 1) and then
> > > xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
> > > relevant position and then include FieldInfo:=ColumnFormats in the parameter
> > > list
> > >
> > > Then it should all work, or have I got myself confused?
> > >
> > > Regards,
> > >
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
> > > >
> > > > If you rename your .csv to .txt (or almost anything else), then your macro will
> > > > work ok.
> > > >
> > > >
> > > >
> > > > SB wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I am struggling to understand the FieldInfo parameter in the
> > > > > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > > > > problem dumping the whole file into a sheet but when I try to use the
> > > > > FieldInfo parameter to specify which columns to ignore and which should be
> > > > > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > > > > a worked example or the like, or explain to me in laymans terms how to go
> > > > > about it?
> > > > >
> > > > > Regards,
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
>
> --
>
> Dave Peterson
>

All the 0's were to bring that field in as General, right--not skip the column?

(Your code does things differently from what your text reads -- but I bet you're
happier with the code <vbg>.)



SB wrote:
>
> Thanks Dave,
>
> pointed me in the right direction. I assumed that the list of datatypes were
> in numerical order and then when I read that xlSkipColumn was number 9 that
> the number had to start from 0. All the 0's in that array were to ignore
> columns.
>
> Once again, thanks for your help.
>
> Final code for anyone else that is interested is
>
> Option Explicit
> Sub importdata()
> Dim myFileName
> Dim ColumnsDesired
> Dim DataTypeArray
> Dim x
> Dim ColumnArray(0 To 21, 1 To 2)
>
> ' open the file
>
> myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> If myFileName = False Then
> MsgBox "Try Later"
> Exit Sub
> End If
>
> ' fill the column and data type information
> ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> 15, 16, 17, 18, 19, 20, 21, 22)
> DataTypeArray = Array(1, 9, 3, 1, 1, 2, 1, 1, 1, 9, 9, 9, 1, 1, 1, 9, 1,
> 9, 9, 9, 9, 9)
>
> ' populate the array for fieldinfo
> For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> ColumnArray(x, 1) = ColumnsDesired(x)
> ColumnArray(x, 2) = DataTypeArray(x)
> Next x
>
> ' open the file
> Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> Comma:=True, FieldInfo:=ColumnArray
>
> End Sub
>
> "Dave Peterson" wrote:
>
> > You have a couple of problems in your code.
> >
> > First, the easy one:
> > Dim ColumnArray(0 To 21, 0 To 3)
> > should be:
> > Dim ColumnArray(0 To 21, 1 to 2)
> >
> > And those columnArrays have to be valid--0 isn't a valid choice.
> >
> > You can use 1-10 (either as numbers or as xlconstants)
> >
> > XlColumnDataType can be one of these XlColumnDataType constants.
> >
> > (I think in earlier versions of the help, they actually gave the numbers instead
> > of xl's constants):
> >
> > 1 xlGeneralFormat. General
> > 2 xlTextFormat. Text
> > 3 xlMDYFormat. MDY Date
> > 4 xlDMYFormat. DMY Date
> > 5 xlYMDFormat. YMD Date
> > 6 xlMYDFormat. MYD Date
> > 7 xlDYMFormat. DYM Date
> > 8 xlYDMFormat. YDM Date
> > 10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
> > 9 xlSkipColumn. Skip Column
> >
> > I don't think I could guess what you want for each field:
> >
> > DataTypeArray _
> > = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0, 9, 9, 9, 9, 9)
> >
> > but those 0's can't be there.
> >
> >
> >
> > SB wrote:
> > >
> > > ok,
> > >
> > > a little more understanding happening here, the list of parameters are
> > > referred to by an index number and not a string. Still not quite there yet.
> > > Here is my code. I have 22 columns in the text file (not CSV) and I have 4
> > > different data types that I want to specify.
> > >
> > > Option Explicit
> > > Sub importdata()
> > > Dim myFileName
> > > Dim ColumnsDesired
> > > Dim DataTypeArray
> > > Dim x
> > > Dim ColumnArray(0 To 21, 0 To 3)
> > >
> > > myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> > > If myFileName = False Then
> > > MsgBox "Try Later"
> > > Exit Sub
> > > End If
> > >
> > > ' fill the column and data type information
> > > ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> > > 15, 16, 17, 18, 19, 20, 21, 22)
> > > DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
> > > 9, 9, 9, 9, 9)
> > >
> > > ' populate the array for fieldinfo
> > > For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> > > ColumnArray(x, 1) = ColumnsDesired(x)
> > > ColumnArray(x, 2) = DataTypeArray(x)
> > > Next x
> > >
> > > ' open the file
> > > Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> > > Comma:=True, FieldInfo:=ColumnArray
> > >
> > > End Sub
> > >
> > > "SB" wrote:
> > >
> > > > I love continuity!
> > > >
> > > > Thanks Dave. I have 22 columns to bring in, now my understanding is that if
> > > > I create an array like
> > > >
> > > > Dim ColumnFormats(1 To 22, 1 To 2) As Variant
> > > >
> > > > Then put 1 to 22 in position 1 column 1 (using option base 1) and then
> > > > xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
> > > > relevant position and then include FieldInfo:=ColumnFormats in the parameter
> > > > list
> > > >
> > > > Then it should all work, or have I got myself confused?
> > > >
> > > > Regards,
> > > >
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
> > > > >
> > > > > If you rename your .csv to .txt (or almost anything else), then your macro will
> > > > > work ok.
> > > > >
> > > > >
> > > > >
> > > > > SB wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I am struggling to understand the FieldInfo parameter in the
> > > > > > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > > > > > problem dumping the whole file into a sheet but when I try to use the
> > > > > > FieldInfo parameter to specify which columns to ignore and which should be
> > > > > > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > > > > > a worked example or the like, or explain to me in laymans terms how to go
> > > > > > about it?
> > > > > >
> > > > > > Regards,
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Yes I know what I meant Gotta stop trying to do two things at once!

For anyone else reading this the 0's were to bring the field in as a
general, the 9's skip the field.

"Dave Peterson" wrote:

> All the 0's were to bring that field in as General, right--not skip the column?
>
> (Your code does things differently from what your text reads -- but I bet you're
> happier with the code <vbg>.)
>
>
>
> SB wrote:
> >
> > Thanks Dave,
> >
> > pointed me in the right direction. I assumed that the list of datatypes were
> > in numerical order and then when I read that xlSkipColumn was number 9 that
> > the number had to start from 0. All the 0's in that array were to ignore
> > columns.
> >
> > Once again, thanks for your help.
> >
> > Final code for anyone else that is interested is
> >
> > Option Explicit
> > Sub importdata()
> > Dim myFileName
> > Dim ColumnsDesired
> > Dim DataTypeArray
> > Dim x
> > Dim ColumnArray(0 To 21, 1 To 2)
> >
> > ' open the file
> >
> > myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> > If myFileName = False Then
> > MsgBox "Try Later"
> > Exit Sub
> > End If
> >
> > ' fill the column and data type information
> > ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> > 15, 16, 17, 18, 19, 20, 21, 22)
> > DataTypeArray = Array(1, 9, 3, 1, 1, 2, 1, 1, 1, 9, 9, 9, 1, 1, 1, 9, 1,
> > 9, 9, 9, 9, 9)
> >
> > ' populate the array for fieldinfo
> > For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> > ColumnArray(x, 1) = ColumnsDesired(x)
> > ColumnArray(x, 2) = DataTypeArray(x)
> > Next x
> >
> > ' open the file
> > Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> > Comma:=True, FieldInfo:=ColumnArray
> >
> > End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > You have a couple of problems in your code.
> > >
> > > First, the easy one:
> > > Dim ColumnArray(0 To 21, 0 To 3)
> > > should be:
> > > Dim ColumnArray(0 To 21, 1 to 2)
> > >
> > > And those columnArrays have to be valid--0 isn't a valid choice.
> > >
> > > You can use 1-10 (either as numbers or as xlconstants)
> > >
> > > XlColumnDataType can be one of these XlColumnDataType constants.
> > >
> > > (I think in earlier versions of the help, they actually gave the numbers instead
> > > of xl's constants):
> > >
> > > 1 xlGeneralFormat. General
> > > 2 xlTextFormat. Text
> > > 3 xlMDYFormat. MDY Date
> > > 4 xlDMYFormat. DMY Date
> > > 5 xlYMDFormat. YMD Date
> > > 6 xlMYDFormat. MYD Date
> > > 7 xlDYMFormat. DYM Date
> > > 8 xlYDMFormat. YDM Date
> > > 10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
> > > 9 xlSkipColumn. Skip Column
> > >
> > > I don't think I could guess what you want for each field:
> > >
> > > DataTypeArray _
> > > = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0, 9, 9, 9, 9, 9)
> > >
> > > but those 0's can't be there.
> > >
> > >
> > >
> > > SB wrote:
> > > >
> > > > ok,
> > > >
> > > > a little more understanding happening here, the list of parameters are
> > > > referred to by an index number and not a string. Still not quite there yet.
> > > > Here is my code. I have 22 columns in the text file (not CSV) and I have 4
> > > > different data types that I want to specify.
> > > >
> > > > Option Explicit
> > > > Sub importdata()
> > > > Dim myFileName
> > > > Dim ColumnsDesired
> > > > Dim DataTypeArray
> > > > Dim x
> > > > Dim ColumnArray(0 To 21, 0 To 3)
> > > >
> > > > myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
> > > > If myFileName = False Then
> > > > MsgBox "Try Later"
> > > > Exit Sub
> > > > End If
> > > >
> > > > ' fill the column and data type information
> > > > ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
> > > > 15, 16, 17, 18, 19, 20, 21, 22)
> > > > DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
> > > > 9, 9, 9, 9, 9)
> > > >
> > > > ' populate the array for fieldinfo
> > > > For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
> > > > ColumnArray(x, 1) = ColumnsDesired(x)
> > > > ColumnArray(x, 2) = DataTypeArray(x)
> > > > Next x
> > > >
> > > > ' open the file
> > > > Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
> > > > Comma:=True, FieldInfo:=ColumnArray
> > > >
> > > > End Sub
> > > >
> > > > "SB" wrote:
> > > >
> > > > > I love continuity!
> > > > >
> > > > > Thanks Dave. I have 22 columns to bring in, now my understanding is that if
> > > > > I create an array like
> > > > >
> > > > > Dim ColumnFormats(1 To 22, 1 To 2) As Variant
> > > > >
> > > > > Then put 1 to 22 in position 1 column 1 (using option base 1) and then
> > > > > xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
> > > > > relevant position and then include FieldInfo:=ColumnFormats in the parameter
> > > > > list
> > > > >
> > > > > Then it should all work, or have I got myself confused?
> > > > >
> > > > > Regards,
> > > > >
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.
> > > > > >
> > > > > > If you rename your .csv to .txt (or almost anything else), then your macro will
> > > > > > work ok.
> > > > > >
> > > > > >
> > > > > >
> > > > > > SB wrote:
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I am struggling to understand the FieldInfo parameter in the
> > > > > > > Workbooks.OpenText method. I have a csv file that I am opening, I have no
> > > > > > > problem dumping the whole file into a sheet but when I try to use the
> > > > > > > FieldInfo parameter to specify which columns to ignore and which should be
> > > > > > > treated as text (to preserve a leading 0) I get lost. Can anyone point me to
> > > > > > > a worked example or the like, or explain to me in laymans terms how to go
> > > > > > > about it?
> > > > > > >
> > > > > > > Regards,
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>