Hi,
I have couple of excel in a folder and in all the excels I have field called PATH which contain some location.Based on the value in one of the field of another other table I want to replace the value of PATH field in the excel sheet and save it with the same name.For example if the value of the field is LEAVE then I will be replace the value as shown below if not location will remain same it will not be replaced.
(i,e If the value in a path is C:/Desktop/Folder1/Folder2 i want to replace it to D:/Desktop/Folder1/Folder3 if the value of the PATH is LEAVE, if not PATH will remain same C:/Desktop/Folder1/Folder2).I am aware that I can use REPLACE function to replace the same.
replace('Path','C:/Desktop/Folder1/','D:/Desktop/Folder1') returns 'D:/Desktop/Folder1/Folder3'
Also I would say that all the excel is in the format "ExcelName_YYYYDD".So after reading and replace the value in excel I need to store it back with same name only. For example if I have below files I need to save it with the same name only.
ExcelName_201301.xls
ExcelName_201302.xls
ExcelName_201303.xls
ExcelName_201304.xls
ExcelName_201305.xls
Hope my requirement is understand if not please let me know. Hope some one can help me on this soon.
I want to achieve above requirement without the use of Macro.
Regards,
qvforum
