Path/File Error

This code has worked for a long time, but starting yesterday I am now getting a "Path/File" error.  The code below is run from a MS Access module and lists all the files in a directory, then deletes any that are more than 45 days old based on the file name.  The File names look like this...Backup_20150431.xls.   The numbers at the end of the file name are the date.  I use that to determine if the file is older than 45 days.  If it is, the code is supposed to delete that file.  Now I am suddenly getting a Path/File Error and I don't know where to start troubleshooting.  Thanks in advance!
Public Function CopyClothLeads()

' finds all files in destination folder with names in format "Backup_YYYYMMDD' and deletes any older than 45 days

Dim strFldr As String
Dim strFile As String
Dim FileToGet As String

strFldr = "P:\Sales_Dept\Reports\Cloth Referral Program\Lead Folder\Backups" ' modify folder to your own
strFile = Dir(strFldr & "\Backup*.*") ' get list of files in folder

Do While Len(strFile) > 0
FileToGet = Left(strFile, Len(strFile) - 4)
If Len(FileToGet) = 15 Then ' only check files with name of correct length
If FileToGet <= CStr("Backup_" & Format(Date - 45, "yyyymmdd")) Then

'to rename the file...use this
'Name strFldr & "\" & strFile As strFldr & "\" & "OLD" & strFile

' to delete the file use this:
Kill strFldr & "\" & strFile

Else
' do nothing
End If

Else

'ignore file
End If

strFile = Dir
Loop

Open in new window

bsncpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
which line is raising the error?

do a debug>Compile and correct any error raised.
do a compact and repair

was there a recent update done on the computer?
0
KimputerCommented:
You should use F8 to step through the code.
Several possible reasons:

Drive P: isn't there anymore
Drive P: is there, as are the files, but you can't delete the file due to permission issues or file is in use (can be tested by just deleting the file in Explorer, you will also get an error)

The code itself look simple enough and I don't think the error is in the code
0
bsncpAuthor Commented:
Thanks...I am running into the error at this line:

Kill strFldr & "\" & strFile

I am able to manually delete the file.  I can even delete the whole directory or rename it if I want, so this is not acting like a Permissions issue.  I've even tried Killing the actual file path:

Kill "P:\Sales_Dept\Reports\Cloth Referral Program\Lead Folder\Backups\Backup_20150524.xls"

...I still get the error.  However, I created a sub-directory within this directory and I can kill/delete files with no error.  If this were a permissions issue, I would expect to be unable to even manually delete the files, but that is not the case.  Only have the issue when I run the code.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This:

FileToGet = Left(strFile, Len(strFile) - 4)
If Len(FileToGet) = 15 Then ' only check files with name of correct length

 is bad.  Your assuming the extension is .XLS.    Bet you someone went to a newer version of Excel and it's now .XLSX

 You need to be looking for the last period in the file name to property get the extension, not assuming it's the last three characters.

Jim.
0
KimputerCommented:
Quick workaround (only 2 lines modification)

Public Function CopyClothLeads()

' finds all files in destination folder with names in format "Backup_YYYYMMDD' and deletes any older than 45 days

Set objfso = CreateObject("Scripting.FileSystemObject")

Dim strFldr As String
Dim strFile As String
Dim FileToGet As String

strFldr = "P:\Sales_Dept\Reports\Cloth Referral Program\Lead Folder\Backups" ' modify folder to your own
strFile = Dir(strFldr & "\Backup*.*") ' get list of files in folder

Do While Len(strFile) > 0
FileToGet = Left(strFile, Len(strFile) - 4)
If Len(FileToGet) = 15 Then ' only check files with name of correct length
If FileToGet <= CStr("Backup_" & Format(Date - 45, "yyyymmdd")) Then

'to rename the file...use this
'Name strFldr & "\" & strFile As strFldr & "\" & "OLD" & strFile

' to delete the file use this:
objfso.DeleteFile strFldr & "\" & strFile

Else
' do nothing
End If

Else

'ignore file
End If

strFile = Dir
Loop

End Function

Open in new window


If this doesn't work, I'm pretty sure there must be one tiny typing error in path or folder.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<objfso.DeleteFile  strFldr & "\" & strFile>>

Change the code to:

strFullPath = strFldr & "\" & strFile
objfso.DeleteFile strFullPath

and put a stop on the Kill.  Then look at the full path.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bsncpAuthor Commented:
I figured it out.  This last suggestion was the closest thing to what I actually did.  The files I was trying to delete were Read-Only.  Not sure why this wasn't an issue before.  So I added a variable for the full file path, similar to Jim's suggestion, and added a step to set the Read-Only attribute to vbNormal before deleting the file.  That worked.  Thanks for the help.  Here is my final code, acknowledging the fact one line is a little clunky because it assumes all my files will be .xls files.  For now, that's okay.

Public Function CopyClothLeads()

' finds all files in destination folder with names in format "Backup_YYYYMMDD' and deletes any older than 45 days

Dim strFldr As String
Dim strFile As String
Dim strFilePath As String
Dim FileToGet As String

strFldr = "P:\Sales_Dept\Reports\Cloth Referral Program\Lead Folder\Backups" ' modify folder to your own
strFile = Dir(strFldr & "\Backup*.*") ' get list of files in folder


Do While Len(strFile) > 0

FileToGet = Left(strFile, Len(strFile) - 4)
If Len(FileToGet) = 15 Then ' only check files with name of correct length
If FileToGet <= CStr("Backup_" & Format(Date - 45, "yyyymmdd")) Then

'to rename the file...use this
'Name strFldr & "\" & strFile As strFldr & "\" & "OLD" & strFile

strFilePath = strFldr & "\" & strFile
SetAttr strFilePath, vbNormal

' to delete the file use this:
Kill strFilePath

Else
' do nothing
End If

Else

'ignore file
End If

strFile = Dir
Loop
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.