DIR command

LearnMeExcel

Well-known Member
Joined
Aug 11, 2009
Messages
728
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Does this help?

Code:
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

Well-known Member
Joined
Aug 11, 2009
Messages
728
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Well-known Member
Joined
Aug 11, 2009
Messages
728

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
This

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

gets the first file name.

This

Code:
MyFile = Dir

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

LearnMeExcel

Well-known Member
Joined
Aug 11, 2009
Messages
728

ADVERTISEMENT

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
Code:
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
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Because Debug.Print Dir is calling the Dir function again, skipping to the next file. Try

Code:
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

Well-known Member
Joined
Aug 11, 2009
Messages
728
Because Debug.Print Dir is calling the Dir function again, skipping to the next file. Try

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top