File already in use by another user

[MichaelS_]

Hi

We are using a VBA-Macro which opens about 180 files in order to update them. Sometimes it happens that one of those files, is already in use by another user, then an error message appears which says “A file named ‘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 message 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 dialog box”.

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

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

Michael

[Dnereb]

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

On Error Resume Next

'try saving

Please Login or Register  to view this content.

to prevent alert messages....

Please Login or Register  to view this content.

Hope it helps

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Dnereb" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> --------------------
>
>
> Hope it helps
>
>
> --
> Dnereb
> ------------------------------------------------------------------------
> Dnereb's Profile:
http://www.excelforum.com/member.php...o&userid=26182
> View this thread: http://www.excelforum.com/showthread...hreadid=396807
>

[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.

--
Regards,
Tom Ogilvy

"MichaelS_" <[email protected]> wrote
in message news:[email protected]...
>
> 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_
> ------------------------------------------------------------------------
> MichaelS_'s Profile:
http://www.excelforum.com/member.php...o&userid=26406
> View this thread: http://www.excelforum.com/showthread...hreadid=396807
>

[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.