VBA problem importing and auditing Unix reports |
|
Author |
Message |
Dastard
|
Posted: Fri May 28 11:27:06 CDT 2004 |
Top |
Excel Programming >> VBA problem importing and auditing Unix reports
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.hide-link.com/
--
Message posted from http://www.hide-link.com/
Excel347
|
|
|
|
|
Tom
|
Posted: Fri May 28 11:27:06 CDT 2004 |
Top |
Excel Programming >> VBA problem importing and auditing Unix reports
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
> 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/
>
|
|
|
|
|
acarril
|
Posted: Fri May 28 18:07:23 CDT 2004 |
Top |
Excel Programming >> VBA problem importing and auditing Unix reports
THANKS!!! I tried it and it worked
--
Message posted from http://www.ExcelForum.com
|
|
|
|
|
|
|