Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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 -- MichaelS ----------------------------------------------------------------------- MichaelS_'s Profile: http://www.excelforum.com/member.php...fo&userid=2640 View this thread: http://www.excelforum.com/showthread.php?threadid=39680 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
fName = "c:\myfolder\something.xls"
if dir(fName) = "" then activeworkbook.SaveAs fName else activeworkbook.Close SaveChanges:=False end if -- Regards, Tom Ogilvy "Dnereb" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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_" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
File already in use by another user
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. -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182 View this thread: http://www.excelforum.com/showthread...hreadid=396807 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User replaces existing file with blank during file open | Excel Discussion (Misc queries) | |||
Prompt user to select file with default file selected dialog | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming |