VBA code No any error but files not dispalyed in excel sheet

[senthilvelayutham]

Sub CheckFolders()

' Define your folder paths and corresponding cells
Dim FoldersAndCells As Variant

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of AOF (Alarm Off)\2023", "b2"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\1-Jan", "B5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\2-Feb", "C5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\1-Jan", "b11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\3-Mar", "D5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\4-Apr", "E5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\5-May", "F5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\6-Jun", "G5"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\7-Jul", "H5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\8-Aug", "I5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\9-Sep", "J5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\10-Oct", "K5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\11-Nov", "L5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 100 Reading Sheet\2023\12-Dec", "M5"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\2-Feb", "C11"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\3-Mar", "D11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\4-Apr", "E11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\5-May", "f11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\6-Jun", "G11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\7-Jul", "H11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\8-Aug", "i11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\9-Sep", "J11"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\10-Oct", "K11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\11-Nov", "L11"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 200 & 300 Reading Sheet\2023\12-Dec", "m11"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\7-Jul", "H16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 400 Reading Sheet\2023\8-Aug", "I16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 400 Reading Sheet\2023\9-Sep", "J16"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 400 Reading Sheet\2023\10-Oct", "K16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 400 Reading Sheet\2023\11-Nov", "L16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 400 Reading Sheet\2023\12-Dec", "M16"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\1-Jan", "B16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\2-Feb", "C16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\3-Mar", "D16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\4-Apr", "E16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\5-May", "F16"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 400 Reading Sheet\2023\6-Jun", "G16"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\1-Jan", "B21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\2-Feb", "c21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\3-Mar", "D21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\4-Apr", "E21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\6-Jun", "G21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\7-May", "F21"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\7-Jul", "H21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 500 Reading Sheet\2023\8-Aug", "I21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 500 Reading Sheet\2023\10-Oct", "K21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 500 Reading Sheet\2023\11-Nov", "L21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of Area 500 Reading Sheet\2023\12-Dec", "M21"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area 500 Reading Sheet\2023\9-Sep", "J21"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\7-Jul", "H26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\8-Aug", "I26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\9-Sep", "J26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\10-Oct", "K26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\11-Nov", "L26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\12-Dec", "M26"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\1-Jan", "B26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\2-Feb", "c26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\3-Mar", "D26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\4-Apr", "E26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\5-May", "F26"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area Console Reading Sheet\Console Reading Sheet 2023\6-Jun", "G26"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\1-Jan", "B32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\2-Feb", "C32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\3-Mar", "D32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\4-Apr", "E32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\5-May", "F32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\6-Jun", "G32"))

FoldersAndCells = Array(Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\7-Jul", "H32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\8-Aug", "I32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\9-Sep", "J32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\10-Oct", "K32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\11-Nov", "L32"), Array("\\fipr2\PR-POX\OPERATION\0-OPERATION\30-Record of Routine checks\Record of area RU 900 Reading Sheet\2023\12-Dec", "M32"))

' Get a FileSystemObject
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

' Loop through each folder
Dim i As Integer
For i = LBound(FoldersAndCells) To UBound(FoldersAndCells)
' Get the folder object
Dim folder As Object
Set folder = fso.GetFolder(FoldersAndCells(i)(0))

' Initialize row number for writing file names
Dim rowNumber As Integer
rowNumber = 2 ' Start from row 2 assuming row 1 is used for headers

' Write the file names to the specified cell and its below cells
WriteFileNames folder, ThisWorkbook.Sheets("2023").Range(FoldersAndCells(i)(1)), rowNumber
Next i

End Sub

Sub WriteFileNames(folder As Object, startCell As Range, ByRef rowNumber As Integer)

' Write the name of each file in the current folder to the cells
Dim file As Object
For Each file In folder.Files
startCell.Offset(rowNumber - 1, 0).Value = file.Name
rowNumber = rowNumber + 1
Next file

' Loop through each subfolder in the current folder
Dim subfolder As Object
For Each subfolder In folder.Subfolders
' Recursively write the names of files in the subfolder to the cells
WriteFileNames subfolder, startCell, rowNumber
Next subfolder

End Sub


Above code generated to pick up multiple file names from multiple folders with subfolders and write to Excel sheet whenever execute check folder action button

but files not pick up and written in excel sheet as expected but it works only first line of coding if I disable other code lines

No error was detected by system

please help me to solve this

[Tajan]

Check this:

Please Login or Register  to view this content.

[senthilvelayutham]

Thank you for your great reply

I encountered error like this

Sub or funtion not defined and pointing out on WriteFile Names

Could you please help me on this , I am just buddy in VBA programming

[senthilvelayutham]

Sorry for my mistake

Second time I copied full code it pick up the file details to my excel sheet but parallel I got runtime error 76: path not found in VBA editor page and error Pointing out on

Set folder = fso.GetFolder(FoldersAndCells(j)(i)(0))

ask to debug

[Tajan]

Check if you wrote the paths correctly in the code. I added a message in case the path does not exist.

Please Login or Register  to view this content.

[senthilvelayutham]

Dear Mr. Tajan

Great work, Loads of thanks and appreciated , Well and good work, Code work well thank you so much

-Senthilvelayutham