how compare file name with lastrow contains comma ,dot when file is open

[Kalil]

Hi experts,
I need macro for file is open .
when file is open and its name like ADFGGT 1,000.00 OUT and in lastrow in column G like 15,000.00
then I would compare file name contains amount contains comma and dot with last row in column G
if the amount is different for file name with amount in lastrow for column G then populate message " the file name is wrong you need correct to ADFGGT 1,5000.00 OUT" should replace old amount with new amount and save and close file to show new file name.
when you search for amount in file name will be different location.
every amount contains comma and dot will be space before and after it except when it be first location then there is no space before and when it be last location then will not be space after it and when there is just mount then there are no spaces before and after it.
examples:
1,000.00 EXT
EXTER 15,000.00
10,000.00
WERT5 15,500.00 CVFG JKUI8
REQTR12 800.00 ZXC ASW45 ASW
when compare amount file name with amount in lastrow for column G then should check it by sum column G from row8 until last row in column G will change amount in lastrow in column G and compare after that.

here is data for open file as picture PP1

then rename and save and close like this ACVFF 8,000.00 ZXCDER

another example as picture PP2

as you see TOTAL in column G is wrong then should correct to become 750 in TOTAL row for column G and rename and save and close like this 750.00 EXT
directory will be "C:\Users\KKL\Desktop\CHANGE"
how many
at least 60 files in folder to loop and will be within multiple folders
like this
1- "C:\Users\KKL\Desktop\CHANGE\01-025"
2- "C:\Users\KKL\Desktop\CHANGE\02-025"
3- "C:\Users\KKL\Desktop\CHANGE\03-025"


here are 4 files
the second will rename based on first file and fourth file will rename based on third file.
so should follow theses steps
1- location file open is "C:\Users\KKL\Desktop\CHANGE"
2- based on attached file will match amount is already existed in open file name with amount in lastrow in column G if it's wrong then populate message " the file name is wrong you need correct"
should replace amount based on lastrow for column G.
3- after rename open file then should save in close file .
4- if the amount is wrong summing in lastrow for column G then should sum and show right summing in TOTAL row for column G and rename as I mentioned the rules.
5-Must the code loop all the subfolders in Change Folder and check all files in these subfolders
also posted
https://www.excelforum.com/search.php?searchid=26567550
https://www.mrexcel.com/board/thread...-open.1274753/

[Aussiebear]

I must admit I'm always left in a state of confusion when I try to understand your file naming conventions and in particular when you are attempting to name a file based on a variable as in this case a numerical value with two decimal places. Your comma and a dot explanation is misleading. Excel will store numerical variables as a "Number" format and you simply extend the decimal places to your hearts content. Each to their own I guess.

[werafa]

I think the original dos conventions were that punctuation was not permitted in the file name, except for the '.' before the file extension, and a few characters like '-' and '_' were.

Windows has implemented workarounds, and can now deal with punctuation (most of the time)
it will however cause you many problems and headaches.

you are much better off employing a naming convention that replaces your dots and commas with more pc-friendly syntax, and translating this using find/replace within your file if this is necessary
if find/replace is not necessary, then the current syntax would seem to have no critical purpose. and should be avoided.

[jindon]

All the files in the main/sub folders MUST be closed.

Sub test()
    Dim myDir As String, temp(), myList
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1)
    End With
    If myDir = "" Then Exit Sub
    myList = SearchFiles(myDir, 0, temp())
    If IsError(myList) Then MsgBox "No file found": Exit Sub
    DoIt myList
End Sub
 
Private Function SearchFiles(myDir$, n&, myList)
    Dim fso As Object, myFolder As Object, myFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each myFile In fso.getfolder(myDir).Files
        If (Not myFile.Name Like "~$*") * (UCase$(myFile.Name) Like UCase$("*.xls*")) Then
            n = n + 1
            ReDim Preserve myList(1 To 2, 1 To n)
            myList(1, n) = myDir & "\"
            myList(2, n) = myFile.Name
        End If
    Next
    For Each myFolder In fso.getfolder(myDir).subfolders
        SearchFiles = SearchFiles(myFolder.Path, n, myList)
    Next
    SearchFiles = IIf(n > 0, myList, CVErr(xlErrRef))
End Function


Sub DoIt(ByVal myList)
    Dim i&, fn$, myAmount$, wsName$, s$, x, msg$, g
    For i = 1 To UBound(myList, 2)
        fn = myList(1, i) & myList(2, i)
        If fn <> ThisWorkbook.FullName Then
            myAmount = GetAmount(CStr(myList(2, i)))
            If myAmount Like "*#.00" Then
                wsName = GetWsName(fn)
                s = "'" & myList(1, i) & "[" & myList(2, i) & "]" & wsName & "'!"
                x = ExecuteExcel4Macro("match(""total""," & s & "c5:c5,0)")
                If Not IsError(x) Then
                    g = ExecuteExcel4Macro(s & "r" & x & "c7")
                    x = ExecuteExcel4Macro("sum(" & s & "r9c7:r" & x - 1 & "c7)")
                    If myAmount <> Format$(x, "#,###.00") Then
                        MsgBox "Update Total from " & g & " to " & x & " in " & vbLf & fn, , "Wrong Total"
                        UpdateAmount myList(1, i) & myList(2, i), wsName, x
                        s = myList(1, i) & Replace(myList(2, i), myAmount, x)
                        If Dir(s) = "" Then
                            Name fn As myList(1, i) & Replace(myList(2, i), myAmount, Format$(x, "#,###.00"))
                        Else
                            msg = msg & vbLf & Replace(myList(2, i), myAmount, Format$(x, "#,###.00")) & vbLf & _
                                " is already exists in the same folder"
                        End If
                    End If
                End If
            End If
        End If
    Next
    If Len(msg) Then MsgBox msg
End Sub

Function GetWsName$(fn$)
    GetWsName = Replace(CreateObject("DAO.DBEngine.120").OpenDatabase(fn, _
                         False, False, "excel 5.0;hdr=no;").tabledefs(0).Name, "$", "")
End Function

Function GetAmount$(fn$)
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b\d{1,3}(,\d{3})*\.00*\b"
        If .test(fn) Then GetAmount = .Execute(fn)(0)
    End With
End Function

Sub UpdateAmount(fn$, wsName$, myAmount)
    Dim s$
    s = "Update `" & wsName & "$E8:G` Set `Balance` = " & myAmount & " Where `C#N` = 'TOTAL';"
    With CreateObject("ADODB.Connection")
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 8.0"
        .Open fn
        .Execute s
    End With
End Sub

[Kalil]

thanks jindon.
just correct me how use the code, please ?
I open new workbook and put the code inside it and close all of files then will select folder and shows message to rename files after that shows me path /file access error

 Name fn As myList(1, i) & Replace(myList(2, i), myAmount, Format$(x, "#,###.00"))

[jindon]

Message box will appear when 1) Total in Col.G is wrong and updates it to correct value.2) the new file name is already exists in the same folder as one folder can't have duplicate file name.I don't understand such error as it runs without error here.Wait for someone else.

[jindon]

Message box will appear when

1) Total in Col.G is wrong and updates it to correct value.

2) the new file name is already exists in the same folder as one folder can't have duplicate file name.

I don't understand such error as it runs without error here.

Wait for someone else.

[Kalil]

ok I check it again there are duplicates files names in specific folder when rename (if it's possible show message to inform me, please)
there is case need fixing if Total in Col.G is correct and the file name is wrong then should also rename correct file name based on Total in Col.G is correct.
thanks again

[jindon]

                        s = myList(1, i) & Replace(myList(2, i), myAmount, x)
                        If Dir(s) = "" Then
                            Name fn As myList(1, i) & Replace(myList(2, i), myAmount, Format$(x, "#,###.00"))
                        Else
                            msg = msg & vbLf & Replace(myList(2, i), myAmount, Format$(x, "#,###.00")) & vbLf & _
                                " is already exists in the same folder"
                        End If

Above part should take care.
If file is exists that is to be renamed, you will get the result at the end.

Otherwise post Folder with workbooks to test. you will need to compress I guess.
If you can't, no help.

[Kalil]

works really well.
question about amounts are existed in files names the code will search amount contains comma, dot.
if it doesn't then will ignore I mean if there files names are like this
BBG 1000 MM
VVVV 100 BB
could also deal with files names don't contain comma , dot please?

[jindon]

No, too much already.

[Kalil]

ok as you like.
thank you for your help.