format the cells in column

[KlausW]

Hi
I use this VBA code to rename images and it works well.
I have done something so that the text in column D, new image name cannot be recognised. And I for the following error message.
I can't figure out how to format the cells in column D so that the VBA code can use them.
Anyone who can help?
Any help will be appreciated.
Best regards
Klaus W

File for help

Sub Rektangelafrundedehjørner1773_Klik()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Ark1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
 
Dim new_name As String
 
Set fo = fso.GetFolder(sh.Range("e1").Value)
 
For Each f In fo.Files
      new_name = Application.VLookup(f.Name, sh.Range("A:d"), 4, 0)
      f.Name = new_name
Next
 
MsgBox "Done"


End Sub

 

[KlausW]

File for help

Now there is no error messenger, but the VBA-code do not change the name, KW

 

[rollis13]

The problem is that you are now using numbers as file names. Is it just for testing or it will always be like this?

 

[KlausW]

The problem is that you are now using numbers as file names. Is it just for testing or it will always be like this?

Numbers is only for test

 

[rollis13]

So let's go back to post #2 (as in Monopoly) updated to the layout of latest attached file (sheet Ark1). Works only with names that aren't single numbers:

Sub Rektangelafrundedehjørner1773_Klik()
    Dim sh     As Worksheet
    Set sh = ThisWorkbook.Sheets("Ark1")
    Dim fso    As New FileSystemObject
    Dim fo     As Folder
    Dim f      As File
    Dim new_name As String
    Set fo = fso.GetFolder(sh.Range("E1").Value)
    On Error Resume Next
    For Each f In fo.Files
        new_name = Application.VLookup(Left(f.Name, InStr(f.Name, ".") - 1), sh.Range("A:D"), 4, 0)
        If Not new_name = "" Then f.Name = new_name
    Next
    MsgBox "Done"
End Sub

 

[KlausW]

Thankssssssssssssssss allot, now I see, no numbers. It works, just a question, is it not allowed to have numbers in the file name?
Have a nice weekend
Best regards Klaus W

 

[rollis13]

Works only with names that aren't single numbers:

As I said it doesn't work with single numbers 0-9; no problem if the name has mixed characters.