Copy List of Files using Excel

  • Thread starter Thread starter KateB
  • Start date Start date
K

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
 
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
 
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
 
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
 
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
 
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
Click to expand...

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
 
Back
Top