Kalil
08-27-2025, 04:45 AM
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/
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/