Solved

VBA  Object does not support  property or method

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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;…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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