Pulling data from a text file, splitting by fixed column width

I need to pull a large table of data from a notepad .txt file. Presently, I'm trying to pull it into a new work book, apply the fixed column split, and then copy and paste it over to my work book. Unfortunately this is not working and I've tried so much code now my own macro is just looking convoluted and painful. Any help would be highly appreciated.

I was hoping that the text to column conversion with the array setup would split this appropriately but it's not splitting the data at all. I'm not sure why. If anyone knows, am I just formatting the numbers wrong? I thought the first number was the character start positions and the second was the number of characters to include.

Sub Pull_FlashReport_Fiscal()

Dim master As Workbook
Dim slave As Workbook

Set master = ActiveWorkbook

Application.DisplayAlerts = False

'clear old data

Sheet4.Cells.ClearContents

'pull data from notepad
Workbooks.OpenText Filename:="C:\Documents and Settings\User\Desktop\Excel Project\Sales Sheet\FLASH_REPORT_FOR_GASA_-_FISCAL.4292013.txt", _
    Origin:=-535, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False

Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(1, 5), Array(5, 33), Array(38, 12), Array(50, 10), Array(60, 15), Array(75, 13), Array(88, 12), Array(100, 11), Array(111, 8), Array(119, 7), Array(126, 7)), _
    TrailingMinusNumbers:=True

Set slave = ActiveWorkbook

ActiveSheet.Cells.Select
Selection.Copy

master.Activate
Sheet4.Activate
Sheet4.Range("A1").Activate
Sheet4.Paste

slave.Close

Application.DisplayAlerts = True

End Sub

You're close. The first parameter is the character position to the LEFT of the position you want next. The second parameter is data type. 1 is text.

So, to get the first character set of text use (0, 1). If the first set was 5 characters, then the NEXT group will indicate that with (5, 1) which means start with the character after the 5th and use data type 1. Etc.


So maybe:

Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(32, 1), Array(37, 1), Array(49, 1), Array(59, 1), Array(74, 1), Array(87, 1), Array(99, 1), Array(110, 1), Array(118, 1), Array(125, 1)), TrailingMinusNumbers:=True

Thanks for the feedback.

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.