[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA  Object does not support  property or method

Posted on 2016-10-20
3
Medium Priority
?
82 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
3 Comments
 
LVL 35

Expert Comment

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

Accepted Solution

by:
Rgonzo1971 earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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