how to rename folder in VBA

[snjpverma]

sorry for the incorrect title -- it should be How to rename FILE in VBA and not folder. it is not allowing me to change the title now.

Sub createfolders()

fpath = "C:\Users\xx\Downloads\"       'path to create folders
   ar = Sheets(1).Cells(1, 1).CurrentRegion
 
Dim strFile As String
    strFile = "D:\Test VBA\XYZ.xlsx"    'File to copied
 
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    Dim oFile As Object
    Set oFile = fso.GetFile(strFile)
 
    For i = 1 To UBound(ar)
     c00 = fpath & ar(i, 1) & "-" & ar(i, 2)
     If Dir(c00) = "" Then MkDir c00
     If Not fso.FileExists(fso.BuildPath(c00, oFile.Name)) Then oFile.Copy fso.BuildPath(c00, oFile.Name)
 
   Next
     Set oFile = Nothing
    Set fso = Nothing
 
End Sub

In the above code, in each new folder a file is created with name given in variable strFile.
How do I create/rename files with this name in each folder? I want the file name to be this = ar(i, 1) & "-" & ar(i, 2)

 

[rollis13]

If I understood your request correctly you have to create a folder with pasted inside a file with the same name given to the folder, then, try these changes in your macro:

Sub createfolders()

    Dim fPath  As String
    Dim ffName As String
    fPath = "C:\Users\xx\Downloads\"              'path to create folders
    ar = Sheets(1).Cells(1, 1).CurrentRegion
    Dim strFile As String
    strFile = "D:\Test VBA\XYZ.xlsx"              'File to copied
    Dim fso    As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile  As Object
    Set oFile = fso.GetFile(strFile)
    Dim eFile  As String
    eFile = Mid(oFile.Name, InStrRev(oFile.Name, ".")) '<- added, retrieve file extention
    For i = 1 To UBound(ar)
        ffName = ar(i, 1) & "-" & ar(i, 2)        '<- added, folder and file name
        c00 = fPath & ffName                      '<- changed
        If Dir(c00) = "" Then MkDir c00
        If Not fso.FileExists(fso.BuildPath(c00, oFile.Name)) Then oFile.Copy fso.BuildPath(c00, ffName & eFile) '<- changed
    Next
    Set oFile = Nothing
    Set fso = Nothing
 
End Sub

 

[rollis13]

Glad having been of some help.
By the way, I would suggest changing this:

If Dir(c00) = "" Then MkDir c00

to this:

If Dir(c00, vbDirectory) = "" Then MkDir c00

 

[snjpverma]

Glad having been of some help.
By the way, I would suggest changing this:

If Dir(c00) = "" Then MkDir c00

to this:

If Dir(c00, vbDirectory) = "" Then MkDir c00

what would change when I make this correction? Because the code you provided provided was working perfectly when I tried earlier.
And mostly I am a bit curious to know the difference.

 

[rollis13]

Well, when I tested it would crash if the new folders to be created were already there, let's say, if you launch the macro twice with the same data, it wasn't correctly testing for the presence of a directory (folder) with same name.

PS. please, if you don't mind, don't quote my post (unless necessary), just click Reply and if you are already doing so, you need to log-in to avoid unnecessary quoting.