Error in a "Kill" command

[Otto Moehrbach]

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

 

[Don Guillett]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Otto Moehrbach]

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

 

[Chip Pearson]

I believe you do in fact need to change access to read-only. Otherwise
you'll get a "permission denied" error.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

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 Guillett]

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

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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

 

[Dave Peterson]

For Each TheFile In Array("One.doc", "Two.doc", "Three.doc")
kill ThePathDocs & TheFile
next TheFile

assuming that thepathdocs is ok.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Chip Pearson]

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.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Otto Moehrbach]

Thanks Dave. Otto

For Each TheFile In Array("One.doc", "Two.doc", "Three.doc")
kill ThePathDocs & TheFile
next TheFile

assuming that thepathdocs is ok.

 

[Otto Moehrbach]

Thanks Chip. That's good information to have. Otto