Set folder to rename

[SunValley]

After executing the code below, it is requested to define the path of the files through a dialog box. However, I need to replace this behavior, to make the file path predefined by me, instead of requesting the target folder manually.

Sub RenameFiles_A()


Dim xDir As String

Dim xFile As String

Dim xRow As Long

With Application.FileDialog(msoFileDialogFolderPicker)

    .AllowMultiSelect = False

If .Show = -1 Then

    xDir = .SelectedItems(1)

    xFile = Dir(xDir & Application.PathSeparator & "*")

    Do Until xFile = ""

        xRow = 0

        On Error Resume Next

        xRow = Application.Match(xFile, Range("A:A"), 0)

        If xRow > 0 Then

            Name xDir & Application.PathSeparator & xFile As xDir & Application.PathSeparator & Cells(xRow, "C").Value

        End If

        xFile = Dir

    Loop

End If

End With

End Sub

 

[DanteAmor]

Hi @SunValley and welcome to MrExcel board.

to make the file path predefined by me

How do you want to predefine it, directly in the macro or in a cell.

Try the following code by putting the path in the macro.

xDir = "C:\trabajo\files"

If you want it from a cell, change this line: to something like this:

xDir = range("A1").value

And then, change "A1" to the cell where you have the path

Sub RenameFiles_B()
  Dim xDir As String, xFile As String
  Dim xRow As Long
  Dim ps As String
  
  xDir = "C:\trabajo\files"       'or range("A1").value
  xFile = Dir(xDir & Application.PathSeparator & "*")
  ps = Application.PathSeparator
  
  Do Until xFile = ""
    xRow = 0
    On Error Resume Next
    xRow = Application.Match(xFile, Range("A:A"), 0)
    If xRow > 0 Then
      Name xDir & ps & xFile As xDir & ps & Cells(xRow, "C").Value
    End If
    xFile = Dir
  Loop
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[SunValley]

How do you want to predefine it, directly in the macro or in a cell.

Directly in the macro

Sub RenameFiles_B()
Dim xDir As String, xFile As String
Dim xRow As Long
Dim ps As String

xDir = "C:\trabajo\files" 'or range("A1").value
xFile = Dir(xDir & Application.PathSeparator & "*")
ps = Application.PathSeparator

Do Until xFile = ""
xRow = 0
On Error Resume Next
xRow = Application.Match(xFile, Range("A:A"), 0)
If xRow > 0 Then
Name xDir & ps & xFile As xDir & ps & Cells(xRow, "C").Value
End If
xFile = Dir
Loop
End Sub

I replaced the path, however the rename is not applied, no error is displayed either

 

[DanteAmor]

I replaced the path, however the rename is not applied, no error is displayed either

No error is displayed, because in your macro you have this instruction:

On Error Resume Next

That instruction will ignore any errors.
Delete that line and try again.

Check the error message, it is possible that the path you entered does not exist, check that the path is correctly written and that the files with their names in column A exist in that path.

For me to help you more, you will have to put images of your path, of the files in the path and the names of the files in the sheet, that way I can review them and tell you where you have the error.

 

[DanteAmor]

I replaced the path

You don't help me much, you don't specify exactly what you changed, I'm going to assume you put the pah in the macro.

Try the following macro, it has validations to help you with your process.

Sub RenameFiles_B()
  Dim xDir As String, xFile As String
  Dim ps As String
  Dim f As Range
  
  xDir = "C:\trabajo\files"       'or range("A1").value
  xFile = Dir(xDir & Application.PathSeparator & "*")
  ps = Application.PathSeparator
  
  If Dir(xDir, vbDirectory) = "" Then
    MsgBox "path does not exist"
    Exit Sub
  End If
  
  Do Until xFile = ""
    Set f = Range("A:A").Find(xFile, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Name xDir & ps & xFile As xDir & ps & Cells(f.Row, "C").Value
    End If
    xFile = Dir
  Loop
End Sub

 

[SunValley]

check that the path is correctly written

That was the problem, I put the path of a subfolder. Thank you very much for your help friend