I have the following macro run in a template file and works well without the
error handling:

Sub Save()
' Macro recorded 02/10/2007 by me

Dim MyJobNo As String
Dim myFileName As String
Dim myDir As String
Dim myBUDir As String
Dim myBUName As String
On Error GoTo ErrorHandler

MyJobNo = Sheets("Summary").Range("A8").Formula = "left($a$7,4)"
myFileName = "CAPEXSummary-"
myDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji\CAPEX\"
'enter correct path name
ActiveWorkbook.SaveAs Filename:= _
myDir & myFileName & MyJobNo

myBUDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job
Analysis\Seraji\CAPEX\Backup\"
myBUName = "Backup-CAPEXSummary-"
ActiveWorkbook.SaveCopyAs Filename:= _
myBUDir & myBUName & MyJobNo
End Sub

When the file already exists the message "File already exists...etc" is fine.

If users have mistakenly left the previously saved file open and restart the
Template file the file saves as normal but MyJobNo changes to "FALSE" and the
user is unaware a new file wasn't saved.

Can I put an error trap in that warns the user that the file is open and
asks if they would like to close it before saving?

I've tried the following addition but I don't know how to identify the open
file in order to close it automatically (or even if it's a good idea):

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 55 ' "File already open" error.
MsgBox msg = "The file you are trying to save is already open" &
vbNewLine & _
" " & vbNewLine & _
"Select OK to have the file close and resave"
Close THE OPEN FILE 'Close open file.
Case Else
' Handle other situations here...
End Select
Resume ' Resume execution at same line
' that caused the error.

My applogies if this is long winded and difficult to read.

--
Jim

I agree you can certainly write a error handling routine.
There is one more way you can achieve this...write a function which checks
if the file already exists and if yes, deletes it. Write your error handling
inside this function such as if the file is open can not be deleted the
function returns FALSE, else the function deletes the file and returns TRUE.
Now you should have this functino call with IF statement before your SAVE
statement.

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Jim G" wrote:

I have the following macro run in a template file and works well without the
error handling:

Sub Save()
' Macro recorded 02/10/2007 by me

Dim MyJobNo As String
Dim myFileName As String
Dim myDir As String
Dim myBUDir As String
Dim myBUName As String
On Error GoTo ErrorHandler

MyJobNo = Sheets("Summary").Range("A8").Formula = "left($a$7,4)"
myFileName = "CAPEXSummary-"
myDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji\CAPEX\"
'enter correct path name
ActiveWorkbook.SaveAs Filename:= _
myDir & myFileName & MyJobNo

myBUDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job
Analysis\Seraji\CAPEX\Backup\"
myBUName = "Backup-CAPEXSummary-"
ActiveWorkbook.SaveCopyAs Filename:= _
myBUDir & myBUName & MyJobNo
End Sub

When the file already exists the message "File already exists...etc" is fine.

If users have mistakenly left the previously saved file open and restart the
Template file the file saves as normal but MyJobNo changes to "FALSE" and the
user is unaware a new file wasn't saved.

Can I put an error trap in that warns the user that the file is open and
asks if they would like to close it before saving?

I've tried the following addition but I don't know how to identify the open
file in order to close it automatically (or even if it's a good idea):

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 55 ' "File already open" error.
MsgBox msg = "The file you are trying to save is already open" &
vbNewLine & _
" " & vbNewLine & _
"Select OK to have the file close and resave"
Close THE OPEN FILE 'Close open file.
Case Else
' Handle other situations here...
End Select
Resume ' Resume execution at same line
' that caused the error.

My applogies if this is long winded and difficult to read.

--
Jim

You give me more credit than I deserve. I usually develop these macros by
melding bits and pieces I find here that I think might work.

I was hoping for a suggested code snippet to add to my library.

I was not thinking more of closing the file after a warning rather than
deleting the file without an option to back out.

Thanks for your suggestion
--
Jim


"Pranav Vaidya" wrote:

I agree you can certainly write a error handling routine.
There is one more way you can achieve this...write a function which checks
if the file already exists and if yes, deletes it. Write your error handling
inside this function such as if the file is open can not be deleted the
function returns FALSE, else the function deletes the file and returns TRUE.
Now you should have this functino call with IF statement before your SAVE
statement.

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Jim G" wrote:

I have the following macro run in a template file and works well without the
error handling:

Sub Save()
' Macro recorded 02/10/2007 by me

Dim MyJobNo As String
Dim myFileName As String
Dim myDir As String
Dim myBUDir As String
Dim myBUName As String
On Error GoTo ErrorHandler

MyJobNo = Sheets("Summary").Range("A8").Formula = "left($a$7,4)"
myFileName = "CAPEXSummary-"
myDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji\CAPEX\"
'enter correct path name
ActiveWorkbook.SaveAs Filename:= _
myDir & myFileName & MyJobNo

myBUDir = "N:\MINING\PLANT\SERVICES\AXAPTA\Job
Analysis\Seraji\CAPEX\Backup\"
myBUName = "Backup-CAPEXSummary-"
ActiveWorkbook.SaveCopyAs Filename:= _
myBUDir & myBUName & MyJobNo
End Sub

When the file already exists the message "File already exists...etc" is fine.

If users have mistakenly left the previously saved file open and restart the
Template file the file saves as normal but MyJobNo changes to "FALSE" and the
user is unaware a new file wasn't saved.

Can I put an error trap in that warns the user that the file is open and
asks if they would like to close it before saving?

I've tried the following addition but I don't know how to identify the open
file in order to close it automatically (or even if it's a good idea):

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 55 ' "File already open" error.
MsgBox msg = "The file you are trying to save is already open" &
vbNewLine & _
" " & vbNewLine & _
"Select OK to have the file close and resave"
Close THE OPEN FILE 'Close open file.
Case Else
' Handle other situations here...
End Select
Resume ' Resume execution at same line
' that caused the error.

My applogies if this is long winded and difficult to read.

--
Jim