Copy List of Files using Excel
 

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

Copy List of Files using Excel
 

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" wrote in message
...
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

Copy List of Files using Excel
 

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

On Aug 11, 12:29*pm, "Tim Williams" wrote:
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" wrote in message

...

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

Copy List of Files using Excel
 

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



"KateB" wrote in message
...
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

On Aug 11, 12:29 pm, "Tim Williams" wrote:
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" wrote in message

...

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

Copy List of Files using Excel
 

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


On Aug 11, 8:54*pm, "Peter T" <peter_t@discussions wrote:
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

"KateB" wrote in message

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

On Aug 11, 12:29 pm, "Tim Williams" wrote:

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" wrote in message

....

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

Copy List of Files using Excel
 

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



"KateB" wrote in message
...
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


On Aug 11, 8:54 pm, "Peter T" <peter_t@discussions wrote:
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

"KateB" wrote in message

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

On Aug 11, 12:29 pm, "Tim Williams" wrote:

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" wrote in message

...

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