Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to handle error in for.. next statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to handle error in for.. next statement
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 "M Armaghan Khan" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to handle error in for.. next statement
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. -- Thanks, Shane Devenshire "M Armaghan Khan" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to handle error in for.. next statement
Thanks guys of your replies it really help me a lot Thank you very much both
of you. Regards, M Armaghan Khan "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "M Armaghan Khan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
handle error in IF ELSEIF statement | Excel Worksheet Functions | |||
How to handle automation error? | Excel Programming | |||
Error Handle | Excel Programming | |||
How to handle error 8007000e Memory Error | Excel Programming |