I have been tasked to analyze data from several unix reports (downloade
to C:\). I just recently had my first exposure to VBA but am trying m
best. I wish to import these reports into Excel. I would need t
"clean up" (remove empty rows,sort the data, and remove certai
unwanted text such as mulitple headers). when all is done, i woul
need to be left with a worksheet for each report and a consolidate
master with combined information.
I already have the code for deleting emptly rows. Attached is
sample(4 pages of many) of the downloaded report. Any help would b
greatly appreciated

Attachment filename: openpo.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=55993
--
Message posted from http://www.ExcelForum.com

This gets you down to the details:

You can add back your desired heading and use Excel to build any subtotals
you need.

Sub AAOpenReport()
' Change the next to lines to refer to your report file.
ChDir "C:\Data6"
Workbooks.OpenText Filename:="C:\Data6\openpo.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(11, 1), Array(16, 1), _
Array(33, 1), Array(44, 1), _
Array(65, 1), Array(70, 1), _
Array(81, 1), Array(91, 1), _
Array(100, 1), Array(110, 1), _
Array(122, 1))
Set rng = Columns(1).SpecialCells(xlBlanks)
rng.EntireRow.Delete
With Columns(1)
.Replace What:="RTROPNPO", Replacement:="=Na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="R*", Replacement:="=Na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

.Replace What:="DIST*", Replacement:="=Na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="---*", Replacement:="=Na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="PO", Replacement:="=Na()", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

End With
Columns(2).Replace What:="At", _
Replacement:="=Na()", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
Columns("A:B").SpecialCells(xlFormulas, _
xlErrors).EntireRow.Delete
End Sub

--
Regards,
Tom Ogilvy

"acarril " wrote in message
...
I have been tasked to analyze data from several unix reports (downloaded
to C:\). I just recently had my first exposure to VBA but am trying my
best. I wish to import these reports into Excel. I would need to
"clean up" (remove empty rows,sort the data, and remove certain
unwanted text such as mulitple headers). when all is done, i would
need to be left with a worksheet for each report and a consolidated
master with combined information.
I already have the code for deleting emptly rows. Attached is a
sample(4 pages of many) of the downloaded report. Any help would be
greatly appreciated.

Attachment filename: openpo.txt
Download attachment:
http://www.excelforum.com/attachment.php?postid=559933
---
Message posted from http://www.ExcelForum.com/

THANKS!!! I tried it and it worked

--
Message posted from http://www.ExcelForum.com