File already in use by another user

[MichaelS_]

Hi

We are using a VBA-Macro which opens about 180 files in order to updat
them. Sometimes it happens that one of those files, is already in use b
another user, then an error message appears which says “A file name
‘XY.xls’ already exists in this location. Do you want to replace it?
Then I press the “No” button. After that a Visual Basic error messag
pops-up “Run-time error’1004’: ‘XY.xls’ is read-only. To save a copy
click OK, then give the workbook a new name in the Save As dialo
box”.

The idea would be that the macro updates all 180 files unless one i
already in use. In this case the macro should just close the fil
without saving.

It would be great to get some help. Thanks a lot.

Michae

 

[Dnereb]

You couls use the err. object and on error goto
to catch the error.

On Error Resume Next

'try saving


Code:
--------------------
If Err Then
'do what you want with an error (like checking if it is the read only error)
end if
on error goto 0 'reset error trapping
--------------------

to prevent alert messages....


Code:
--------------------
Application.DisplayAlerts = False

'DO YOUR STUFF

Application.DisplayAlerts = True

 

[Tom Ogilvy]

fName = "c:\myfolder\something.xls"
if dir(fName) = "" then
activeworkbook.SaveAs fName
else
activeworkbook.Close SaveChanges:=False
end if

 

[MichaelS_]

Thanks for your answer. I am already using the DisplayAlerts =
False/True. Why do I need an error handler (sorry about this question)?
The only thing I want is to say “No” as soon as the message “A file
named ‘XY.xls’ already exists in this location. Do you want to replace
it?” appears.

Maybe I am doing it the wrong way.

Michael

 

[MichaelS_]

Thanks for your answer. I am already using the DisplayAlerts =
False/True. Why do I need an error handler (sorry about this question)?
The only thing I want is to say “No” as soon as the message “A file
named ‘XY.xls’ already exists in this location. Do you want to replace
it?” appears.

Maybe I am doing it the wrong way.
Michael

 

[Tom Ogilvy]

I didn't suggest using an error handler. I suggested checking if the file
already exists and not attempting to overwrite it. Using displayalerts =
false should just overwrite the file without prompt which is not what you
said you want.

You ask "Why" Indeed the answer is that you must code the behavior you
desire when it differs from the default. In fact, what I suggested does
not answer no, but moved immediately to the desired consequence. If you
wish to ask a question for which the answer is already known, then perhaps
you **will** need an error handler as well.

 

[Dnereb]

In other words you need to error handle to react to an already open
file, what you prefer to do is up to you. If you don't handle the error
the file won't be saved.
And Tom nor I assumed that was what you want.
And take into account that there can be other errors while saving your
file. Like a down network, not enough space on the media and others.