VBA to loop through a range and copy documents based on variables.

[NimrodsSon]

Hi,

I've referred to this site numerous times over the years and it has been very helpful so firstly I would like to say thanks. Generally I've not needed to post a question before as I have generally been able to use the information in the various posts to figure out what I have needed to do. Unfortunately in this case that has not been possible.

I have some code that audits a folder and lists the file path and name in column A.

I am then looking to provide a new file path and name in column C and then use FileCopy to create a new renamed copy of the document.

The audit aspect works as I would like it to but I am having real difficulty with the copy and rename.


My spreadsheet is as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]C:\Test\A\001.txt[/TD]
[TD]001.txt[/TD]
[TD]C:\Test\B\001.txt[/TD]
[TD]001.txt[/TD]
[/TR]
[TR]
[TD]C:\Test\A\002.txt[/TD]
[TD]002.txt[/TD]
[TD]C:\Test\B\001.txt[/TD]
[TD]001.txt[/TD]
[/TR]
</tbody>[/TABLE]

The code I currently have is as follows:

Sub Rename_Test()
    Dim FileList As Range, Cel As Range
    Dim OldName As String, NewName As String
    
    Set FileList = Range("A2", Range("A65536").End(xlUp))

    For Each Cel In FileList
        OldName = Cel.Value
        NewName = Cel.Offset(, 2)
        FileCopy OldName, NewName
        'Debug.Print OldName & " " & NewName
    Next Cel
End Sub

If I run the code I get an "error 76 - Path not found" on the following line:

       FileCopy OldName, NewName

When I check the variables they seem to be the correct file path.

Can anyone suggest a way forward with this or alternatively point me in the direction of a solution.

Thanks in advance

 

[Joe4]

Welcome to the Board!

It works mine for me.
Are you sure that BOTH the A and B subdirectories already exist?
The code, as written, will NOT create the B subdirectory for you.

 

[NimrodsSon]

Thank you - I didn't relaise that the second folder had to exist. I'm sure I can write a step to create it. I've been bashing my head at that on and off for a couple of days!

i really apreciate the fast help.

 

[Joe4]

You are welcome.

Here is the code that shows you how to do it. Note the code at the very bottom, which is quite handy. That first checks to see if it already exists before creating it (so as to avoid error messages).
https://www.techonthenet.com/excel/formulas/mkdir.php