none
Using an Excel cell to determine if a folder is empty RRS feed

  • Question

  • Is it possible to use an Excel cell to determine if a folder is empty via VBA or function?
    Wednesday, June 12, 2019 1:59 AM

All replies

  • Novice
    Re: function for files

    Code goes in a standard module
    When using the function in a cell, the file path must be enclosed in quote marks.
    Note:  This function ignores any sub-folders included in the folder.
    Example:  =FileCount("E:\Other Releases\3Com\Engr")
    '---

       

    Public Function FileCount(ByRef folderspec As Variant)
       Dim fso As Object
       Dim f   As Object
       Dim fc  As Object
       Dim s   As Long

       Set fso = CreateObject("Scripting.FileSystemObject")
       Set f = fso.GetFolder(folderspec)
       Set fc = f.Files
       s = fc.Count
       FileCount = s
    End Function
    '---


    Custom_Functions add-in (19 new functions)
    Download from MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    Wednesday, June 12, 2019 5:37 AM
  • You may try something like this...

    Function getFileCount(FolderPath As String)
    With CreateObject("Scripting.FileSystemObject")
        If Not .folderexists(FolderPath) Then
            getFileCount = "Folder doesn't exist."
        Else
            getFileCount = .getfolder(FolderPath).Files.Count
        End If
    End With
    End Function

    Then assuming your folder path is in A2, try the function on the sheet like below...

    =getFileCount(A2)


    Subodh Tiwari (Neeraj) sktneer

    Wednesday, June 12, 2019 4:09 PM
  • I keep getting a #NAME error.  Do I need to set a reference?
    Wednesday, June 12, 2019 8:07 PM
  • Novice,
    Re:  error received

    1.  FileSystemObject is not available on a MAC.
    -or-
    2.  Office 365 might not have it; am uncertain ?
    -or-
    3.  Advise what line error occurs on and
         what your cell formula looks like and
         confirm code is in a Standard module not the module behind the worksheet.

    Wednesday, June 12, 2019 8:54 PM
  • I keep getting a #NAME error.  Do I need to set a reference?

    #NAME error simply indicates that the formula is not available or not recognized by the Excel.

    Where did you place that User Defined Function?

    You are supposed to place this Function on a Standard Module like Module1.

    To do so, follow these steps...

    1) Open the VB Editor by pressing Alt+F11.

    2) On VB Editor ribbon --> Insert --> Module --> and paste the Function into the opened code window.

    3) Close VB Editor and save your file as Macro-Enabled Excel Workbook.

    Now you may use this UDF on the worksheet without an issue.


    Subodh Tiwari (Neeraj) sktneer


    Thursday, June 13, 2019 12:05 AM
  • Subodh Tiwari (Neeraj) sktneer,

    I have successfully gotten the first function to work; however, your code result in #value now.  Any suggestions?  Thank you for your time and patience.

    Tuesday, June 18, 2019 3:03 PM
  • If the first function worked for you, please feel free to go ahead with that one.

    Subodh Tiwari (Neeraj) sktneer

    Tuesday, June 18, 2019 3:17 PM
  • What if I am attempting to look into files on a sharepoint for my work, would this work?
    Tuesday, June 18, 2019 7:40 PM
  • Novice,
    Re:  would this work?

    Probably.
    Also see:  https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-49b833c0-873b-48d8-8bf2-c1c59a628534?CorrelationId=bdb880d0-c445-4239-a3df-8c20eea479eb&ui=en-US&rs=en-US&ad=US

    Toward the bottom of the page click:  "Unsupported features"
    '---

    Custom_Functions add-in (19 new functions)
    Download from MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Tuesday, June 18, 2019 8:47 PM