How to forcely remove or overwrite an open excel file

[Piotr (Peter)]

Hi,

We gather data form various excel files accross our network and combine
them. However, we can't proceed if a user left a file open as it generates an
error. We can't ignore such file so is there any way to get the file copied
to a different location then close the original and replace it with a copy?
Every time we try to do this we get Access denied error if the file is open.

Your help would be highly appreciated.

Peter

 

[JLatham]

Are you opening the other workbooks from code or manually in Excel?

If you're opening them in VBA code, you can open them in Read Only mode and
later Close them without saving changes. But if you need to clear out data
that you've copied from them, then you still have the same problem - trying
to take control of an open file that someone else owns. If that's truly a
show stopping situation, then user education may be the only answer.

Here are examples of the .Open and .Close commands in VBA that could help you.

Application.DisplayAlerts = False
'open the workbook without updating links and in read only mode
Workbooks.Open "X:\DailyLog\pathto.xls", False, True
Application.DisplayAlerts = True

and to close without saving any perceived changes
Application.DisplayAlerts = False
Workbooks("pathto.xls").Close False
Application.DisplayAlerts = True

 

[Piotr (Peter)]

Thanks a lot for this information. So we just need to forcus on education
then...

Regards,
Peter