How to Rename Files In A Folder Using VBA??

[liammoohan]

I have a folder C:\Reporting\A10\

I have an excel sheet which when I run VBA will list all the files in the above folder in column 'A' (see below):

The values in column 'B' are what I need the files to be renamed to and was wondering if this can be done using VBA?

Thanks
Liam

 

[gordsky]

You could achieve this by using Dir and looping through the folder. Have a look at the link and you will be able to adapt to suit your needs. There are several other thread on the forum with similar query

Link

 

[Marc L]

(removed)

 

[Marc L]

A VBA demonstration for starters to paste only to the worksheet module :​

Sub Demo1()
  Const C = ".csv", D = " - ", E = "*" & D, P = "C:\Reporting\A10\"
    Dim T$(), F$, R&, S$()
  ReDim T(1 To Rows.Count, 1)
        UsedRange.Clear
        F = Dir$(P & E & E & E & "*" & C)
  While F > ""
        R = R + 1
        S = Split(F, D)
        T(R, 0) = F
        T(R, 1) = Replace$(F, D & S(UBound(S)), C)
        Name P & F As P & T(R, 1)
        F = Dir$
  Wend
        If R Then Range("A1:B" & R).Value2 = T
End Sub

 

[JEC]

How about

Sub jec()
 Dim xFold As String, it As Variant
 xFold = "C:\Reporting\A10\"
 For Each it In Range("A1").CurrentRegion.Columns(1)
   Name xFold & it As xFold & it.Offset(, 1)
 Next
End Sub

 

[Marc L]

How about

Like my first code removed in post #3 …​

 

[JEC]

Didnt see, it’s the simplest way though?

 

[Marc L]

Yes, the reason why I felt into the trap …​

 

[liammoohan]

How about

Sub jec()
 Dim xFold As String, it As Variant
 xFold = "C:\Reporting\A10\"
 For Each it In Range("A1").CurrentRegion.Columns(1)
   Name xFold & it As xFold & it.Offset(, 1)
 Next
End Sub

Tried the code provided and it is Debugging (see image):

 

[liammoohan]

A VBA demonstration for starters to paste only to the worksheet module :​

Sub Demo1()
  Const C = ".csv", D = " - ", E = "*" & D, P = "C:\Reporting\A10\"
    Dim T$(), F$, R&, S$()
  ReDim T(1 To Rows.Count, 1)
        UsedRange.Clear
        F = Dir$(P & E & E & E & "*" & C)
  While F > ""
        R = R + 1
        S = Split(F, D)
        T(R, 0) = F
        T(R, 1) = Replace$(F, D & S(UBound(S)), C)
        Name P & F As P & T(R, 1)
        F = Dir$
  Wend
        If R Then Range("A1:B" & R).Value2 = T
End Sub

Tried the code provided and it is Debugging (see image):