In Folder , 2 or more File Name's First 8 Char Should Not Same, How to Chek?

[Chirag R Raval]

Dear All Experts,

I have a folder contains Month Wise 9 Excel's files.
some times by mistake new file name's first 8 char become same when naming it.
(after that 8 char, rest char is unique, that's not matter here)
I can not check in hurry when new file generating in that folder that
its first 8 char is already used for another file name in previous.

Actually I have a VBA macro that open each file & process that files based on that file name (first 8 char of that file name), when get wrong result, then realize that 2 file name's first 8 char have same name so whole process end with mess up.

How can we check by code that in particular folder , Excel files, first 8 char is same?
may be more then 1 case can be found that another 2 files can have same name.

How can we check that there are how many case, that 2 files have same name ?

Message box should appear that say" there are 1, 2 or 3 instance found (with file name)
that have same first 8 char in its name .

Attached Screen Shot of That Folder



Hope there are some solution found here.

Help will be appreciated.

Regards,

Chirag Raval

 

[Marc L]

Hi !

Use Dir VBA function to read files names,
Left VBA text function to extract first 8 chars
and a dictionary to store & compare these partial names …
​

 

[Chihiro]

Alternately, since first 8 seems to be 01MMYYYY, and first 8 character is duplicate, but not the file itself...

You could use SPLIT on file name using "-". Take 2nd to last element from resulting array (i.e. Month name/short code. Though I'd recommend sticking with one or the other as naming convention).

Then concatenate it and convert to date.
CDate(Month Name/Short Code & " 1")

And use the resulting string to replace the first element of the array. Then join it back together using "-".

Ex:

Sub Demo()
Dim x
Dim fName As String, sMonth as String
fName = "01022018-FULL-DISP-MARCH-18.xlsx"
x = Split(fName, "-")
sMonth = x(UBound(x) - 1)
x(0) = Format(CDate(sMonth & " 1"), "DDMMYYYY")
Debug.Print Join(x, "-")
End Sub

Then use that to rename the file.

 

[Chirag R Raval]

Dear Sirs @Marc L & @Chihiro ,

Thanks for your valuable reply.

I think Sir @Marc L is very near to this situation.

Sir @Chihiro ..
Thanks for your code but it is for renaming the file based on month name
mentioned in file name..though its for sample that how can we do on single predefined file name but just in case, if you have an array of that
file names we can use to multiple file renaming of that folder. that's great.)

But my requirement is like take 1 check/ compare first 8 char with every other file & if same, store that name in another array, this is for 2nd file, 3rd file etc...
after complete & store matching name files in variable
its display There are 1, 2 or 3 instance that have same first 8 char .

if we go as per Sir @Marc L, how can we do with code?

Help please.

Regards,
Chirag Raval

 

[Chirag R Raval]

Dear Sir @Chihiro ,

As most possibility in file name that
Month name intentionally give accurately
When mannually naming the file that
0.5% possibility for repeat.

We can also go towards your approach
To renaming the file which needed to change.
or just virtually change the Name stored in variable & loop over
That list & change it.

Which is good approach?.
How can we construct loop?

Sir @Marc L approach also logical but how can we do that?

Hope there are some way.

Regards
Chirag Raval

 

[Chihiro]

If using my approach. You'd first run code to clean up file name(s) in the folder. Then use your code.

You can use FileDialog or hard coded folder path in conjunction with DIR() to loop through folder.

Ex:

Sub Demo()
    Dim MyPath As String, MyFile As String, sMonth As String
'If hard coded folder path is fine, then skip this part and set MyPath with path string
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = "C:\Test\" 'Change as needed
        If .Show <> -1 Then GoTo Skip:
        MyPath = .SelectedItems(1) & "\"
    End With
   
Skip:
    MyPath = MyPath
    If Not Len(MyPath) = 0 Then MyFile = Dir(MyPath & "*.xls*")
    If Len(MyFile) = 0 Then Exit Sub
   
    Do While MyFile <> ""
        x = Split(MyFile, "-")
        sMonth = x(UBound(x) - 1)
        x(0) = Format(CDate(sMonth & " 1"), "DDMMYYYY")
        Name MyPath & MyFile As MyPath & Join(x, "-")
        MyFile = Dir()
    Loop

    MsgBox "Process Completed"
End Sub

 

[Chirag R Raval]

Dear Sir @Chihiro ,

Thank you very much sir, we are near the sucessfullprocess But.

It's not change year in first 8 char as per last part of spitted element (year).
in past dated files, Its change first 8 char's last 4 digits year as current year.

I attached screen shot of test result.



can we modify it?

Regards,

Chirag Raval

 

[Chihiro]

After the split take first 2 character of the last element in array and concatenate with 20 to get the year.

 

[Chirag R Raval]

Dear Sir @Chihiro

Yes, Got It , & applied as per your guideline (Thank You) but applied modification logic, as per my mind suggest.(not optimized)

here it is..

'https://chandoo.org/forum/threads/in-folder-2-or-more-file-names-first-8-char-should-not-same-how-to-chek.39802/#post-238164
'RENAME FIRST 8 CHARECTER OF FILES IN FOLDER BASED ON MONTH NAME IN FILE NAME SOMEWHERE
Sub RenameFirstEightCharOfFilesok()
    Dim MyPath As String
    Dim MyFile As String
    Dim sMonth As String
    Dim sYear As String
 
    Dim x As Variant
'If hard coded folder path is fine, then skip this part and set MyPath with path string
'  With Application.FileDialog(msoFileDialogFolderPicker)
'        .title = "Select a Folder"
'        .AllowMultiSelect = False
'        .InitialFileName = "C:\Test\" 'Change as needed
'      If .Show <> -1 Then GoTo Skip:
'        MyPath = .SelectedItems(1) & "\"
'    End With

'Skip:
    MyPath = "D:\TEST\"
    If Not Len(MyPath) = 0 Then MyFile = Dir(MyPath & "*.xls*")
    If Len(MyFile) = 0 Then Exit Sub

    Do While MyFile <> ""
        x = Split(MyFile, "-")
        sMonth = x(UBound(x) - 1)
        sYear = Left(x(4), 2)
        x(0) = Format(CDate(sMonth & " 1"), "DDMMYYYY")
        x(0) = Left(x(0), 6) & sYear
        Name MyPath & MyFile As MyPath & Join(x, "-")
        MyFile = Dir()
    Loop

    MsgBox "Process Completed"
End Sub

i try to integrate my sYear variable in first time you value assigned to x(0) variable but can not integrate as optimization (when your code first time assign value to x(0),integrate it with year digit) , so i reassigned x(0) as above.

can optimization possible here?

Hope your suggestion .

Regards,

Chirag Raval

 

[Chihiro]

You could condense it to something like...

Sub RenameFirstEightCharOfFilesok()
    Const MyPath As String = "D:\Test\"
    Dim MyFile As String
    Dim x
    If Not Len(MyPath) = 0 Then MyFile = Dir(MyPath & "*.xls*")
    If Len(MyFile) = 0 Then Exit Sub
    Do While MyFile <> ""
        x = Split(MyFile, "-")
        x(0) = Left(Format(CDate(x(UBound(x) - 1) & " 1"), "DDMMYYYY"), 6) & Left(x(UBound(x)), 2)
        Name MyPath & MyFile As MyPath & Join(x, "-")
        MyFile = Dir()
    Loop

    MsgBox "Process Completed"
End Sub

But it makes little difference in terms of speed and if you wanted to edit the code in future, keep it as is. It's probably easier to understand the logic when it's written in stages.

 

[Chirag R Raval]

Dear Sir @Chihiro ,

Thank you very much to help me & teach.
Yes, amazing, 'Do & design simple steps so we can ( & may be others)
Can understand & edit in future , & we can also transform it for other
Task (many possibilities there) if we work
On small things"...really great.

Point to learn " Try to optimise, but with fully understandable each its parts & steps."

I will revert soon.

Regards,

Chirag Raval