Solved

VBA  Object does not support  property or method

Posted on 2016-10-20
3
43 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 33

Expert Comment

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

839 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