Loop through files and use the filename without extension

[desolatori]

Hello everyone,

My problem is the following:
I got a directory with a set of .csv files. I want to loop through these files and everytime take the name of each file without extension and put it in a cell in Sheet1(the cell has to increase by 1)
So lets say: 01.csv takes the 01 and place it in C12, 02.csv -> C13 etc until it finds and empty row.
My goal is to look like the following:

Cells || Number of Csv ||| Voltage
C12 || 01 ||| 2kV
C13 || 02 ||| 4kV
C14 || 03 ||| 6kV
C15 || 04 ||| 8kV
Empty row===============================
Cells || Number of Csv ||| Voltage
C18 || 05 ||| 2kV
C19 || 06 ||| 4kV
C20 || 07 ||| 6kV
C21 || 08 ||| 8kV

I use the following code for the first group 01 to 04. I guess the same has to be used for C18:C21

Please Login or Register  to view this content.

With the above code my problem is that it pastes always the 01. So i guess i need something like file=file+1 to take the next file but obviously this doesn't work :P
Thank you for your help in advance !

[millz]

Made some amendments, see if this helps?

Please Login or Register  to view this content.

[desolatori]

Thank you for the quick reply. Yeah it works if there are 8 files in the directory. If there are more it crashes the excel. Can this work with a more dynamic way and perform the same independently from the number of csv which are inside the directory?

Thank you again!

[millz]

How about this?

Please Login or Register  to view this content.

[desolatori]

It works like a charm, Thank you