Import and delimit dynamic files as text

  • Hi - I need help opening a file, delimiting by tab, and importing the data as text (not general). I have code that I'm using now that works fine, but now I need to expand the code to a dynamic number of columns.

    The code I'm using now only imports some of the data as text, but I can go ahead an apply Text to all columns to make it simpler. As you can see below my Array applies to a specific amount of columns (29 total).

    Workbooks.OpenText filename:=Path & "Desktop\I6 FILES\Output\" & host1 & "\" & file1 & ".001" _
            , DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2), _
            Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
            , 1), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 1), Array(20, 1), Array(21, 2), _
            Array(22, 2), Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array( _
            28, 2), Array(29, 2)), TrailingMinusNumbers:=True

    I need to modify this code to expand to any number of columns. I will always have a minimum of 29 columns, but I could have any maximum number. I know that I can go in and hard-code the array numbers that I think will cover my total possible columns, but I'm sure there's a way to do it dynamically...Can anyone help? Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!