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
@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
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?
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.