Testing if various folders are empty

[Zunit]

Hi,

I have around 60 folders on a shared network that various people are going to save files into. I need a way to check if they have saved the files in their folder. So I tried the below code and it worked:

Strpath = "P:\SARS\2017 Financial year\1. July\Botswana\"
If Right(Strpath, 1) <> "\" Then
        Strpath = Strpath & "\"
    End If
    If Dir(Strpath & "*.*") = "" Then
        MsgBox "The folder doesn't contain files"
    Else
        MsgBox "The folder does contain files"
    End If

Now I am trying to link parts of the Strpath to cells in the worksheet so that I can change the path from the sheet and check a specific folder I want. I tried the below but its not working:

Name = ThisWorkbook.Sheets("Sheet1").Range("O2").Value
Name1 = ThisWorkbook.Sheets("Sheet1").Range("O3").Value
Strpath = "P:\SARS\2017 Financial year\" & Name & Name1
If Right(Strpath, 1) <> "\" Then
        Strpath = Strpath & "\"
    End If
    If Dir(Strpath & "*.*") = "" Then
        MsgBox "The folder doesn't contain files"
    Else
        MsgBox "The folder does contain files"
    End If

The values in O1 is 1. July and in O2 it is \Botswana\

What am I doing wrong?

Also is there a way I could set it up so that the result (message in msgbox) gets pasted in a specific cell rather than doing a message box?

Please assist.

 

[ParamRay]

Here are two functions you can use to count files or folders. You can use them in a worksheet cell, as in =COUNTFILES("C:\Users\UserName\Documents")

Public Function COUNTFILES(ByVal folder_path As String, _
                  Optional include_subfolders As Boolean = False _
                ) As Variant
  
  Dim objSubfolder As Object
  Dim objFileSys As Object
  Dim objFolder As Object
  Dim lngFileCount As Long
  
  On Error GoTo ErrHandler
  If Right(folder_path, 1) <> "\" Then
    folder_path = folder_path & "\"
  End If
  
  Set objFileSys = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objFileSys.GetFolder(folder_path)
  lngFileCount = objFolder.Files.Count
  
  If include_subfolders Then
    For Each objSubfolder In objFolder.SubFolders
      lngFileCount = lngFileCount + COUNTFILES(objSubfolder.Path, True)
    Next objSubfolder
  End If
  
  COUNTFILES = lngFileCount
  
ExitProc:
  Set objSubfolder = Nothing
  Set objFileSys = Nothing
  Set objFolder = Nothing
  Exit Function
  
ErrHandler:
  If Err.Number = 70 Then 'Permission denied
    Err.Clear
    Resume Next
  Else
    COUNTFILES = CVErr(xlErrValue)
    Resume ExitProc
  End If
End Function


Public Function COUNTFOLDERS(ByVal folder_path As String, _
                    Optional include_subfolders As Boolean = False _
                ) As Variant
  
  Dim objSubfolder As Object
  Dim objFileSys As Object
  Dim objFolder As Object
  Dim lngFolderCount As Long
  
  On Error GoTo ErrHandler
  If Right(folder_path, 1) <> "\" Then
    folder_path = folder_path & "\"
  End If
  
  Set objFileSys = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objFileSys.GetFolder(folder_path)
  lngFolderCount = objFolder.SubFolders.Count
  
  If include_subfolders Then
    For Each objSubfolder In objFolder.SubFolders
      lngFolderCount = lngFolderCount + COUNTFOLDERS(objSubfolder.Path, True)
    Next objSubfolder
  End If
  
  COUNTFOLDERS = lngFolderCount
  
ExitProc:
  Set objSubfolder = Nothing
  Set objFileSys = Nothing
  Set objFolder = Nothing
  Exit Function
  
ErrHandler:
  If Err.Number = 70 Then 'Permission denied
    Err.Clear
    Resume Next
  Else
    COUNTFOLDERS = CVErr(xlErrValue)
    Resume ExitProc
  End If
End Function

 

[Zunit]

Hi,

Thanks for your help. Your code is way too advanced for me, I can't even get VBA to run it, let alone understand it

When I try run it, I get a Expected Sub End error. Not sure what that is. Also where in this code do I specify the folder path? Should I replace each "folder_path" with the path or add it at the top?

 

[ParamRay]

Hi Zunit.
Copy and paste the above code into a separate code module, on its own. After that, use the function in a worksheet cell. For example, if you have a folder path in cell A1, you can enter this formula in cell B1:
=COUNTFILES(A1)

 

[Zunit]

Wow! That is brilliant. Didn't know you could create functions in excel using VBA then use them on the worksheet.