DIR command

[LearnMeExcel]

hi all
i read the help of this command, but i can't understand it,could any one help me to understand it with explain and Example, thank you

 

[VoG]

Does this help?

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[LearnMeExcel]

Sure peter all information from you help me always
from your code i understand that DIR use to search the file in folder and return it is name. IS THIS TRUE

and in your code why you use this line
MyFile = Dir


thank you peter

 

[VoG]

Yes, it lists the files in a folder.

After you have used Dir for the first time, calling Dir again returns the next file name.

 

[LearnMeExcel]

After you have used Dir for the first time, calling Dir again returns the next file name.

you mean to reset the Dir
MyFile = Dir

i sm sorry for reask but i can't get the idea

 

[VoG]

This

MyFile = Dir(MyFolder & "\*.xls")

gets the first file name.

This

MyFile = Dir

gets the next file name from the same folder or returns "" if there are no more files.

 

[LearnMeExcel]

Hi Peter
i make folder named "VoG Example"
and there is 10 files in it
Test (1).xlsx
Test (2).xlsx
Test (3).xlsx
Test (4).xlsx
Test (5).xlsx
Test (6).xlsx
Test (7).xlsx
Test (8).xlsx
Test (9).xlsx
Test.xlsx
when i run your macro i get this result
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Test (1).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Test (2).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Test (3).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Test (4).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Test (5).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Test (6).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Test (7).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Test (8).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Test (9).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Test.xlsx</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

when i add Debug.Print

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "D:\VoG Example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
Debug.Print Dir
    MyFile = Dir
Loop
End Sub

the result become
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Test (1).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Test (3).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Test (5).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Test (7).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Test (9).xlsx</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

and when i change the position of Debug like this

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "D:\VoG Example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile

    MyFile = Dir
Debug.Print Dir
Loop
End Sub

i get this result, Why ????
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Test (1).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Test (2).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Test (4).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Test (6).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Test (8).xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Test.xlsx</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

why ???

 

[VoG]

Because Debug.Print Dir is calling the Dir function again, skipping to the next file. Try

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "D:\VoG Example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
Debug.Print MyFile
    MyFile = Dir
Loop
End Sub

 

[LearnMeExcel]

Because Debug.Print Dir is calling the Dir function again, skipping to the next file. Try

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "D:\VoG Example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
Debug.Print MyFile
    MyFile = Dir
Loop
End Sub

Thank you peter, it is clearing now