 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Error in a "Kill" command
Excel XP & Win XP In this snippet of code I am trying to "Kill" 3 files. I get a "Subscript out of range" error on the "Kill" command with the first file. So I changed the order of the files in the "For Each.." line and again I get that error with the first name. This tells me that the problem is with the "Kill" line. The "ThePathDocs" is the path to the folder that holds the files. What did I do wrong? Thanks for your time. Otto For Each TheFile In Array("One.doc", "Two.doc", "Three.doc") Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly Kill Workbooks(ThePathDocs & TheFile).FullName This is a sub I use to kill a file where the full path is typed into a cell along with some other stuff in the same row. Maybe you need to just set the path and kill that. Or, maybe just change to Kill ThePathDocs & TheFile.FullName Sub KillFile() mc = Choose(ActiveCell.Column, 4, 3, 2, 1, 0) wbn = ActiveCell.Offset(0, mc).Value MyAnswer = MsgBox("Do you want to KILL this file?", vbYesNo) If MyAnswer = vbYes Then Kill wbn ActiveCell.EntireRow.Delete End If End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Excel XP & Win XP > In this snippet of code I am trying to "Kill" 3 files. I get a "Subscript [quoted text clipped - 7 lines] > Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly > Kill Workbooks(ThePathDocs & TheFile).FullName Don Thanks for your help. I understand that one must set the file attribute to "Read Only" prior to "killing" the file. Is that true? Otto > This is a sub I use to kill a file where the full path is typed into a > cell along with some other stuff in the same row. [quoted text clipped - 24 lines] >> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >> Kill Workbooks(ThePathDocs & TheFile).FullName I believe you do in fact need to change access to read-only. Otherwise you'll get a "permission denied" error. SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) > Don > Thanks for your help. I understand that one must set the file [quoted text clipped - 28 lines] >>> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >>> Kill Workbooks(ThePathDocs & TheFile).FullName Nope. In fact, if you set the attribute to readonly, then you'll have trouble deleting the file. The code that you tried to use is based on code that deletes a workbook that's open in excel. If the file is open in read/write mode, then windows will yell at you when you try to delete it (either manually or via code). The .changefileaccess line doesn't change the file's attribute. Instead, it tells excel to change its access to that file as readonly. Kind of like originally opening the file in readonly mode. Then Windows won't see the file/workbook as in-use and the Kill statement will work. ==== Some notes about your original code: For Each TheFile In Array("One.doc", "Two.doc", "Three.doc") Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly Kill Workbooks(ThePathDocs & TheFile).FullName This doesn't really make sense to me. The file extensions are .doc. I would think that you would not have this kind of file open in excel. That means that you would refer to them through the workbooks() collection. If your code were using excel files and these files were open, you could use: For Each TheFile In Array("One.xls", "Two.xls", "Three.xls") Workbooks(TheFile).ChangeFileAccess xlReadOnly Kill Workbooks(TheFile).FullName When you use workbooks(), you don't specify the drive/folder/path--just the filename. > Don > Thanks for your help. I understand that one must set the file attribute [quoted text clipped - 32 lines] > >> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly > >> Kill Workbooks(ThePathDocs & TheFile).FullName SignatureDave Peterson Dave Thanks for that. Looking at it again, "workbooks" and ".doc" don't make any sense to me either. What I have is 3 Word files, none of which are open. I want to "Kill" them. Then I want to move/copy 3 other word files from one folder to another. I would appreciate any help you can give with the code for this. Thanks again for your time. Otto > Nope. > [quoted text clipped - 82 lines] >> >> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >> >> Kill Workbooks(ThePathDocs & TheFile).FullName For Each TheFile In Array("One.doc", "Two.doc", "Three.doc") kill ThePathDocs & TheFile next TheFile assuming that thepathdocs is ok. > Dave > Thanks for that. Looking at it again, "workbooks" and ".doc" don't make [quoted text clipped - 92 lines] > > > > Dave Peterson SignatureDave Peterson Thanks Dave. Otto > For Each TheFile In Array("One.doc", "Two.doc", "Three.doc") > kill ThePathDocs & TheFile [quoted text clipped - 109 lines] >> > >> > Dave Peterson Here is one in my personal.xls assigned to a custom button on the toolbar. I use it to kill the worbook that I am currently in. Works if in the set path. I suppose it could be changed to refer to the dir of the activeworkbook. In my experience, no readonly needed. Can't remember where it came from. Sub KillActiveWorkbook() With ActiveWorkbook mb = .Name .Close End With MyAnswer = MsgBox("Do you want to KILL this file?", vbYesNo) If MyAnswer = vbYes Then Kill mb End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don > Thanks for your help. I understand that one must set the file [quoted text clipped - 28 lines] >>> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >>> Kill Workbooks(ThePathDocs & TheFile).FullName I'm do not see why you have to change the file access to Read Only in order to delete it, but that shouldn't be your error either. My first thought is that ThePathDocs variable does **not** end in a backslash (so that when you concatenate it with TheFile, the resulting filename is an ugly combination of the last folder in the path and the actual filename). Rick > Excel XP & Win XP > In this snippet of code I am trying to "Kill" 3 files. I get a "Subscript [quoted text clipped - 7 lines] > Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly > Kill Workbooks(ThePathDocs & TheFile).FullName Rick The path does end in a backslash. Dave pointed out that the word "Workbooks" and a Word file name (xxx.doc) do not go together, and that's obvious now that he has pointed it out. I have 3 Word files, none of which are open, and I want to remove them from disk (hence the Kill command). Any help you can provide with the code would be appreciated. Thanks again. Otto > I'm do not see why you have to change the file access to Read Only in > order to delete it, but that shouldn't be your error either. My first [quoted text clipped - 15 lines] >> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >> Kill Workbooks(ThePathDocs & TheFile).FullName Since the path is OK, you would just delete them as normal files. You could do them one at a time... Kill ThePathDocs & "One.doc" Kill ThePathDocs & "Two.doc" Kill ThePathDocs & "Three.doc" or via the Array function as you originally tried to do.... For Each TheFile In Array("One.doc", "Two.doc", "Three.doc") Kill ThePathDocs & TheFile Next One note, in case you were not aware, Kill performs a non-recoverable deletion of the file... it does NOT delete to the Recycle Bin. Rick > Rick > The path does end in a backslash. Dave pointed out that the word [quoted text clipped - 23 lines] >>> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >>> Kill Workbooks(ThePathDocs & TheFile).FullName > One note, in case you were not aware, Kill performs a non-recoverable > deletion of the file... it does NOT delete to the Recycle Bin. And code to send a file or folder to the Recycle Bin can be found at http://www.cpearson.com/Excel/Recycle.aspx. SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) > Since the path is OK, you would just delete them as normal files. You > could do them one at a time... [quoted text clipped - 42 lines] >>>> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >>>> Kill Workbooks(ThePathDocs & TheFile).FullName Thanks Chip. That's good information to have. Otto >> One note, in case you were not aware, Kill performs a non-recoverable >> deletion of the file... it does NOT delete to the Recycle Bin. [quoted text clipped - 48 lines] >>>>> Workbooks(ThePathDocs & TheFile).ChangeFileAccess xlReadOnly >>>>> Kill Workbooks(ThePathDocs & TheFile).FullName |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.