CntrlAltDel
New Member
- Joined
- Oct 26, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello
My goal is to have a table where Column A is a independent entity, Column B is a number dependent on the number of files from a certain folder, Column C is the directory of the certain folder.
____
For vizualization aid you can Imagine:
Column A is a store (ex: Portuguese Store)
Column C is a file where every individual sale is stored as an excel document
Columb B is the cell that will reveal the number of excel files (sales) inside the directory specified in column C
Q:
Trying to understand how I can add more stores and directories inside the same code
____
I have a innitial draft for this but I can only get the number of files for one directory, if I try to reuse the variable to get the number of files for the following directories I find a bug.
Draft:
Sub CountFiles()
Dim folder_path As String
Dim strType As String
Dim totalfiles As Variant
folder_path = Worksheets("Files Count").Cells(2, 3).Value
If Right(folder_path, 1) <> "\" Then folder_path = folder_path & "\"
totalfiles = Dir(folder_path & strType)
Dim i As Integer
While (totalfiles <> "")
i = i + 1
totalfiles = Dir
Wend
Worksheets("Files Count").Cells(2, 2).Value = i
End Sub
How can I edit this code so that I can add as many directories and results as I need?
My goal is to have a table where Column A is a independent entity, Column B is a number dependent on the number of files from a certain folder, Column C is the directory of the certain folder.
____
For vizualization aid you can Imagine:
Column A is a store (ex: Portuguese Store)
Column C is a file where every individual sale is stored as an excel document
Columb B is the cell that will reveal the number of excel files (sales) inside the directory specified in column C
Q:
Trying to understand how I can add more stores and directories inside the same code
____
I have a innitial draft for this but I can only get the number of files for one directory, if I try to reuse the variable to get the number of files for the following directories I find a bug.
Draft:
Sub CountFiles()
Dim folder_path As String
Dim strType As String
Dim totalfiles As Variant
folder_path = Worksheets("Files Count").Cells(2, 3).Value
If Right(folder_path, 1) <> "\" Then folder_path = folder_path & "\"
totalfiles = Dir(folder_path & strType)
Dim i As Integer
While (totalfiles <> "")
i = i + 1
totalfiles = Dir
Wend
Worksheets("Files Count").Cells(2, 2).Value = i
End Sub
How can I edit this code so that I can add as many directories and results as I need?