-
Dec 7th, 2021, 11:43 AM
#1
Thread Starter
Junior Member
[RESOLVED] Procedure to delete certain files
I'm not sure of the version but it was in Excel 2007, my current.
I had some nice help in developing a code to delete files:
They are in a sub-sub-sub folder in Program files.
The all have a specific extension.
I wish to retain all files less than two months old, deleting the rest.
There are many sub folders and I wanted to cover them all.
Code:
Sub DeleteOldFiles()
' Path of top folder
Const strFolder = "C:\Program Files (x86)\Tradestation 9.5\Scans"
Dim objFSO As Object
Dim objFolder As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)
Call DeleteFiles(objFolder)
End Sub
Sub DeleteFiles(objFolder As Object)
Dim objFile As Object
Dim objSubfolder As Object
' Check files
For Each objFile In objFolder.Files
If LCase(objFile.Name) Like "*.tsrslts" Then
If objFile.DateLastModified < Date - 60 Then
objFile.Delete
End If
End If
Next objFile
' Check subfolders
For Each objSubfolder In objFolder.SubFolders
Call DeleteFiles(objSubfolder)
Next objSubfolder
End Sub
I believe the code will work, except when the code attempts to execute objFile.Delete then I get a run time error 70 "permission denied"
I'm looking for a line to allow permissions.
-
Dec 7th, 2021, 01:53 PM
#2
Re: Procedure to delete certain files
Is this for use on your computer, or on a computer where someone else is going to be using this macro?
The simply fix is to change the permissions on the affected folder to allow the logged in user to delete files. Note that the Program Files folder and sub-folders are not normally a place where you should be saving, updating, or deleting files, since you do not normally have permission to do that for those folders.
-
Dec 7th, 2021, 02:44 PM
#3
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
Is this for use on your computer, or on a computer where someone else is going to be using this macro?
My PC not intended for distribution.
The simply fix is to change the permissions on the affected folder to allow the logged in user to delete files.
That would work, but I would like to have a clean procure to do all.
Note that the Program Files folder and sub-folders are not normally a place where you should be saving, updating, or deleting files, since you do not normally have permission to do that for those folders.
Thanks
Any suggestions for the code?
-
Dec 7th, 2021, 03:19 PM
#4
Re: Procedure to delete certain files
The Program Files folder has been increasingly locked down as Windows progressed, because what's in there is not intended to be deleted in this fashion. You can still do it, but you run into issues like this. Partially for that reason, the SpecialFolders was created:
https://docs.microsoft.com/en-us/dot...r?view=net-6.0
If you could work with them rather than Program Files, it would make your life a whole lot easier.
My usual boring signature: Nothing
-
Dec 7th, 2021, 03:26 PM
#5
Re: Procedure to delete certain files
jdc2000 is right... your Program Files folders or subfolders are not the place for reading and writing data files from your application. The AppData folder is specifically intended for that purpose, and has full read/write access by default
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Dec 7th, 2021, 03:26 PM
#6
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by Shaggy Hiker
The Program Files folder has been increasingly locked down as Windows progressed, because what's in there is not intended to be deleted in this fashion. You can still do it, but you run into issues like this. Partially for that reason, the SpecialFolders was created:
https://docs.microsoft.com/en-us/dot...r?view=net-6.0
If you could work with them rather than Program Files, it would make your life a whole lot easier.
The software writes the files to these folders, I have no control.
-
Dec 7th, 2021, 11:46 PM
#7
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by .paul.
jdc2000 is right... your Program Files folders or subfolders are not the place for reading and writing data files from your application. The AppData folder is specifically intended for that purpose, and has full read/write access by default
Well I'm telling all of you that the files are written where I have it coded, Program Files (x86). I will check in the AppData folder but that will probably be fruitless.
-
Dec 7th, 2021, 11:58 PM
#8
Re: Procedure to delete certain files
Originally Posted by bknight
The software writes the files to these folders, I have no control.
That's the pertinent point. I think that everyone was assuming that you had written the code to write the files in the first place in which case you should change it to write them elsewhere. If that's out of your hands then you have no choice, but you can't then just write code that ignores Windows security. You will need to run your up under a Windows account that has admin privileges and then probably run it as an administrator as well. That means right-clicking the EXE and selecting the appropriate option. If you're using VS to debug/test then I think that running VS as an admin willk then run your app as an admin, but I'm not 100% sure about that.
-
Dec 8th, 2021, 07:21 AM
#9
Re: Procedure to delete certain files
Originally Posted by bknight
I'm not sure of the version but it was in Excel 2007, my current.
I had some nice help in developing a code to delete files:
They are in a sub-sub-sub folder in Program files.
The all have a specific extension.
I wish to retain all files less than two months old, deleting the rest.
There are many sub folders and I wanted to cover them all.
Code:
Sub DeleteOldFiles()
' Path of top folder
Const strFolder = "C:\Program Files (x86)\Tradestation 9.5\Scans"
Dim objFSO As Object
Dim objFolder As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)
Call DeleteFiles(objFolder)
End Sub
Sub DeleteFiles(objFolder As Object)
Dim objFile As Object
Dim objSubfolder As Object
' Check files
For Each objFile In objFolder.Files
If LCase(objFile.Name) Like "*.tsrslts" Then
If objFile.DateLastModified < Date - 60 Then
objFile.Delete
End If
End If
Next objFile
' Check subfolders
For Each objSubfolder In objFolder.SubFolders
Call DeleteFiles(objSubfolder)
Next objSubfolder
End Sub
I believe the code will work, except when the code attempts to execute objFile.Delete then I get a run time error 70 "permission denied"
I'm looking for a line to allow permissions.
That looks like VB6 code. VB.Net doesn't have Set statements. You might want to ask the mods to move this to the VB6 section.
-
Dec 8th, 2021, 09:10 AM
#10
Thread Starter
Junior Member
Re: Procedure to delete certain files
So there is no code to override the admin control?
Running an account with admin control has issues as well for hackers into your system totally non-protected.
-
Dec 8th, 2021, 09:11 AM
#11
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by Niya
That looks like VB6 code. VB.Net doesn't have Set statements. You might want to ask the mods to move this to the VB6 section.
OK will do.
-
Dec 8th, 2021, 09:13 AM
#12
Thread Starter
Junior Member
Re: Procedure to delete certain files
Mods would you move this to VB6, please.
-
Dec 8th, 2021, 09:29 AM
#13
Re: Procedure to delete certain files
You are lucky I spotted your post. The best way to let moderators know about something like this is to use the "Report this post" link (triangle icon) at the bottom of a post.
Based on the first line:
Originally Posted by bknight
I'm not sure of the version but it was in Excel 2007, my current.
...I'm assuming that you are using the VB Editor inside Excel, in which case you are using VBA rather than VB (VBA is similar to VB6, but there are enough differences to cause problems)
I have therefore moved this thread to our Office Development/VBA forum.
-
Dec 8th, 2021, 10:01 AM
#14
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by si_the_geek
You are lucky I spotted your post. The best way to let moderators know about something like this is to use the "Report this post" link (triangle icon) at the bottom of a post.
Based on the first line:
...I'm assuming that you are using the VB Editor inside Excel, in which case you are using VBA rather than VB (VBA is similar to VB6, but there are enough differences to cause problems)
I have therefore moved this thread to our Office Development/VBA forum.
That is correct.
-
Dec 8th, 2021, 10:43 AM
#15
Re: Procedure to delete certain files
I remember when i had to do something along those lines: Accessing a Folder from code, which the user normaly wouldn't have access to.
I remember using something like Impersonate and/or LogonUser-API
Would have to dig really deep if i can find it
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 9th, 2021, 04:33 AM
#16
Re: Procedure to delete certain files
search in vb6 forum for "runas administrator" which should return some code that can elevate your code, but will require an administrator password to be entered each time
NOTE even if you are logged in as an administrator user you still will require to elevate to delete the files
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 9th, 2021, 10:23 AM
#17
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by westconn1
search in vb6 forum for "runas administrator" which should return some code that can elevate your code, but will require an administrator password to be entered each time
NOTE even if you are logged in as an administrator user you still will require to elevate to delete the files
Thanks for the tip, however I have spent thew last two hours attempting to patch in the resolved threads and nothing compiles.
I'm still stumped as to how to bypass the admin permissions.
-
Dec 9th, 2021, 12:30 PM
#18
Re: Procedure to delete certain files
Originally Posted by bknight
Thanks for the tip, however I have spent thew last two hours attempting to patch in the resolved threads and nothing compiles.
I'm still stumped as to how to bypass the admin permissions.
Not sure bypass is the correct choice of word there. You will need to run your application with Admin rights or your application would need to be written to elevate the permissions it runs with. Either way you are still going to require the correct permissions.
-
Dec 9th, 2021, 12:55 PM
#19
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by PlausiblyDamp
Not sure bypass is the correct choice of word there. You will need to run your application with Admin rights or your application would need to be written to elevate the permissions it runs with. Either way you are still going to require the correct permissions.
Perhaps bypass wasn't the correct verb. But to have the procedure run with no run stops with error message "Permission Denied"
-
Dec 9th, 2021, 02:22 PM
#20
Re: Procedure to delete certain files
-
Dec 9th, 2021, 04:54 PM
#21
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
Thanks, but that opens another(?) instance of Excel
-
Dec 9th, 2021, 06:23 PM
#22
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
This did give me an idea to search different words and phrases.
-
Dec 9th, 2021, 07:14 PM
#23
Re: Procedure to delete certain files
Thanks, but that opens another(?) instance of Excel
Yes, it does. The whole point is that you cannot run an Excel macro to delete files in the desired location unless Excel has been Run As Administrator.
-
Dec 9th, 2021, 08:40 PM
#24
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
Yes, it does. The whole point is that you cannot run an Excel macro to delete files in the desired location unless Excel has been Run As Administrator.
The desired location C:\Program Files (x86)\Tradestation 9.5\Scans or any location?
-
Dec 10th, 2021, 02:13 AM
#25
Re: Procedure to delete certain files
Originally Posted by jdc2000
Yes, it does. The whole point is that you cannot run an Excel macro to delete files in the desired location unless Excel has been Run As Administrator.
That's plain wrong.
As i mentioned i have coded a VBA macro in Excel where the User executing Excel on his machine had to get Read/Write-Access to a restricted Folder on our File-Server (for which that User didn't have access).
I had to use the mentioned API's in my Post above.
But i would really have to start studying archeology to find that code.......
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 10th, 2021, 07:26 AM
#26
Re: Procedure to delete certain files
check this thread
https://www.vbforums.com/showthread....ighlight=runas
if you elevate as an admin user you should not require password, but standard user will
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 10th, 2021, 09:50 AM
#27
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by Zvoni
That's plain wrong.
As i mentioned i have coded a VBA macro in Excel where the User executing Excel on his machine had to get Read/Write-Access to a restricted Folder on our File-Server (for which that User didn't have access).
I had to use the mentioned API's in my Post above.
But i would really have to start studying archeology to find that code.......
I do wish you to find your archeological hat.
-
Dec 10th, 2021, 09:54 AM
#28
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by westconn1
Since there is no code in that thread, just words I'm at a worse condition than asking for some code to allow my deletion code to delete files in a UAC area.
But thanks for the time.
-
Dec 11th, 2021, 11:12 AM
#29
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
Ad I tried the code is a partial success. The code does open another(?) instance of Excel but hidden so change the visible parameter to true. Upon running the code, it will not execute a sub from the previous instance, I will attempt to check whether the code deletes files later today or tomorrow. The jury is still out.
-
Dec 13th, 2021, 04:30 AM
#30
Re: Procedure to delete certain files
i have tested the code below from excel, works as desired, BUT you will have to click yes to the UAC dialog, and i believe as a standard user, have to put an admin password
Code:
Private Declare Function ShellExecute Lib "shell32" _
Alias "ShellExecuteA" ( _
ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub delfiles()
Open "c:\temp\delfiles.bat" For Output As 1
Print #1, "del /q ""C:\Program Files (x86)\xmas\*.*"""
Close 1
ShellExecute Application.hWnd, "runas", "c:\temp\delfiles.bat", "", "", vbNormalFocus
End Sub
change folder paths etc as required
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 13th, 2021, 07:55 AM
#31
Thread Starter
Junior Member
Re: Procedure to delete certain files
Um This sounds recursive. Opens delfiles.bat which is inside the Sub. Aside from that it deletes no files that are on the hd, or am I totally missing something?
-
Dec 13th, 2021, 08:57 AM
#32
Thread Starter
Junior Member
Re: Procedure to delete certain files
Originally Posted by jdc2000
After some extensive testing and rewriting commands, I have made this work. As I indicated the test sub opens another instance of Excel and I assume it to be as Administrator. After that is accomplished then run my original code to delete the older files.
Code:
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOWNORMAL = 1
Sub Test()
ShellExecute 0, "runas", "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE", Command, vbNullString, SW_SHOWNORMAL
Dim xl As Excel.Application
Set xl = GetObject(, "Excel.Application")
With xl
.Visible = True
.Workbooks.Open ThisWorkbook.FullName
End With
End Sub
Sub DeleteOldFiles() ' Delete scan results older than two months
' Path of top folder
Const strFolder = "C:\Program Files (x86)\Tradestation 9.5\Scans"
Dim objFSO As Object
Dim objFolder As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)
Call DeleteFiles(objFolder)
End Sub
Sub DeleteFiles(objFolder As Object)
Dim objFile As Object
Dim objSubfolder As Object
' Check files
For Each objFile In objFolder.Files
If LCase(objFile.Name) Like "*.tsrslts" Then
If objFile.DateLastModified < Date - 60 Then
'objFile.Delete.Permission = True
objFile.Delete
End If
End If
Next objFile
' Check subfolders
For Each objSubfolder In objFolder.SubFolders
Call DeleteFiles(objSubfolder)
Next objSubfolder
End Sub
I thank all for the time, research and effort in aiding this request.
I'm not sure how to mark this as a final post to include a SOLVED notation.
-
Dec 14th, 2021, 05:32 AM
#33
Re: [RESOLVED] Procedure to delete certain files
Um This sounds recursive.
not recursive, just needs some button (or other event) to run the procedure whenever required
Opens delfiles.bat which is inside the Sub.
creates a bat file to whatever folder chosen, though this could be a permanent batch file that can be reused often, and no need to create in code
Aside from that it deletes no files that are on the hd
shellexecute runs the batch file elevated, which will delete whatever files are listed in the bachfile
i tested the code and files were deleted from a folder (xmas) that i created, for testing, below program files (86), without elevation i would get an error
if you use a permanent batch file the procedure code can be reduced to a single line, plus the API declaration, which must be at the top of the code module and not within a procedure
the content of the batch file here is a single line, but more instructions could be added, all of which would run elevated
you must change the paths in the batch file to exactly match your file system, also the location of the batch file must exist
you can also change the filespec for deletion to *.tsrslts in the batchfile
using another instance of excel seems to be overkill, but in hindsight i would probably use a .VBS file rather than a batch file as you original code could all be put in a .vbs file then run elevated
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 14th, 2021, 10:38 AM
#34
Thread Starter
Junior Member
Re: [RESOLVED] Procedure to delete certain files
Originally Posted by westconn1
not recursive, just needs some button (or other event) to run the procedure whenever required
creates a bat file to whatever folder chosen, though this could be a permanent batch file that can be reused often, and no need to create in code
shellexecute runs the batch file elevated, which will delete whatever files are listed in the bachfile
i tested the code and files were deleted from a folder (xmas) that i created, for testing, below program files (86), without elevation i would get an error
if you use a permanent batch file the procedure code can be reduced to a single line, plus the API declaration, which must be at the top of the code module and not within a procedure
the content of the batch file here is a single line, but more instructions could be added, all of which would run elevated
you must change the paths in the batch file to exactly match your file system, also the location of the batch file must exist
you can also change the filespec for deletion to *.tsrslts in the batchfile
using another instance of excel seems to be overkill, but in hindsight i would probably use a .VBS file rather than a batch file as you original code could all be put in a .vbs file then run elevated
I originally started with a batch file scheme, but you could fill an ocean with what I don't know about programming. A friend on a forum dealing with MS products gave me the VBA code, so I went with that. The program worked right up to the delete line where it failed "permission denied".
Let's start at the beginning.
The files that I wish to delete are located within two main sub-folders that contain another 44 sub-sub-folders. A deletion will cover ~1200 deletions.
The only files to be deleted will have a specific extension so deleting with *.tsrslts would be ok.
Further the only files to be deleted will be GT 60 days old.
Can a batch do those deletions in one operation? I simply don't know.
Whether another instance of Excel is opened is a bit of a misnomer, only one instance of Excel is running as I get an error message that the file is opened already, two instances would likely not give that warning. It may/not be overkill but it works.
Thanks for your input and time.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|