Excel 2010 VBA - Unable to Kill Files

[Frank353]

OS Windows 7
Excel 2010

Requirement to delete a number of Files(Not just Excel Files) in different folders.
These files and their fully qualified paths are listed in an Excel Workbook.

When I run my code it to perform the deletion it functions well for about 80% of the files. The other 20% return Err.Number 75.

I have checked the filename and path exists using the fully qualified path.
I have checked the files are not open.
I have the correct authority level to delete these files.

Yet when I perform the kill statement using the same fully qualified path I get VBA Err.number 75

I would be very grateful for your insight/thoughts.

Regards
Frank

 

[Frank353]

I thought this might help:

Example of Data:

status	FileName	Path	FileName and Path
	03151-LZBN 505 510 rev.X.doc	J:\H-Mod\03151 Distributielijst	J:\H-Mod\03151 Distributielijst\03151-LZBN 505 510 rev.X.doc
	1_102 72-FCX 901 05_1 Uen.pdf	J:\H-Mod\6 Integration	J:\H-Mod\6 Integration\1_102 72-FCX 901 05_1 Uen.pdf
	1_102 72-FCX 901 05_1 Uen.pdf	J:\H-Mod\Inhoud\6 Integration	J:\H-Mod\Inhoud\6 Integration\1_102 72-FCX 901 05_1 Uen.pdf
	1_102 72-FCX 901 05_1 Uen.pdf	J:\H-Mod\Network Intergration	J:\H-Mod\Network Intergration\1_102 72-FCX 901 05_1 Uen.pdf

<colgroup><col style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;" width="44"> <col style="width: 222pt; mso-width-source: userset; mso-width-alt: 10825;" width="296"> <col style="width: 251pt; mso-width-source: userset; mso-width-alt: 12251;" width="335"> <col style="width: 383pt; mso-width-source: userset; mso-width-alt: 18651;" width="510"> <tbody>
</tbody>

Code being used:

Sub Remove_Dross()

For i = 2 To 281
'Fname = Cells(i, 2).Value
'FPath = Cells(i, 3).Value
'FMerge = FPath & "\" & Fname
FName1 = Cells(i, 4).Value

If Dir(FName1) <> "" Then
Cells(i, 5).Value = "File exists"
Else
Cells(i, 5).Value = "File does not exist"
End If

Err.Number = 0
On Error Resume Next
Kill FName1
Cells(i, 1).Value = Err.Number
Next i
End Sub

Hope this clarifies things

Regards
Frank353

 

[Frank353]

After Much Head scratching and thinking, I have found a solution.
I was able to manually delete the files therefore I do have the correct authority to delete files, but it turned out that I need to set the file attributes in VBA.

I introduced "SetAttr FName1, vbNormal" to the code and it worked.

The attributes settings are:

Constant	Value	Description
vbNormal		Normal (default).
vbReadOnly	1	Read-only.
vbHidden	2	Hidden.
vbSystem	4	System file. Not available on the Macintosh.
vbArchive	32	File has changed since last backup.
vbAlias	64	Specified file name is an alias. Available only on the Macintosh.

<tbody>
</tbody>

I am still not sure why it worked, but hey it worked and that is good enough for me.

Thank you to the 50+ people who viewed this query.

Regards
Frank