Error handling

[kknb4591]

Hi,

Have searche high and low for an answer to this one, I think it should be pretty simple.

I have a piece of code which opens two files one-by-one and reformats them before closing them:

---First bit
Workbooks.Open Filename:= _
Range("D6")
Rows("1:18").Select
Range("A18").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireRow.AutoFit
Selection.RowHeight = 12
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"H:\file1.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

---Second bit
Workbooks.Open Filename:= _
Range("D11")
Cells.Select
Selection.RowHeight = 12
Range("A1").Select
Cells.Find(What:="Global Treasury Services", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-4).Resize(5).ClearContents
Range("A1").Select
ChDir "H:\"
ActiveWorkbook.SaveAs Filename:= _
"H:\file2.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub

But what I need to do is if there is no file for the "first bit" to reformat then to just skip on to the "second bit" and complete that.

I'd be grateful for any help with this.

 

[VoG]

Not ideal but

On Error Goto SecondBit
Workbooks.Open Filename:= _
Range("D6")
Rows("1:18").Select
Range("A18").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireRow.AutoFit
Selection.RowHeight = 12
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"H:\file1.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Secondbit:
Workbooks.Open Filename:= _
Range("D11")
Cells.Select
Selection.RowHeight = 12
Range("A1").Select
Cells.Find(What:="Global Treasury Services", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-4).Resize(5).ClearContents
Range("A1").Select
ChDir "H:\"
ActiveWorkbook.SaveAs Filename:= _
"H:\file2.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub

 

[Andrew Poulsom]

There's a FileExists function here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/