Let me preface this by saying I am a rank amateur at writing code. I have cobbled this code together using pieces from here and there.
The data set:
Excel file containing ~30,000 rows of data which is file names and directory names.
My mission is as follows:
Copy these files from the current directory path into the destination directory path using the names of the files in the list.
The issue:
The code begins and will and copy 442 files with no issue, however, when the "current directory w/o" p/n transitions to a different directory, "the file is not found" error pops up.
Here is the code:
Option Explicit
Sub CopyFiles()
Dim FSO As Object
Dim PATH, sourcefile As String, dest, DestinationFolderName, SourceFileName, Filename As String
Dim lr, x As Long
Set FSO = CreateObject("Scripting.Filesystemobject")
PATH = Range("F2").Value
lr = Cells(Rows.Count, "B").End(xlUp).Row
If PATH <> "" Then
For x = 2 To lr
SourceFileName = Range("B" & x).Value
DestinationFolderName = Range("H" & x).Value
sourcefile = PATH & "\" & SourceFileName & ".SLDPRT"
'dest = PATH & "\" & DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
dest = DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
If Not FSO.FileExists(sourcefile) Then
MsgBox ("File Not Found in " & sourcefile)
Else
FSO.CopyFile Source:=sourcefile, Destination:=dest
'MsgBox (sourcefile + " Moved to " + dest)
End If
Next x
Else
MsgBox ("Please Insert PATH in cell 'I2'")
Exit Sub
End If
End Sub
Any help which can be offered would be greatly appreciated.
The data set:
Excel file containing ~30,000 rows of data which is file names and directory names.
My mission is as follows:
Copy these files from the current directory path into the destination directory path using the names of the files in the list.
The issue:
The code begins and will and copy 442 files with no issue, however, when the "current directory w/o" p/n transitions to a different directory, "the file is not found" error pops up.
Here is the code:
Option Explicit
Sub CopyFiles()
Dim FSO As Object
Dim PATH, sourcefile As String, dest, DestinationFolderName, SourceFileName, Filename As String
Dim lr, x As Long
Set FSO = CreateObject("Scripting.Filesystemobject")
PATH = Range("F2").Value
lr = Cells(Rows.Count, "B").End(xlUp).Row
If PATH <> "" Then
For x = 2 To lr
SourceFileName = Range("B" & x).Value
DestinationFolderName = Range("H" & x).Value
sourcefile = PATH & "\" & SourceFileName & ".SLDPRT"
'dest = PATH & "\" & DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
dest = DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
If Not FSO.FileExists(sourcefile) Then
MsgBox ("File Not Found in " & sourcefile)
Else
FSO.CopyFile Source:=sourcefile, Destination:=dest
'MsgBox (sourcefile + " Moved to " + dest)
End If
Next x
Else
MsgBox ("Please Insert PATH in cell 'I2'")
Exit Sub
End If
End Sub
Any help which can be offered would be greatly appreciated.