how to handle error in for.. next statement

[M Armaghan Khan]

i am having problem in hadling error in for next statement i have make a
program
to convert Excel 2007 sheets in Excel-2003. i have made a array and put all
the
file names in it please see below if some files are missing and programs
wants to
opern it error occurs i have use the on error goto Lasteline to skip to the
next counter it works fine when program doesn't find first file but if
another file is missing the program crashes i don't know why on error goto
lasteline not working on second attempt
Please help

For i=1 to 129


On Error Goto Lastline
Workbooks.Open Filename:="" & aPath & "\" & saleId(a) & "-Deduction.xls"

ActiveWorkbook.SaveAs Filename:= _
"" & bPath & "\" & saleId(a) & "-Deduction-" & newName & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Lastline:
a = a + 1
Next i
End Sub

 

[Joel]

Use the dir statement to test if file exists before opening

For i=1 to 129
FName = dir("" & aPath & "\" & saleId(a) & "-Deduction.xls")
if FName <> "" then
Workbooks.Open Filename:="" & aPath & "\" & saleId(a) &
"-Deduction.xls"

ActiveWorkbook.SaveAs Filename:= _
"" & bPath & "\" & saleId(a) & "-Deduction-" & newName & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
end if

a = a + 1
Next i
End Sub

 

[ShaneDevenshire]

Hi,

If you use an on error goto statement something like this might work better

For i = 1 To 129


On Error Resume Next
Workbooks.Open Filename:="" & aPath & "\" & saleId(a) & "-Deduction.xls"
If Err = 0 Then
ActiveWorkbook.SaveAs Filename:= _
"" & bPath & "\" & saleId(a) & "-Deduction-" & newName & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Else
Err = 0
End If

a = a + 1
Next i

However, I haven't tested to see if you can trap this error this way.

 

[M Armaghan Khan]

Thanks guys of your replies it really help me a lot Thank you very much both
of you.

Regards,
M Armaghan Khan