VBA Workbooks.OpenText FieldInfo as a variable -> Import text into Excel question

[antaeusguy]

hi

I've several text file which I would want to import into Excel and each text file has different columns

I noted I could use Workbooks.OpenText and the property to change which columns to import and it's import format is FieldInfo.

I declared a variable called arrtext and this will change from file to file. I intended to use arrtext as the FieldInfo.

I tried the code below but is does not work.

Does anyone knows how to declare the variable for FieldInfo in .OpenText? Thanks a lot!

Sub TextImportTest()

Dim arrtext As String


    arrtext = "Array(Array(1, 1), Array(2, 2), Array(3, 9))"




    Workbooks.OpenText Filename:="C:\Users\john.doe\Desktop\test\test.txt", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=arrtext, TrailingMinusNumbers:=True


End Sub

 

[patel45]

Dim arrtext()
arrtext = Array(Array(1, 1), Array(2, 2), Array(3, 9))

with FieldInfo you can not select columns to import, but columns to analyze

 

[Jon von der Heyden]

As above ^ (Patel has correctly shown the syntax)

with FieldInfo you can not select columns to import, but columns to analyze

Just to clarify that the 2nd element of each array refers to xlColumnDataType constant; one of which is xlSkipColumn, or #9...

 

[patel45]

see Workbooks.OpenText Method (Excel), SkipColumn # 9 Column is not parsed

.

<tbody>
</tbody>

 

[Jon von der Heyden]

I think I lost the plot on this one. So #9 column is still included in the open file, only it is not parsed... At least that what appears to happen. So if you don't want any particular column one has to either delete it or change to an import method instead.

 

[antaeusguy]

Thanks guy for the help