Copy List of Files using Excel

[KateB]

Hi,

I have a many tif files (1000's) for which I have a master list in
excel. I'd like to be able to filter the list & copy the filtered
list of tifs to a new location (i.e. c:\NewFiles). The tif files are
organised in a couple of subdirectories, which I suspect makes this
harder.

I've had a bit of a dig through the group, but haven't been able to
turn anything up. Any suggestions? Using Excel 03.

Thanks,
Kate

 

[Tim Williams]

Does the master list contain the paths (including subfolder) ?
You should be able to loop through the filtered list and just copy each of
the files.
The FileSystem object has methods for moving/copying files (add a reference
to "Microsoft Scripting Runtime")

Tim

 

[KateB]

Yes, the full path for each file is in the cell. Don't suppose you
could give me a demo with the FileSystem object? I'm a bit of a self-
taught hack in VBA (mostly through editing the macro recorder, but
unfortunately that doesn't work for things outside of excel like this)

Thanks!
Kate

 

[Peter T]

Look at the "Name" function, from help

OldName = "C:\MYDIR\OLDFILE"
NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName

If not sure how to incorporate that into a loop post examples of what you
have in cells (and cell ref's) and where you want your files to go

Regards,
Peter T



Yes, the full path for each file is in the cell. Don't suppose you
could give me a demo with the FileSystem object? I'm a bit of a self-
taught hack in VBA (mostly through editing the macro recorder, but
unfortunately that doesn't work for things outside of excel like this)

Thanks!
Kate

 

[KateB]

I tried this as a dodgy first-pass, but it's hanging on the "Name
oldName As NewName" part - it says "Run time error '53' - File not
found". The file is definately there, if I click the hyperlink in
excel, it opens up. Any ideas? The list of files starts in cell A1.

Thanks,
Kate


Sub copyFiles()
strDirectory = "c:\OldSite"
strDestFolder = "c:\test"

For i = 1 To 28

oldName = Cells(i, 1).Value
truncName = Right(oldName, Len(oldName) - 3)
NewName = strDestFolder & "\" & truncName
Name oldName As NewName

Next i


End Sub

 

[Peter T]

The file is definately there

The file might be there but are you specifying the file you think you are,

oldName = Cells(i, 1).Value

Is that the full name or merely the file name? If the latter maybe you want

oldName = strDirectory & "\" & Cells(i, 1).Value

Try the following and look at error results in the Immediate window (ctrl-g)


Sub copyFiles2()
Dim i As Long
Dim strDirectory As String
Dim strDestFolder As String
Dim oldName As String, NewName As String
Dim truncName As String

strDirectory = "c:\OldSite"
strDestFolder = "c:\test"

On Error GoTo errH
For i = 1 To 28

oldName = "": NewName = "": truncName = ""
oldName = Cells(i, 1).Value
truncName = Right(oldName, Len(oldName) - 3)
NewName = strDestFolder & "\" & truncName
Name oldName As NewName
errSkip:
Next i
Exit Sub

errH:
Debug.Print i, oldName, NewName
Resume errSkip
End Sub



I notice you named your routine "copyFiles". If that's what your really want
to do you might want to look at VBA's FileCopy function

Regards,
Peter T



I tried this as a dodgy first-pass, but it's hanging on the "Name
oldName As NewName" part - it says "Run time error '53' - File not
found". The file is definately there, if I click the hyperlink in
excel, it opens up. Any ideas? The list of files starts in cell A1.

Thanks,
Kate


Sub copyFiles()
strDirectory = "c:\OldSite"
strDestFolder = "c:\test"

For i = 1 To 28

oldName = Cells(i, 1).Value
truncName = Right(oldName, Len(oldName) - 3)
NewName = strDestFolder & "\" & truncName
Name oldName As NewName

Next i


End Sub