Solved

VBA  Object does not support  property or method

Posted on 2016-10-20
3
58 Views
Last Modified: 2016-10-20
I copies this code from another thread by Bill Prew and modified it to work in Excel vba

it stops  at line  For Each objCheckFolder In objSubFolder.Folders  with error  Object does not support  property or method

any idea how to make this work and if there is any other issue to spot? thanks

Option Explicit



Sub macro()
    ' Local variables
    
Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")
    Dim objFolder
    Dim objFile
    Dim objSubFolder
    Dim objCheckFile
    Dim objCheckFolder
    Dim Flag
    Dim strFolder As String
strFolder = "C:\Users\James\Downloads\Myfolder\"
    ' Acess the folder
    Set objFolder = objFSO.GetFolder(strFolder)

    ' Check each file in this folder
    For Each objFile In objFolder.Files

        ' If file is old enough delete it
        If DateDiff("h", objFile.DateCreated, Now()) >= 24 Then
            objFile.Delete True
        End If

    Next

    ' Now check all folders
    For Each objSubFolder In objFolder.Subfolders

        If DateDiff("h", objSubFolder.DateCreated, Now()) >= 24 Then
            ' Assume we can delete it
            Flag = "yes"

            ' Any new files that prevent deletion
            For Each objCheckFile In objSubFolder.Files
                If DateDiff("h", objCheckFile.DateCreated, Now()) < 24 Then
                    Flag = "no"
                End If
            Next

            ' Any new folders that prevent deletion
            For Each objCheckFolder In objSubFolder.Folders
                If DateDiff("h", objCheckFolder.DateCreated, Now()) < 24 Then
                    Flag = "no"
                End If
            Next

            ' Delete folder if no newer items found
            If Flag = "yes" Then
                objSubFolder.Delete True
            End If
        End If

    Next

End Sub

Open in new window

0
Comment
Question by:excelismagic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 41851638
I can't replicate the error, where are you running this code from?
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41851648
Hi,

pls try
For Each objCheckFolder In objSubFolder.SubFolders

Open in new window

Regards
0
 
LVL 3

Author Closing Comment

by:excelismagic
ID: 41851827
thanks Rgonzo. after i replace the line with your code, it worked like a charm
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question