0

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!

Vidhi
  • 89
  • 1
  • 2
  • 8

2 Answers2

1

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).

L42
  • 19,427
  • 11
  • 44
  • 68
  • 2
    What is rngfile? How to take file names present in sheet2 and check for its existance? – Vidhi Jan 22 '14 at 09:32
  • edited :) that is where your list is. also added comments to guide you using the code. test for a few samples first. – L42 Jan 22 '14 at 09:36
  • To answer you second question, `Dir` checks if the file exist or not. It returns the filename if the file exist. – L42 Jan 22 '14 at 09:41
  • I copied the file names from a folder and pasted it on sheet2. I performed several condition checks and filtered it.Now i have few required file names. Its messing up my head. I have to take these file names from sheet2 check with the same folder(from where i got the names from) and move it to another folder!! – Vidhi Jan 22 '14 at 10:25
  • Did you include the full path? Can you post a sample filename contained in your sheet? I tested above code and it works. – L42 Jan 22 '14 at 13:23
  • How do i add full path i have extracted only file names!! example: i have file names in A2:A range(only name) i want to fetch these names in a loop and check them with a folder(i know the where the folder is) – Vidhi Jan 23 '14 at 05:49
  • Can anyone suggest me a good blog or site to gain knowledge about filesystem objects and operations on them? – Vidhi Jan 23 '14 at 05:50
  • can you explain me the code? May be im not understanding it properly – Vidhi Jan 23 '14 at 06:30
  • you told me you know where the folder is. you also know where you want to put it. so in the code, `desPath` is where you want to put the files, and `sourcePath` is where the files are located. You say you have filenames in `Sheet2`, which i hope have extension names. You concatenate it with the folder paths(`desPath & sourcePath`) to get the `file full path`. Use this full paths as argument for `FileCopy` funtion (syntax: FileCopy (source,destination). – L42 Jan 23 '14 at 06:35
  • I gave the proper path and proper sheet name, but its not copying! – Vidhi Jan 23 '14 at 09:25
0

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

Community
  • 1
  • 1
Andrew
  • 2,315
  • 3
  • 27
  • 42
  • I have more than 500 file names on sheet2 i need to take each cell value compare if the file exists? The above page you suggested is not helping me.. – Vidhi Jan 22 '14 at 08:51
  • Check new document at top, which details looping through cells. In each iteration of the loop you then need to check if the file exists and copy it to the directory if it does. – Andrew Jan 22 '14 at 08:55