PDA

View Full Version : [SOLVED:] Rename text file with new name



YasserKhalil
08-06-2017, 02:13 PM
Hello everyone
I have this code


Sub RenameFiles()
Dim OldName As String, NewName As String, MyDir As String

MyDir = ThisWorkbook.Path & "\"
With Worksheets("Sheet1")
OldName = "Copyright - Yasser - R80351ú¿2ú.txt"
NewName = "Copyright - Khalil - R80351ú¿2ú.txt"
End With

Name MyDir & OldName As MyDir & NewName
End Sub


It would rename text file (part of the file name) with another part ....
The file name has bad special characters and this may cause the problem

I need to replace "Yasser" in the file name with "Khalil" without touching the other characters at all
Thanks advanced for help

mdmackillop
08-06-2017, 02:22 PM
How about

OldName = "Copyright - Yasser - R80351ú¿2ú.txt"
NewName = Replace(OldName, "Yasser", "Khalil")


or

OldName = Dir(MyDir & "Copyright*.txt")
NewName = Replace(OldName, "Yasser", "Khalil")

YasserKhalil
08-06-2017, 03:39 PM
Thank you very MD for reply
I encountered this error and this is exactly what I was encountering in my real code

20010

mdmackillop
08-06-2017, 04:13 PM
This worked for me

Sub RenameFiles()
Dim OldName As String, NewName As String, MyDir As String
MyDir = ThisWorkbook.Path & "\"
With Worksheets("Sheet1")
OldName = Dir(MyDir & "Copyright*.txt")
NewName = Replace(OldName, "Yasser", "Khalil")
End With
Name MyDir & OldName As MyDir & NewName
End Sub

YasserKhalil
08-06-2017, 04:22 PM
I got the same error again ...
May be these characters doesn't conform with my regional language or what .. I am confused?

Leith Ross
08-06-2017, 09:19 PM
Hello Yasser,

VBA can only display text characters from ANSI code pages. It can not display Unicode characters. You would need to save any Unicode words or phrases on a worksheet. You can then use those cells values when building the file names.

YasserKhalil
08-07-2017, 12:51 AM
Thank you very much Mr. Leith for reply
As I got your post I used this code

Sub RenameFiles() Dim OldName As String, NewName As String, MyDir As String

MyDir = ThisWorkbook.Path & "\"
With Worksheets("Sheet1")
OldName = Dir(MyDir & Range("A1").Value & ".txt")
NewName = Dir(MyDir & Range("B1").Value & ".txt")
End With

Name MyDir & OldName As MyDir & NewName
End Sub

Put the old name in A1 and the new name in B1 but got the same error
How can I overcome this?

mdmackillop
08-07-2017, 02:55 AM
I tried adding a couple of Arabic letters from Character Map to the file name
Copyright-Yasser-ﻱﻼﻼﻱﻱ-R80351ú¿2ú.txt

The following code returns Char(63) "?" for both of these. I've no idea how to solve this.

Sub RenameFiles2() Dim OldName As String, NewName As String, MyDir As String
Dim x, i, j
MyDir = ThisWorkbook.Path & "\"
With Worksheets("Sheet1")
OldName = Dir(MyDir & "Copyright*.txt")
x = Split(OldName, "-")
For j = 0 To UBound(Split(OldName, "-"))
For i = 1 To Len(x(j))
Cells(j + 1, i) = AscW(Mid(x(j), i, 1))
Cells(j + 6, i).Formula = "=char(r[-5]c)"
Next i
Next j
End With
End Sub

GTO
08-07-2017, 04:29 AM
@Malcom:

Not any real help, but I think the Arabic characters, at least as I copy them from the thread, are Unicode. If I do this:



Sub QuickTest()
Dim b() As Byte
Dim fsoFile As Object

For Each fsoFile In CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path & "\").Files
If fsoFile.Name Like "Copyright*.txt" Then
b() = fsoFile.Name

For n = LBound(b) To UBound(b) Step 2
Debug.Print b(n) & " " & b(n + 1)
Next

Exit Sub
End If
Next
End Sub


I get this:
' 67 0
' 111 0
' 112 0
' 121 0
' 114 0
' 105 0
' 103 0
' 104 0
' 116 0
' 45 0
' 89 0
' 97 0
' 115 0
' 115 0
' 101 0
' 114 0
' 45 0
' 241 254
' 252 254
' 252 254
' 241 254
' 241 254
' 45 0
' 82 0
' 56 0
' 48 0
' 51 0
' 53 0
' 49 0
' 250 0
' 191 0
' 50 0
' 250 0
' 46 0
' 116 0
' 120 0
' 116 0



@Yasser:

With some files copied (so in case we mess up..) to a new/temp/throw-away folder, I was curious about just replacing the needed bit:



Sub RenameFiles3()
Dim fsoFile As Object

For Each fsoFile In CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path & "\").Files
If fsoFile.Name Like "Copyright*.txt" And InStr(1, fsoFile.Name, " Yasser ", vbBinaryCompare) > 0 Then
fsoFile.Name = Replace(fsoFile.Name, "Yasser", "Khalil")
End If
Next


End Sub



You only included one text file, but it seems to work.

Mark

YasserKhalil
08-07-2017, 05:37 AM
That's wonderful Mr. Mark
You have solved it completely .. Thank you very much
Thanks a lot for all of you ...

Best Regards

GTO
08-07-2017, 05:42 AM
Always nice to get a bit of luck; glad that worked out :-)

YasserKhalil
08-07-2017, 06:24 AM
No luck in knowledge Mr. Mark .. You're a legend and also other members here .. You're all superstars

mdmackillop
08-07-2017, 06:30 AM
If fsoFile.Name Like "Copyright*.txt" And InStr(1, fsoFile.Name, " Yasser ", vbBinaryCompare) > 0 Then
fsoFile.Name = Replace(fsoFile.Name, "Yasser", "Khalil")
End If
or

OldName = Dir(MyDir & "Copyright*.txt")
NewName = Replace(OldName, "Yasser", "Khalil")
Name MyDir & OldName As MyDir & NewName

Hi Mark
What do you think makes the difference between these two?

GTO
08-07-2017, 07:06 AM
Hi Malcom,

Now that I looked a bit more carefully, that second one works, as does the OP's original code at #1... If the text file was actually named "Copyright - Yasser - R80351ú¿2ú.txt", but the file in Sample.zip is named "Copyright-Yasser-R80351qj2q.txt". No spaces around the hyphen and none of the unusual characters... :fainted:

Mark

mdmackillop
08-07-2017, 08:06 AM
OK
Thanks Mark