> 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" <(E-Mail Removed)> wrote in message
news:f44196d1-ebd1-424f-82a1-(E-Mail Removed)...
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" <kate.e.be...@gmail.com> wrote in message
>
> news:fa2b59ca-b7f6-4fc2-b74f-(E-Mail Removed)...
> 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" <timjwilli...@comcast.net> 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" <kate.e.be...@gmail.com> wrote in message
>
> >news:b80661e6-14fd-40f8-a2c0-(E-Mail Removed)...
>
> > > 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
Similar Threads
