CODEWITHSUNDEEP
vba
Vidhi
Vidhi

Reputation: 89

How to copy files to another folder by finding the location

I have list of file names in worksheet("sheet2"). I have to check for these files in a folder, if the file(s) exists then they should be copied to a new folder. I don't know where to start. Can any one guide me?

Dim rngFile as Range, cel as Range
Dim desPath as String, filename as String, sourcePath as String

Set rngFile = Thisworkbook.Sheets("Sheet2").Range("A1","A500") ' file list in ColA

desPath = "D:\withdate\" 'Destination folder is withdate
sourcePath = "D:\All\All\(fetch each cell for file name?)" 'source folde

For Each cel in rngFile
    If Dir(sourcePath & cel) <> "" Then
    FileCopy sourcePath & cel, desPath & cel 'copy to folder
End If
Next

End Sub

But the above code is not copying the files!

Upvotes: 0

Views: 3764

Answers (2)

L42
L42

Reputation: 19737

try this:

Dim rngFile as Range, cel as Range
Dim desPath as String, filename as String

Set rngFile = Thisworkbook.Sheets("Sheet2").Range("A1","A500") 'assuming file list in ColA, change to suit

desPath = "C:\User\Username\Desktop\YourFolder\" 'change to a valid path

For Each cel in rngFile
    If Dir(cel) <> "" Then
        filename = Dir(cel) 'Returns the filename
        FileCopy cel, desPath & filename 'copy to folder
    End If
Next

End Sub

This moves the file with the same filename into a new location in a folder in Desktop named YourFolder.
Hope this helps.

Edit1:

If you only have the filename with EXTENSION

Dim rngFile as Range, cel as Range
Dim desPath as String, filename as String, sourcePath as String

Set rngFile = Thisworkbook.Sheets("Sheet2").Range("A1","A500") 'assuming file list in ColA, change to suit

desPath = "C:\User\Username\Desktop\YourFolder\" 'change to a valid path
sourcePath = "C:\Whatever\Here\" 

For Each cel in rngFile
    If Dir(sourcePath & cel) <> "" Then
        FileCopy sourcePath & cel, desPath & cel 'copy to folder
    End If
Next

End Sub

Again, your filenames in Sheet2 should have extension names (eg. Sample.xlsx, Text.txt).

Upvotes: 1

Andrew
Andrew

Reputation: 2325

Not going to write your code for you, but these might help

Upvotes: 0

Related Questions