Import Text File into Excel - Build Columns based on position 2

[StephDN]

Hi!

I'm trying to import a rather complex text file into an Excel spreadsheet (simple enough). I can use the import wizard and set up the columns where I need them (not based on tabs, commas, etc.), but I need it be automated (no wizard).

I need to select text in the text file based on row positioning and import the text into the columns. For example: I need the column A to contain the first 7 characters of the the text file, Column B to contain the next 10 characters, Column C to contain next 2 characters, and so on. I can't use the regular delimited method because each line in the text file is different (and not based on a character). I just need to be able to select the text from the text file based on character positioning, and import that into each consecutive column - and then do this for each line in the text file.

I've searched and searched for examples, but can only find examples that seperate the text file by commas, spaces, or colons. PLEASE HELP!!

Thanks in advance!

 

[Zathras]

I just recorded the macro below to do what you outlined. It creates a new workbook from the text data. The text file I used looks like this:

aaaaaaa0123456789xxyyyyyzzzzz
bbbbbbb0123456789xxyyyyyzzzzz
ccccccc0123456789xxyyyyyzzzzz
ddddddd0123456789xxyyyyyzzzzz

Here is the macro:

========================================================

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/31/02 by Zathras
'

'
    ChDir "C:\"
    Workbooks.OpenText FileName:="C:\mydata.txt", Origin:=xlWindows, StartRow _
        :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, 1), Array( _
        17, 1), Array(19, 1), Array(24, 1))
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

======================================================

Once you have the file in a workbook, you should be able to do anything you need with it, e.g. copy rows to the workbook/sheet where you need it then close the workbook.

If you record your own macro with the real data, most of the work should be done for you.

 

[StephDN]

THANK YOU SOOOOO MUCH!!

It works beautifully!! Happy New Year!

 

[RobBroekhuis]

StephDN, sounds like Zathras really helped you out - the way to thank people around here is by awarding them a star for a helpful post. Seems like Zathras deserves one!
Rob