CODEWITHSUNDEEP
vbaexcelmacros
whydoieven
whydoieven

Reputation: 599

Get folder path from sheet to count files inside that folder in Excel VBA

I have the following code which returns the number of files in a folder. 

Sub sample()

    Dim FolderPath As String, path As String, count As Integer
    FolderPath = "C:\Documents and Settings\Santosh\Desktop"

    path = FolderPath & "\*.xls"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    Range("Q8").Value = count
    'MsgBox count & " : files found in folder"
End Sub

I need to parametrize FolderPath as an input in the excel sheet itself so that I enter the folder path in Cell A1 and get the no of files in B1. I'm pretty new to Excel Macros - excuse if this is very basic. How can I do this?

Upvotes: 0

Views: 2905

Answers (2)

Ambrish Pathak
Ambrish Pathak

Reputation: 3968

You can send folderPath string from the cell A1 in the following way:

Sub sample()

    Dim FolderPath As String, path As String, count As Integer
    FolderPath = Range("A1")

    path = FolderPath & "\*.xls"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    Range("B1").Value = count
    MsgBox count & " : files found in folder"

End Sub

Put your folder path in cell A1 and run the macro.

you will get count in B1 cell

EDIT

For looping over all folder Paths filled in column A1,A2,A3... and getting count in corresponding cell that is B1,B2,B3...Proceed like below:

Get total rows in which folder path has been filled

TotalRows = Range("A" & Rows.count).End(xlUp).Row

Then loop from 1 to total Rows filled in column A.

Sub sample()

    Dim FolderPath As String, path As String, count As Integer
    Dim TotalRows As Integer

    TotalRows = Range("A" & Rows.count).End(xlUp).Row

    For i = 1 To TotalRows
    count = 0
    FolderPath = Range("A" & i)

    path = FolderPath & "\*.xls"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    Range("B" & i).Value = count
    'MsgBox count & " : files found in folder"

    Next i
End Sub

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Considering your macro is working fine all you need to do is to convert your Sub into Function in this way:

Function FileCounter(FolderPath as String)

    Dim path As String, count As Integer

    path = FolderPath & "\*.xls"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    FileCounter = count

End Function

Next, in Excel do as required and type in A1 folder path and in B1 =FileCounter(A1)

Please note that you get number of files with .xls extension only but not .xlsx, .xlsm and others. To include all Excel files in results you need to change extension patern to \*.xls*.

Upvotes: 0

Related Questions