File List skipping First File

DMROD

New Member
Joined
Jul 23, 2010
Messages
6
The following code return a list of files & file path in the directory except for the frist file in directory, but it adds to the end of the list one file path without file. Total lines in list equals number of Files in directory. There is a sub directory in the directory which I do not need in the list nor it contents. What I am missing?

Thank you
David


strDir = ThisWorkbook.Path

Let strName = Dir(strDir & "\*.*")
Do While strName <> vbNullString
Let j = j + 1
' Let strArr(i, 1) = strDir & "\" & strName
Let strName = Dir()
'Sheets("List").Cells(j, 1) = strArr
Sheets("List").Cells(j, 1) = strDir & "\" & strName
Loop
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,732
Office Version
365
Platform
Windows
David

Are you sure the index of the file list starts at 1?

Perhaps you should move j=j+1?
 

DMROD

New Member
Joined
Jul 23, 2010
Messages
6
Norie

I inserted code before the loop to set j = 0 prior to starting the loop. And tried moving the j = j+1 line within the loop. With no success. Any other suggestions.

Thank you
David

Code:
strDir = ThisWorkbook.Path
    Let j = 0
    Let strName = Dir$(strDir & "\*.*")
    Do While strName <> vbNullString
        Let j = j + 1
 
        Let strName = Dir$()
 
        Sheets("List").Cells(j, 1) = strDir & "\" & strName
        Loop
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,732
Office Version
365
Platform
Windows
Oops, my bad - I misread your code.

It thought you were using another method to get the fiile list, not just Dir.

I think what you need to do is move the line of code that gets the next filename after the line that puts the current filename on the worksheet.

Your very first Dir should get the first filename.

Code:
    strDir = ThisWorkbook.Path
    
    strName = Dir$(strDir & "\*.*") ' get first filename - if there is one    
    
    Do While strName <> vbNullString
 
        j = j + 1
 
        Sheets("List").Cells(j, 1) = strDir & "\" & strName
 
        strName = Dir$() ' get next filename
        
    Loop
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,997
Messages
5,508,671
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top