Need macro to copy files (full path list) to another directory

[jhren]

As the title states.

For specifics, let's say the sheet name is "Playlist", and the list of paths is B1 and down (by formula, so some bottom cell values may be "").

"Copy to" directory specified on another sheet "Config" in cell C2.

I tried modifying a couple different scripts I found on web and kept getting error 76 path not found (yes, directory exists) or error 75 path/file access error. I'm not a code guru so I couldn't debug 'em.

[JBeaucaire]

1) List in Playlist column B, each entry is a full path and filename in a single text string
2) Destination directory is listed on Config sheet cell C2 and should be used for all files
3) Destination path may need to be created

I don't have a set of files to test on, but confidence is high. Try it out on a Playlist with only 3 entries (still need to be formulas), and make sure one of the files listed doesn't exist.
4) This macro will mark column C with "Moved" or "Not Found"

Option Explicit

Sub MoveFiles()
Dim MyFiles As Range, f As Range, destPATH As String, fNAME As String, Delim As String

Delim = Application.PathSeparator
destPATH = ThisWorkbook.Sheets("Config").Range("C2").Value
If Len(destPATH) = 0 Then
    ThisWorkbook.Sheets("Config").Activate
    Range("C2").Select
    MsgBox "Invalid destination"
    Exit Sub
Else
    If Right(destPATH, 1) <> Delim Then destPATH = destPATH & Delim
End If

On Error Resume Next
MkDir destPATH
On Error GoTo 0

Set MyFiles = ThisWorkbook.Sheets("Playlist").Range("B:B").SpecialCells(xlFormulas)
For Each f In MyFiles
    If Len(f.Value) > 0 Then
        If Len(Dir(f.Value)) > 0 Then
            fNAME = Right(f.Value, Len(f.Value) - InStrRev(f.Value, Delim))
            Name f.Value As destPATH & fNAME
            f.Offset(, 1).Value = "Moved"
        Else
            f.Offset(, 1).Value = "Not Found"
        End If
    End If
Next f

End Sub

[JBeaucaire]

I would personally appreciate you updating your profile from "Unspecified" to at least listing the COUNTRY or main city you are from. LOCATION is extremely important in some answers. Thanks.

[jhren]

Works with two exceptions:
1) Moves files instead of copying files to destination.
2) Logs some existing files as "Not Found" when path contains special but permitted character (e.g. "AC ∕DC").

#2 is quite possibly the reason I had problems with the other scripts I tried, as the listed file paths all had a special character.

[JBeaucaire]

Can you post the full filenames of a couple that didn't work. It has not been my experience that the DIR method is affected by special characters, if they are allowed then the DIR() call can see them and thus can use them. I'll take a look at your examples.

Sorry, COPY, not move.

            fNAME = Right(f.Value, Len(f.Value) - InStrRev(f.Value, Delim))
            FileCopy f.Value, destPATH & fNAME
            f.Offset(, 1).Value = "Copied"

FYI, AC/DC is not an accepted special character. The / is a network path separator and will always cause problems in my experience.

[jhren]

Here's a file path example:

D:\My Folder\2013 Honda CR-Z EX\Media System\Kenwood DNN990HD\SD Card\Music (old)\AC⁄DC\Back In Black\01 Hells Bells.m4a

The "⁄" isn't a Slash (aka Solidus). Can't remember exactly what character it is, possibly a U+2044 Fraction Slash in Character Map.

[JBeaucaire]

In this instance the best I can offer is that you change your AC/DC references to AC-DC, issue goes away.

[jhren]

Yeh... I didn't think there was a solution to this one. For those particular files I'll probably end up just doing a manual copy.

Thanks for your help.