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

 

[RoryA]

You don't appear to be specifying a path for the source files.

 

[Pobek]

You don't appear to be specifying a path for the source files.

I am, they are specified in these cells:
sourceFile1 = Cells(52 + j, 3).Value2

each of those cells have valid directory paths in them

 

[RoryA]

Including a file name?

 

[Pobek]

Including a file name?

Yup. Here's an example:

G:\21. xxx\xxx YE23\2. Cashflows\1. YE23_Bxxx\Dev\8. Risk model runs\Scen28\Model.Proj.001.Run.001.Rreq.203.SubTotal203 6.txt

 

[RoryA]

OK, so which line is actually giving the error?

 

[Pobek]

OK, so which line is actually giving the error?

the first line below (copied multiple lines to help you spot it)

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

I wonder if it's because there is space within the string of the path itself?

 

[RoryA]

Shouldn't be an issue. How long are the filenames including paths?

 

[Pobek]

Shouldn't be an issue. How long are the filenames including paths?

length of the directory string is about 145 chrs

 

[RoryA]

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