loop through folders vba

Question

Sub CheckandSend()
    Dim strfile As String
    Dim ws As Worksheet 'make sure to define a sheet
    Set ws = ThisWorkbook.Worksheets("RFQ")
    Sheets("RFQ").Select
    Dim Sourcepath As String
    Sourcepath = "P:\CENTRAL PLANNING\PROJECTS 2020-2021\VALEO FRANCE  35101380FM\Drg folder\"  

    Dim destpath As String
    destpath = "P:\CENTRAL PLANNING\PROJECTS 2020-2021\VALEO FRANCE  35101380FM\New folder1\"
    'make sure paths end with \        
    Dim irow As Long
    Dim f As SearchFolders
    Dim filetype As String
    filetype = "*.pdf"
    irow = 7
    
    Do While ws.Cells(irow, 2) <> vbNullString
        Dim FileName As String
        FileName = Dir(Sourcepath & ws.Cells(irow, 2) & "*.pdf")
        'this will only go through pdf files
        
        Do While FileName <> vbNullString
        'if more files with the key word from ws.Cells(iRow, 2) exist copy all of them
            VBA.FileCopy Sourcepath & FileName, destpath & FileName
            'copy needs to be path AND filename
            FileName = Dir()
        Loop
        
        irow = irow + 1
    Loop
    Sheets("RFQ").Select
    Columns("AB:AG").Select
    Selection.Delete

End Sub

on here , this code is help me to find a pdf file which matches my excel cell data and after that finding a file it will copy that particular file and paste in the destination folder

my issue is my file name in excel will look like AVRO15VE1522A, AVRO15VE1523B, AVRO15VE1524C , (In the file name list, the last alphabet is revision it may change as A,B,C,D,E.....Z)

but in my folder the file name will be like AVRO15VE1522 A, (OR) AVRO15VE1522A, AVRO15VE1523 B, (OR) AVRO15VE1523B, sometimes multiple files with same file name will present ,so i want only one file on that particular file name

and if all the revision files which is AVRO15VE1522A, AVRO15VE1522B, AVRO15VE1522C, Is present in folder means , i want the code to take a file which matches my filename in excel range

Before you start doing anything else I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and to get rid of all `.Select` and `Selection` statements to make your code fast, short, reliable and easier to maintain. — Pᴇʜ, Mar 29 '21 at 07:09
In a folder, you can not have multiple files with *same* file name, they might be similar but not the same. If there are multiples is it the latest you want to select ? — CDP1802, Mar 29 '21 at 09:50

loop through folders vba

0 Answers0