0

im trying to get file name with csv extension , but when i try to debug.print it always print the .csv file and the .txt file

im search the file in the folder and subfolder

heres the code

Sub LoopAllSubFolders(ByVal folderPath As String)

Dim fileName As String
Dim fullFilePath As String
Dim numFolders As Long
Dim folders() As String
Dim i As Long

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.*", vbDirectory)

While Len(fileName) <> 0

    If Left(fileName, 1) <> "." Then

        fullFilePath = folderPath & fileName

        If (GetAttr(fullFilePath) And vbDirectory And Not (fullFilePath Like "*csv*")) = vbDirectory Then
            ReDim Preserve folders(0 To numFolders) As String
            folders(numFolders) = fullFilePath
            numFolders = numFolders + 1
        Else
            'Insert the actions to be performed on each file
            'This example will print the full file path to the immediate window
            Debug.Print folderPath & fileName
        End If

    End If

    fileName = Dir()

Wend

For i = 0 To numFolders - 1

    LoopAllSubFolders folders(i)

Next i

End Sub

The code i get from website and i add not like "*csv*"

perhaps someone can correcting the code,

thankyou

braX
  • 11,506
  • 5
  • 20
  • 33
trytocode
  • 393
  • 4
  • 22
  • 1
    Does this answer your question? [VBA macro that search for file in multiple subfolders](https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders) – braX Dec 23 '19 at 07:02

2 Answers2

0

You can use Dir(folderPath & "*.csv") to find all csv files in folderPath.

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.csv")

While Len(fileName) <> 0

    If Left(fileName, 1) <> "." Then
        fullFilePath = folderPath & fileName
        ReDim Preserve folders(0 To numFolders) As String
        folders(numFolders) = fullFilePath
        numFolders = numFolders + 1
    End If

    fileName = Dir()
Wend

new edition: use FileSystemObject

Use FileSystemObject to get names of subfolders then get all csv files in each subfolder

Dim fso as object, mainFolder as object, subFolder as object, file

set fso = createobject("Scripting.FileSystemObject")
if fso.FolderExists("folderPath") Then
    set mainFolder = fso.getfolder("folderPath")
else
    exit sub
end if

for each subFolder in mainFolder.subfolders
    for each file in subFolder.Files
        if file.name like "*.csv" then
            ReDim Preserve folders(0 To numFolders) As String
            folders(numFolders) = file
            numFolders = numFolders + 1
        end if
    next file
next subFolder
PaichengWu
  • 2,649
  • 1
  • 14
  • 28
  • i think it makes sense, but the folderPath has only subfolders , and inside the subfolders will have .txt and .csv files , thats why if i use the folderPath & "*.csv*" files, it wont work – trytocode Dec 23 '19 at 06:53
0

My suggestion:

    fullFilePath = folderPath & fileName

   If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
        ReDim Preserve folders(0 To numFolders) As String
        folders(numFolders) = fullFilePath
        numFolders = numFolders + 1
   Else
     If Right(fileName, 4) = ".csv" Then 'new line
       'Insert the actions to be performed on each file
        'This example will print the full file path to the immediate window
        Debug.Print folderPath & fileName
    End If
Zeusz
  • 1