Why is this use of the "Dir" function not working as I intended?

Question

I'm trying to place the names of all of the filenames in a folder into a list on excel, so that there is one filename per cell.

However, using the code below, it writes in the number of files in the folder -1. So, if there are 4 files in the folder, it only puts in three of them.

This is my first time using "Dir", and I can't spot what I'm doing wrong for the life of me!

Here's the code:

Sub PrintFilesNames()

Dim PathToFolder As String
Dim file As String
Dim cellOutput As String

Dim i As Integer

i = 6

PathToFolder = ThisWorkbook.Sheets("PDP Comparison").Cells(5, 2).Text
file = Dir(PathToFolder)

While (Len(file) > 0)
    file = Dir
    cellOutput = PathToFolder + file
    ThisWorkbook.Sheets("PDP Comparison").Cells(i, 2).Value = cellOutput
    Debug.Print (file)
    i = i + 1
Wend


End Sub

score 1 · Accepted Answer · answered Apr 01 '14 at 13:29

It's replacing file on the first pass before you print it.

file = Dir(PathToFolder)

While (Len(file) > 0)    
    cellOutput = PathToFolder + file
    ThisWorkbook.Sheets("PDP Comparison").Cells(i, 2).Value = cellOutput
    Debug.Print (file)
    i = i + 1
    file = Dir
Wend

Here file(file = Dir) is just populated again at the end of the loop.

Why is this use of the "Dir" function not working as I intended?

1 Answers1