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/threads/how-compare-file-name-with-lastrow-contains-comma-dot-when-file-is-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.:yes:yes
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.