Extracting/copying files from a folder using VBA

[Guest]

Hi all,

I have a folder (i.e. c:\current) that contains around 5000 csv files, and I
have a list of around 200 off these files that I need to copy and paste to a
new folder i.e. (c:\new).

Rather than find each file manually or use the search function, is there a
way i can tell vba to search through c:\current for the filenames that I
specify and then copy and paste them to c:\new?

Any help would be much appreciated.

Kind Regards,

Bhupinder.

 

[Tom Ogilvy]

assume the list of 200 is in column A of the activesheet and the file names
are like a.csv or aaa.csv rather than c:\current\aaa.csv

Sub copysomefiles()
Dim cell as Range
for each cell in Range("A1:A200")
filecopy "c:\current\" & cell.name, "c:\new\ & cell.name"
Next
end Sub

 

[Guest]

Tom,

excel doesn't like line 4 (filecopy....), it returns an error message saying
'Application defined or object defined error'.

I double-checked the names and filepaths, which are correct. Any ideas?

Regards,

Bhupinder.

 

[Tom Ogilvy]

there was a typo in my code:

Sub copysomefiles()
Dim cell as Range
for each cell in Range("A1:A200")
filecopy "c:\current\" & cell.name, "c:\new\" & cell.name
Next
end Sub


Just for confidence, As you can see from this demo in the immediate window:

? dir("c:\data\A*.xls")
aaa_date_test.xls
filecopy "c:\data\aaa_date_test.xls", "c:\data1\aaa_date_test.xls"
? dir("c:\data1\aaa_date_test.xls")
aaa_date_test.xls

filecopy works very well when properly constructed.

 

[Guest]

Tom,

I picked up on your typo before and tried to run it, but still the same
error message came up.

What does that error message relate to, my limited vba knowledge doesn't
allow me to decipher microsoft help's interpretation of it.

Thanks for your help.

Bhupinder.

 

[Tom Ogilvy]

Application Defined or Object defined error:
the only object in the command is Cell which is defined in my sample.

As I demonstrated, supplied with proper arguments, filecopy works fine.

I tried several different flawed constructs for filecopy and never received
that error.

filecopy does require that the workbook not be opened or the file not in use
at the time it is copied.

 

[Guest]

Hi,

this worked for me......

Sub copysomefiles()
Dim fname As Range
For Each fname In Range("fileRange")
FileCopy "c:\current\" & fname, "c:\new\" & fname
Next
End Sub

I omitted the .Name and it worked.

Thanks for all your help,

Best regards,


Bhupinder

 

[Tom Ogilvy]

I guess I will have to take a hit on that one. I did put in cell.name
rather than cell.value. I will have to admit to a typo. Sorry about that.

 

[Dave Peterson]

I think that there was another typo in Tom's code:

Sub copysomefiles()
Dim cell as Range
for each cell in Range("A1:A200")
filecopy "c:\current\" & cell.value, "c:\new\" & cell.value
Next
end Sub

(.value instead of .name)

 

[Tom Ogilvy]

I think that there was another typo in Tom's code:
I think there was one typo done twice (.name vice .value). What other error
did you see? Maybe I missed something else?? Gads!!

 

[GregR]

What if I want to do the same thing, but rather than copy only files
with a specific name, copy only files dated after a certain date, such
as 10/1/2005. TIA

Greg

 

[Tom Ogilvy]

Sub copyFiles()
Dim dte as Date, fName as String
fname = dir("C:\Myfolder\" & *.xls)
do while fname <> ""
dte = FileDateTime("C:\Myfolder\" & fName)
if clng(dte) > clng(DateValue("10/1/2005")) then
filecopy "C:\MyFolder\" & fName, "C:\New\" & fName
end if
fName = Dir()
Loop
end Sub

code is untested and may contain typos which you can feel free to correct
yourself.

 

[GregR]

Tom, thank you

 

[Dave Peterson]

Nope that was it.

Another counted from the previous and original post:

filecopy "c:\current\" & cell.name, "c:\new\ & cell.name"

 

[Tom Ogilvy]

Thanks!
--
Regards,
Tom Ogilvy

Nope that was it.

Another counted from the previous and original post:

filecopy "c:\current\" & cell.name, "c:\new\ & cell.name"