Error handling? or possible overwrite file code

[keith]

I have some code here that checks to see if a path exists, creates the path/dir if it doesn't exist, and then saves the active workbook in the dir. This works great, once. Problem comes in if the file already exists then Excel prompts the user asking if they want to overwrite. If the user says no, my VB errors out. Is there anyway to stop my VB from puking? Or my 2nd choice would be to stop excel from prompting and just automatically overwrite the file? Here is my current code:

Private Sub savebutton_Click()
Path = "c:worksheets"
If PathExists(Path) = False Then MkDir "c:worksheets"

ChDir "C:worksheets"
ActiveWorkbook.saveas Filename:= _
"C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

Function PathExists(pname) As Boolean
' Returns TRUE if the path exists
Dim x As String
On Error Resume Next
x = GetAttr(pname) And 0
If Err = 0 Then PathExists = True _
Else: PathExists = False

End Function

 

[Ivan F Moala]

Amend code as follows;

On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
"C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Err.Clear


Unless you want to see the error and respond to it then;

On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
"C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

If Err Then MsgBox Err.Number & ":=" & Err.Description, _
vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext



HTH

Ivan

 

[Dave Hawley]

Hi keith


Ivan the terrible has already answered you question, the only poit I would add is, you should use:

On Error GoTo 0 to reset error trapping.

Also if you decide to go with your send choice:

Application.DisplayAlerts=False
'You code
Application.DisplayAlerts=True

This will force Excel to go with the fault answer for any warnings.

 

[Ivan F Moala]

On 2002-03-29 16:35, Dave Hawley wrote:
Hi keith


Ivan the terrible has already answered you question, the only poit I would add is, you should use:

On Error GoTo 0 to reset error trapping.

Also if you decide to go with your send choice:

Application.DisplayAlerts=False
'You code
Application.DisplayAlerts=True

This will force Excel to go with the fault answer for any warnings.

Thanks Dave .....As Dave has pointed out
reset the Error call...good practice even
if you don't need it.

cheers Dave

Ivan

 

[keith]

Thanks guys. It worked great.

I had seen the "On Error Resume Next" code in other posts of various types and thought it might be the answer, but I wasn't sure exactly what it did (or how to turn it back on/reset it).

I was scared of it because the 'resume next' implies it might skip over the current line of code becaused it got an error. And I didn't want to risk the file not getting saved.

Thanks to you guys I think I now understand how it works..

Keith

 

[daleyman]

one extra tip i find useful,

it's often worth isolating an operation like this where you use ON ERROR to trap likely errors, so that it doesn't RESUME NEXT on other items in a larger procedure.

You can put just these three or four lines of code in its own subroutine with the on error command.

 

[keith]

gotcha - thanks

 

[Jack in the UK]

Dave = Ivan

Guys a big tip o the hat

I never knew that or would i.. ermm sounds like standard from now on regardless... UNLESS i need next regardless.

Good men!
you friend Jack