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
0
Utf
2/15/2010 12:27:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1623 Views

Similar Articles

[PageSpeed] 6

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
0
Utf
2/15/2010 2:51:01 PM
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
0
Utf
2/16/2010 9:40:01 AM
Reply:

Similar Artilces:

Can't open attachment
Hi I have an email that is received as part of a mailing list so it is only sent as a BCC, so no TO or CC address it has two attachments a txt an a doc the txt can be opened and or saved but the DOC gets cannot create check permissions... if its forwarded to someone else they can open it! I can open all other attachments including doc, I have tried saving it to another location etc. Any ideas? thanks I realize this is a strange question but the thought did cross my mind. What happens if you forward it to yourself? "Adrian" wrote: > Hi > I have an email that...

publisher cannot write to a file on a network drive
Publisher can not write to K:\...\...\newsletter.pub to the disk. Your hard disk may be full or hardware problems. The network drive is not full and the user has full control over the folder she is trying to save to. The user can write to the folder with other apps, i.e. word Dan Thannhauser wrote: > Publisher can not write to K:\...\...\newsletter.pub to the disk. > > Your hard disk may be full or hardware problems. > > The network drive is not full and the user has full control over the > folder she is trying to save to. > > The user can write to the folder with...

How do I find differences between two excel worksheets?
Each worksheet has one column of numbers and a second column of text. Using Excel2003 See Chip's site www.cpearson.com . He has lots on this topic best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jfurneaux" <jfurneaux@discussions.microsoft.com> wrote in message news:C9877485-FB21-4495-9AE0-83C41409ACA3@microsoft.com... > Each worksheet has one column of numbers and a second column of text. > Using Excel2003 ...

Excel error and closure upon opening
This morning, I used Excel 2000 without any problems. This afternoon, I cannot open it. Every time I try, I get the following error: "EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created." Rebooting does not fix the problem. Shutting down and restarting does not fix the problem. It appears that there have been a flurry of similar complaints on the message board today. Does anyone have any ideas on how to fix this? Open it on someone else's PC with a different version (newer, I guess of Exc...

Force Uninstall
All, A coupla questions . . . Suppose I just want to forcefully uninstall Exchange 2000 from a server, and an org. Currently the uninstall fails because (as mentioned in a previous post) I have users that still show as being on that server, however the store that those users were on is no longer there (because of a disaster). Is there some way to force the uninstall? Another question. I have two healthy clustered machines running currently. Suppose I just remove the object for the old exchange server from the ESM? Assuming I have performed all of the other steps steps (like moving o...

Excell protection
I made a worksheet in excell 2003. There is one group(+)/ungroup(-) button. When I put protection this button is useless. Is there any way to chenge it? i.e. I want to make protection of this worksheet and normal working group(+)/ungroup(-) button. I had the same issue and solved it with a small bit of VBA code that runs when the file opens Sub Auto_Open() Sheets("Sheet Name").Unprotect Password:="password" With Sheets("Sheet Name") .Protect Password:="ou812", userinterfaceonly:=True .EnableOutlining = True ...

Excel acting up
Has anyone experienced the following behavior in Excel Workbooks? We have developed a PM tool (an Excel Workbook using VBA macros) several years ago. This workbook is used as a template by 30 or so people; and working properly in excel 2003 & excel 2007 until maybe 2 weeks ago. About 2-3 weeks ago, one of the users sent a copy of this file to the office. It was behaving strangely...objects from other worksheets (dtpickers, formatted cells/ranges of cells) were painted over the active worksheet as we were working with it (persistent until scrolling down or minimize-maximize ...

forcing user input into cells
howdy all... i'm new to doing stuff with excel have a bit of a background in javascript programming what i'm trying to do is force a user to enter something, anything int a specific cell if the user tries to tab past the cell and leave it blank, i want a error to come up, telling them that they have to enter something befor they can continue on with entry into other cells any help would be much appreciated -Thank -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=164...

How do I change the scale (extend) the X axis in Excel 2007?
One of many questions under the new Office 2007. Trying to reproduce the old behavior some way. In Excel 2003... rioght click on X axis --> select Format Axis --> change value in "Maximum" field --> Bingo. Chart has new scale (extended with dates for another year) If I can't do this in Excel 2007, I just need to know so I can find/buy other software. Hi, In 2007: Right-click the axis and choose Format Axis, then click Fixed to the right of Maximum on the Axis Options tab and enter a new Maximum value -- If this helps, please click the Yes button Cheers, Sha...

Importing a CSV file
I want to import a file into physical inventory. Here is what I do: 1) Run a Quantity report(I don't think the report really matters) 2) I put it in alphabetical order, then I export it using RMS Manager into a ..CSV file 3) I go into Inventory > Physical Inventory, and I click on New and Manuel Entry 4) I import the file...it works just fine!!! BUT If you in any way alter the .CSV file, and save it again, in the same format Steps 1 - 4 won't work...WHY WHAT THE HECK AM I DOING WRONG??? -- Thank You Vince :) The problem might be the Item Lookup Codes are being modified by ...

opening multiple files #4
Excel will not open multiple files. You need to describe the scenario as Excel ordinarily will open multiple files. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Can't open Money 2006 free trial
....or my old Money 2004 after downloading the "free trial." I cannot get support from Microsoft without a product ID (which I don't have for the free trial) and I can't seem to restore any old files, even using the run box and instructions from microsoft.com. Exactly what happens (or doesn't)? What error messages? -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "help" <help@discussions.microso...

How to open a popup form already filtered
I have a form with a list of products. Each product has a unique product ID but could have several that have the same name and the only difference being (other then a different productID) is they are different colors. I want to double click on a product name and get a new form (popup) that looks identical to the form they just doubled clicked on but already filtered to only the records that had the same product name. Example: A form with a list of 1000 different cars. Double click on Toyato Camery and get a popup form that is a list of only 4 Toyota Camerys (each a different color). Form...

live mail opening folders
when I open my in box I usually double click on the message & it opens, during the past week this has stopped & I have right click the email and select open to view. My reading pane is closed as I prefer only to see the email subjec t untgil I choose to open it. can anyone tell me what would be casuing it. I use windows live mail at work & at home both on win 7. My work pc does not have this problem. A couple of things come to mind . . . . . what type of mouse do you use, if it is wireless perhaps the battery is old. Does this happen on anything else you double cl...

Is it possible to count how many times a spreadsheet is opened
I need an "auto counter" that will count how many times certain spreadsheets are opened. Can this be done in Excel? --- Message posted from http://www.ExcelForum.com/ Hi do you mean to count how many times a file has been opened?. If yes you may try the following code. Put this in your workbook module (not in a standard module). It will increment the value in cell A1 on sheet1 Private Sub Workbook_Open() with Worksheets("Sheet1").range("A1") .Value = .Value + 1 end with End Sub -- Regards Frank Kabel Frankfurt, Germany > I need an "auto counte...

Install add-in under Excel 2007/Vista
I have an add-in that comes with the instruction to add the .xla and support files to the C:\Documents and Settings\All Users\Application Data\Microsoft\AddIns folder. Of course, either this folder doesn't exist or I can't find it under Vista. Does anyone know which folder under Vista takes the place of the above folder under XP? Thanks. Hi R If you want to see it in the Add-in list without browsing to the file then copy it in the Library folder See how on this page http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "RStan...

Remove Hyperlinks...show the Path
If you would like to permanently display the path in the cell (i.e. replace microsoft with www.microsoft.com), right click on the cell, and use edit hyperlinks. The top pane shows the display name, the bottom pane shows the path. Copy the path to the display name. I am not sure if there is a quick way to do this. NOTE: You will have to use keyboard commands to copy and paste. This solution works, but it is one by one. Does anyone k now of a mass change method? I want to take a hyperlinked word (web address or email address) and have it display the hyperlink properties instead of t...

open and close form with one OnClick
I would like to be able to open a form by clicking a button, once that form is open and a variable is transferred, I would like to close the original form. I have tried to embed a DoCmd.Close acForm "formname", into a "OnClick" event procedure, but I still have the first form open. Need it in 2000 format. Thanks for any help! Jeff, On the close button of form1st put this code DoCmd.OpenForm "form2nd",,,,,,"value of variable" DoCmd.Close acForm, Me.Name Jeanette Cunningham "Jeff @ CI" <JeffCI@discussions.microsoft.com> wrote in...

How to set Read-Only Recommended as default save in Excel 2002?
How do I set Read-Only Recommended as default to save files in Excel 2002? Thanks. This is not a global option. Must be done on each workbook. One way around this is to open a new workbook then save as a Template with the options you want. Name the workbook BOOK and save as MS Excel Template(*.xlt) Do not enter the extension, Excel will do that for you. Store in your XLSTART folder and it will become the template for all workbooks when opened via File>New Note: this will not affect previously saved workbooks. Gord Dibben Excel MVP On Thu, 14 Apr 2005 14:11:01 -0700, "flyaj&q...

Corrupted PST file
situation as follows; 1. from notebook, copied PST file to CD (using Nero) 2. open PST at home PC, using open PST file...; error msg "not valid PST file" 3. use scanpst.exe utility ... same msg "not valid PST valid" 4. tried import command in Outlook... same msg "not valid PST valid" what gives? TMT You either burned the data too fast (PST files don't like to be burned above 4x) and/or you didn't check the option to remove the ";1" from the file names in Nero's options dialog. -- Due to the Swen virus, all e-mails sent to this account w...

Force quit
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Closing Word get error message &quot;A file error has occurred. Check your network connections or make sure the disk is properly inserted&quot; Am then required to use force quit. Anybody have solution? Make sure Office & OS X are fully updated. If they are & you still encounter the problem, crashing on Quit is usually the result of one of theses causes: Need to repair disk permissions Damaged preference files Corrupt Normal.dotm template See this page for the respective links in t...

Export .zip files from emails
Does anyone know of a way to have an email with say a .zip attachment within it have the attachment automatically saved out of the email and to a folder on the server? A user could really do with this sort of functionality as they get around 100 emails from a specific source with these type of attachments a day. Thanks in advance. Mike ...

Word/Excel: Freezing on Save
I have a client using Office 2001 on a B/W G3, OS 8.6, and every time she tries to save a new Word or Excel file, the system hangs. More specifically, it's not truly hanging -- the cursor moves but it's stuck showing the wrist watch. The system *does* hang when you try to force-quit the application. Short of wiping the entire hard drive and reinstalling everything, she has tried about everything else... Norton says the drive is OK. She's has uninstalled/reinstalled Office countless times. All to no avail. TIA for any help you can provide. Brian Hi System hangs are usually the...

Forcing Appointments
I use outlook to schedule appointments with my customers with specific employees. I have a scheduling department that does this scheduling via outlook. The employees have no option to reject or modify these appointments; therefore, I would like to force this on their calendar with them having to accept it and not allowing them to modify it. Additionally, I need to know real time once they are scheduled. Right now I don't know they are scheduled until they accept and that sometimes takes too long and then they are inadvertently double booked by the scheduling department. So how do ...

picture doesn't appear when file opened.
The picture (tif or jpeg created in Digital Imaging Pro 10) will appear if I open picture file by going thru documents; but picture will not appear if I go thru Publisher "open". I used "repair" and "reinstall" but it doesn't help. -- Mae Mae McGerm wrote: > The picture (tif or jpeg created in Digital Imaging Pro 10) will appear if I > open picture file by going thru documents; but picture will not appear if I > go thru Publisher "open". I used "repair" and "reinstall" but it doesn't > help. Are you ...