When opening book, write all filenames of the directory to cells

Hello everyone

I am new to VBA and face a problem I think does not work without it anymore.

The situation is as follows:

Working directory: "C:\work"

Main file (only file open): "main.xlsx"

Additional files (growing every week to around 100):
"A.xlsx"
"B.xlsx"
"C.xlsx"
...

What do I want?

I want that whenever I start main.xlsx, the current filenames of the files are written into specific cells of a sheet in main.xlsx.

It would be nice to omit "main.xlsx" in the process.

I found a fast looping code on stackoverflow.com:

Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("c:\testfolder\*test*")
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Sub

Maybe there is someone who could help me to make this work?

Thank you very much!
Regards

Please Login or Register  to view this content.