Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Files in excel | Excel Discussion (Misc queries) | |||
excel vba copy files | Excel Programming | |||
Directory List of Excel Files | Excel Programming | |||
List of Excel files | Excel Programming | |||
Getting list of files in a folder to excel | Excel Programming |