My macro works successfully creating my .xls file thanks to this
community's help. However it reports, 2 rows after the end of the last
one "21225 rows selected". Another 3 lines further down, it reports
"PL/SQL procedure successfully completed".

Is there any way of switching off these messages as the Autofill
function sees the report line as the last line and fills some cells
with rubbish?
Thanks


--
leskoby
------------------------------------------------------------------------
leskoby's Profile: http://www.excelforum.com/member.php...o&userid=26616
View this thread: http://www.excelforum.com/showthread...hreadid=400333

Can you post your macro code so that we can try and see what part of the
code is inserting those


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=400333

Here's the code. I hope you can find where the messages are activated.
Thanks

Sub ParsePO()
'
' ParsePO Macro
' Macro recorded 23/08/2005 by msaa0910
'
ChDrive "C"
ChDir "\Data\Finance\"
aFile = InputBox("Text File Name (Do not include .TXT extension)")
afilename = aFile & ".txt"
'
Workbooks.OpenText Filename:=afilename, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
Array(Array(0, 1), Array(4, 9), Array(5, 1), Array(14, 9),
Array(15, 1), Array(65, 9), _
Array(66, 1), Array(146, 9), Array(147, 1), Array(159, 9),
Array(160, 1), Array(168, 9), _
Array(169, 1), Array(229, 9), Array(230, 1), Array(232, 9),
Array(233, 1), Array(237, 9), _
Array(238, 1), Array(243, 9), Array(244, 1), Array(247, 9),
Array(248, 1), Array(253, 9), _
Array(254, 1), Array(256, 9), Array(257, 1), Array(262, 9),
Array(263, 1), Array(282, 9), _
Array(283, 1), Array(295, 9), Array(296, 1), Array(316, 9),
Array(317, 1), Array(333, 9), _
Array(334, 1), Array(346, 9), Array(347, 1), Array(359, 9),
Array(360, 1))
Workbooks.Open Filename:="C:\Data\Finance\RRHACS PO & AP
Lookups.xls"

'Create Heading lines
Sheets("Heading Line").Select
Selection.Copy
Windows(afilename).Activate
ActiveSheet.Paste
Rows("2:4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

'Start Conditional Delete
Dim i As Long
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 5400 And Cells(i, "A").Value 5118 Then
Rows(i).Delete
End If
Next i

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then
Rows(i).Delete
End If
Next i
'End Conditional Delete

'Process formulas
ActiveWindow.SmallScroll ToRight:=16
Range("V2").Select
Windows("RRHACS PO & AP Lookups.xls").Activate
Sheets("Formulas").Select
Range("A2:K2").Select
Selection.Copy
Windows(afilename).Activate
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Find Last line and Auto Fill
Dim GetBottomRow As Long
GetBottomRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range("v2:af2").AutoFill Destination:= _
Range("v2:af" & GetBottomRow), Type:=xlFillDefault

'Save New File
ChDir "C:\Data\Finance"
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\Finance\All POs.xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWindow.Close
ActiveWindow.Close

End Sub


--
leskoby
------------------------------------------------------------------------
leskoby's Profile: http://www.excelforum.com/member.php...o&userid=26616
View this thread: http://www.excelforum.com/showthread...hreadid=400333

Your inital file is generated from Oracle ?
Try having whoever is responsible for generating the output revise
their code to suppress the messages ("set feedback off").

Or just search for and delete the messages after first opening the
file and before running the rest of your macro.

Tim.


"leskoby" wrote
in message ...

My macro works successfully creating my .xls file thanks to this
community's help. However it reports, 2 rows after the end of the
last
one "21225 rows selected". Another 3 lines further down, it reports
"PL/SQL procedure successfully completed".

Is there any way of switching off these messages as the Autofill
function sees the report line as the last line and fills some cells
with rubbish?
Thanks


--
leskoby
------------------------------------------------------------------------
leskoby's Profile:
http://www.excelforum.com/member.php...o&userid=26616
View this thread:
http://www.excelforum.com/showthread...hreadid=400333

Is that text at the end of the text file being read? If so, is it a
consistent block not required, say 5 lines?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"leskoby" wrote in
message ...

Here's the code. I hope you can find where the messages are activated.
Thanks

Sub ParsePO()
'
' ParsePO Macro
' Macro recorded 23/08/2005 by msaa0910
'
ChDrive "C"
ChDir "\Data\Finance\"
aFile = InputBox("Text File Name (Do not include .TXT extension)")
afilename = aFile & ".txt"
'
Workbooks.OpenText Filename:=afilename, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
Array(Array(0, 1), Array(4, 9), Array(5, 1), Array(14, 9),
Array(15, 1), Array(65, 9), _
Array(66, 1), Array(146, 9), Array(147, 1), Array(159, 9),
Array(160, 1), Array(168, 9), _
Array(169, 1), Array(229, 9), Array(230, 1), Array(232, 9),
Array(233, 1), Array(237, 9), _
Array(238, 1), Array(243, 9), Array(244, 1), Array(247, 9),
Array(248, 1), Array(253, 9), _
Array(254, 1), Array(256, 9), Array(257, 1), Array(262, 9),
Array(263, 1), Array(282, 9), _
Array(283, 1), Array(295, 9), Array(296, 1), Array(316, 9),
Array(317, 1), Array(333, 9), _
Array(334, 1), Array(346, 9), Array(347, 1), Array(359, 9),
Array(360, 1))
Workbooks.Open Filename:="C:\Data\Finance\RRHACS PO & AP
Lookups.xls"

'Create Heading lines
Sheets("Heading Line").Select
Selection.Copy
Windows(afilename).Activate
ActiveSheet.Paste
Rows("2:4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

'Start Conditional Delete
Dim i As Long
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 5400 And Cells(i, "A").Value 5118 Then
Rows(i).Delete
End If
Next i

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 6500 And Cells(i, "A").Value 5599 Then
Rows(i).Delete
End If
Next i
'End Conditional Delete

'Process formulas
ActiveWindow.SmallScroll ToRight:=16
Range("V2").Select
Windows("RRHACS PO & AP Lookups.xls").Activate
Sheets("Formulas").Select
Range("A2:K2").Select
Selection.Copy
Windows(afilename).Activate
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Find Last line and Auto Fill
Dim GetBottomRow As Long
GetBottomRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range("v2:af2").AutoFill Destination:= _
Range("v2:af" & GetBottomRow), Type:=xlFillDefault

'Save New File
ChDir "C:\Data\Finance"
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\Finance\All POs.xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWindow.Close
ActiveWindow.Close

End Sub


--
leskoby
------------------------------------------------------------------------
leskoby's Profile:
http://www.excelforum.com/member.php...o&userid=26616
View this thread: http://www.excelforum.com/showthread...hreadid=400333

Yes, the data is extracted from Oracle as a txt file and the text is at
the end of the extract. I shall ask the Oracle maintenance staff to
switch off the meesage but they may not give it ithe priority I would
like.

Is it possible to look at a specific column to check for end of file
so that I can pick an empty one?


--
leskoby
------------------------------------------------------------------------
leskoby's Profile: http://www.excelforum.com/member.php...o&userid=26616
View this thread: http://www.excelforum.com/showthread...hreadid=400333

change

GetBottomRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

to

GetBottomRow = Cells(Rows.Count, "a").End(xlUp).Row

change the "a" column reference to a column that always has an entry i
the last row that you use

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=40033