Copying files to common location

[Very_Confused]

Hello,
I am trying to copy files from different directories into a new, common directory. I have the full pathways for each file of interest listed in column A of an excel spreadsheet. From a previous thread, below macro seems like it should work but for some reason I cannot figure it out. i think the problem may be with the FileOrFolderName Function? Any suggestions would be very helpful thanks much!!!


Sub SaveFilesToFolder()
Dim NewPath As String
Dim OldFilePath As String
Dim NewFilePath As String
Dim DocName As String
Dim OldDir As String

Dim i As Integer
NewPath1 = "C:TempMyFolder" 'Change path
Range("A4").Select
i = 0
Do While ActiveCell.Value <> ""
OldDir = FileOrFolderName(OldFilePath, False) & ""
On Error Resume Next
ChDir OldDir
On Error GoTo 0
OldFilePath = ActiveCell.Value
DocName = FileOrFolderName(OldFilePath, True)
NewFilePath = "C:TempMyFolder" & DocName ' new file location
' FileCopy DocName, NewFilePath 'copy the file to new folder
' Kill OldFilePath 'delete the old file
Name DocName As NewFilePath ' move the file
ActiveCell.Offset(1, 0).Select
Loop

End Sub
Function FileOrFolderName(InputString As String, ReturnFileName As Boolean) As String
' returns the foldername without the last pathseparator or the filename
Dim i As Integer, FolderName As String, FileName As String
i = 0
While InStr(i + 1, InputString, Application.PathSeparator) > 0
i = InStr(i + 1, InputString, Application.PathSeparator)
Wend
If i = 0 Then
FolderName = CurDir
Else
FolderName = Left(InputString, i - 1)
End If
FileName = Right(InputString, Len(InputString) - i)
If ReturnFileName Then
FileOrFolderName = FileName
Else
FileOrFolderName = FolderName
End If
End Function

 

[Very_Confused]

Ha! OK so i finally got it to work!!!! THANK YOU SO MUCH! As it turns out, it works for me when the files are copied to the main directories (E:\ or C:\ etc). I found that any newly created folder defaults to read only and changing its properties is more work than what i am willing to do... For my purposes this works fine. thanks again