Results 1 to 34 of 34

Thread: [RESOLVED] Procedure to delete certain files

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Resolved [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.

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    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?

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    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

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    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

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by Shaggy Hiker View Post
    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by .paul. View Post
    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.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Procedure to delete certain files

    Quote Originally Posted by bknight View Post
    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.

  9. #9
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Procedure to delete certain files

    Quote Originally Posted by bknight View Post
    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.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    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.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by Niya View Post
    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.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Mods would you move this to VB6, please.

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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:
    Quote Originally Posted by bknight View Post
    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.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by si_the_geek View Post
    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.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by westconn1 View Post
    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.

  18. #18
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Procedure to delete certain files

    Quote Originally Posted by bknight View Post
    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.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by PlausiblyDamp View Post
    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"

  20. #20
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Procedure to delete certain files


  21. #21

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    Thanks, but that opens another(?) instance of Excel

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    This did give me an idea to search different words and phrases.

  23. #23
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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.

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    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?

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    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

  26. #26
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by Zvoni View Post
    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.

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by westconn1 View Post
    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
    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.

  29. #29

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    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.

  30. #30
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  31. #31

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    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?

  32. #32

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: Procedure to delete certain files

    Quote Originally Posted by jdc2000 View Post
    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.

  33. #33
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  34. #34

    Thread Starter
    Junior Member
    Join Date
    Dec 2021
    Posts
    19

    Re: [RESOLVED] Procedure to delete certain files

    Quote Originally Posted by westconn1 View Post
    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
  •  



Click Here to Expand Forum to Full Width