Re: Workbooks.OpenText method, FieldInfo parameter

Tech-Archive recommends: Speed Up your PC by fixing your registry

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 *** 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

.

Quantcast