Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to forcely remove or overwrite an open excel file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to forcely remove or overwrite an open excel file
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)" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to forcely remove or overwrite an open excel file
Thanks a lot for this information. So we just need to forcus on education
then... Regards, Peter "JLatham" wrote: 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)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Prompt to Open File as Read-Only (Excel 2007) | Excel Discussion (Misc queries) | |||
Overwrite Open Excel files on Macro | Excel Programming | |||
open text file in Excel and remove columns | Excel Discussion (Misc queries) | |||
Remove Macros before send? & Runtime error if choosing not to overwrite file? | Excel Programming | |||
Calc status of open excel workbook should not overwrite another | Setting up and Configuration of Excel |