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:

[TABLE="width: 889"]
<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>[TR]
[TD="width: 44, bgcolor: transparent"]status[/TD]
[TD="width: 296, bgcolor: transparent"]FileName[/TD]
[TD="width: 335, bgcolor: transparent"]Path[/TD]
[TD="width: 510, bgcolor: transparent"]FileName and Path[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]03151-LZBN 505 510 rev.X.doc[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\03151 Distributielijst[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\03151 Distributielijst\03151-LZBN 505 510 rev.X.doc[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\6 Integration[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\6 Integration\1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\Inhoud\6 Integration[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\Inhoud\6 Integration\1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\Network Intergration[/TD]
[TD="bgcolor: transparent"]J:\H-Mod\Network Intergration\1_102 72-FCX 901 05_1 Uen.pdf[/TD]
[/TR]
</tbody>[/TABLE]



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