Messages at end of file

[leskoby]

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

[mudraker]

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

[leskoby]

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

[Tim Williams]

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" <leskoby.1uk72d_1125374716.726@excelforum-nospam.com> wrote
in message news:leskoby.1uk72d_1125374716.726@excelforum-nospam.com...
>
> 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
>

[Bob Phillips]

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" <leskoby.1uk72h_1125374718.4853@excelforum-nospam.com> wrote in
message news:leskoby.1uk72h_1125374718.4853@excelforum-nospam.com...
>
> 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
>

[leskoby]

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?

[mudraker]

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 in the last row that you use.