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
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
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
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
ASKER
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\Clo
...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.
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\Clo
...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.
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.
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.
Quick workaround (only 2 lines modification)
If this doesn't work, I'm pretty sure there must be one tiny typing error in path or folder.
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 FunctionIf this doesn't work, I'm pretty sure there must be one tiny typing error in path or folder.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
do a debug>Compile and correct any error raised.
do a compact and repair
was there a recent update done on the computer?