Vba rename query

[Pobek]

I have a two texts files say text1.txt and text2.txt, I am trying to copy them over to another folder and rename them to (rename1 and rename2). The new names are in a particular column. I have written the code below but it is giving me a runtime error saying the source file cannot be found even though the link in the macro actually does open the file. This is the code below, can someone help please?

Sub CopyFolderContents()
Dim fso As Object
Dim sourceFolder1, sourceFolder2, sourceFile1, sourceFile2 As String
Dim destinationFolder1, destinationFolder2, destParentFolder, destinationFile1, destinationFile2 As String
Dim i, j As Variant


For j = 1 To 5
    ' Initialize the FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    'Set the source and destination folder paths (change as needed)
    destParentFolder = "C:\XTemp" & "\" & Range("C47").Value2
    sourceFile1 = Cells(52 + j, 3).Value2 'Range("ParentOUT").Value2 & "\" & 203 ' "C:\Path\To\Source\Folder\"
    sourceFile2 = Cells(57 + j, 3).Value2 'Range("ParentOUT").Value2 & "\" & 402 ' "C:\Path\To\Source\Folder\"


    destinationFolder1 = "C:\XTemp" & "\" & Range("C47").Value2 & "\" & 203
    destinationFolder2 = "C:\XTemp" & "\" & Range("C47").Value2 & "\" & 402
    destinationFile1 = "C:\XTemp" & "\" & Range("C47").Value2 & "\" & 203 & "\" & Cells(52 + j, 2).Value2 & "\" & ".txt"
    destinationFile2 = "C:\XTemp" & "\" & Range("C47").Value2 & "\" & 402 & "\" & Cells(57 + j, 2).Value2 & "\" & ".Am1"
  
    strFolderExists = Dir(destParentFolder, vbDirectory)
    If strFolderExists = "" Then MkDir Path:=destParentFolder
   
    strFolderExists = Dir(destinationFolder1, vbDirectory)
    If strFolderExists = "" Then MkDir Path:=destinationFolder1
   
    strFolderExists = Dir(destinationFolder2, vbDirectory)
    If strFolderExists = "" Then MkDir Path:=destinationFolder2

   fso.CopyFile sourceFile1, destinationFile1
    'fso.CopyFile Chr(34) & sourceFile1 & Chr(34), Chr(34) & destinationFile1 & Chr(34)
    fso.CopyFile sourceFile2, destinationFile2
    ' Clean up
    Set fso = Nothing
Next j

'MsgBox "All contents have been copied from " & sourceFolder & " to " & destinationFolder, vbInformation
End Sub

 

[Pobek]

Just noticed that your destination file names seem to have backslashes just before the extensions...

Lord, my brain is obviously stuck in traffic! Thanks for that ... works now