function getPath() getPath = left(wscript.scriptFullName,inStrRev(wscript.scriptFullName,"\")-1) end function dim areaCodeCol, addrNameCol areaNameCol = 2 addrCodeCol = 7 workPath = getPath + "\" ' current working path Set fso = CreateObject("Scripting.FileSystemObject") Set fileTxtOld = fso.OpenTextFile(workPath + "0.txt", 1, True) objTxtTmp = replace(fileTxtOld.readall," ", "") objTxtTmp = replace(objTxtTmp, "||", "| |") objTxtNew = replace(objTxtTmp, "|", ",") set fileTxtNew = fso.OpenTextFile(workPath + "00.csv", 2, true) fileTxtNew.write objTxtNew WScript.Sleep 3000 ' create excel object set oExcel = createObject("Excel.Application") oExcel.visible = True ' open the csv file with excel object oExcel.workBooks.open(workpath + "00.csv") ' set the worksheets to active oExcel.workSheets(1).activate ' get worksheets rows and columns number rows = oExcel.workSheets(1).usedRange.rows.count cols = oExcel.workSheets(1).usedRange.columns.count ' create excel lookup object set lookupExcel = createObject("Excel.Application") lookupExcel.visible = True ' open the excel file with excel object ' *** change the path in the quotes lookupExcel.workBooks.open(workPath + "code.xls") ' set worksheets to active lookupExcel.workSheets(1).activate ' get worksheets rows and columns number lookupRows = lookupExcel.workSheets(1).usedRange.rows.count lookupCols = lookupExcel.workSheets(1).usedRange.columns.count For i = 1 to rows addrName = oExcel.activeSheet.cells(i, areaNameCol).value addrCode = oExcel.activeSheet.cells(i, addrCodeCol).value addrName = trim(addrName) posProv = instrRev(addrName, "省") posCity = inStrRev(addrName, "市") posCounty = instrRev(addrName, "县") if addrCode = " " then MsgBox "checking is running..." if addrName = "北京市" then oExcel.activeSheet.cells(i, addrCodeCol) = "1000" MsgBox "北京市" MsgBox "Found the missing area code!!!" MsgBox "1000" ' end if elseif addrName = "上海市" then oExcel.activeSheet.cells(i, addrCodeCol) = "2900" MsgBox "上海市" MsgBox "Found the missing code!!!" MsgBox "2900" ' end if elseif addrName = "天津市" then oExcel.activeSheet.cells(i, addrCodeCol) = "1100" MsgBox "天津市" MsgBox "Found the missing code!!!" MsgBox "1100" ' end if elseif addrName = "重庆市" then oExcel.activeSheet.cells(i, addrCodeCol) = "6530" MsgBox "重庆市" MsgBox "Found the missing code!!!" MsgBox "6530" ' end if elseif posProv > 0 then if posCounty > 0 then if posCity > 0 then str = right(addrName, posCounty-posCity) else str = right(addrName, posCounty-posProv) end if 'WScript.echo str elseif posCity > 0 then str = right(addrName, posCity-posProv) 'WScript.echo str end if ' end if '*important* trim off all the spaces before and after the word str = trim(str) MsgBox str j = 1 found = 0 for j = 2 to lookupRows 'lookupExcel.activeSheet.cells(j, 1).value <> "" if lookupExcel.activeSheet.cells(j, 1).value = str then MsgBox "Found the missing area code!!!" found = j oExcel.activeSheet.cells(i, addrCodeCol).value = lookupExcel.activeSheet.cells(j, 2).value MsgBox oExcel.activeSheet.cells(i, addrCodeCol).value end if Next if found = 0 then MsgBox "Cannot find matched area code!!!" end if end if end if WScript.sleep 1000 Next 'lookupExcel.workBooks(1).save lookupExcel.workBooks.close lookupExcel.quit set lookupExcel = nothing wscript.echo "Code file is closed completely!!" oExcel.activeWorkBook.saveAs workPath+"01.csv" oExcel.activeWorkBook.close oExcel.quit set oExcel = nothing wscript.echo "Source file is saved completely!!" WScript.sleep 3000 ' save the updated file Set fileCsvOld = fso.OpenTextFile(workPath + "01.csv", 1, True) objTxtFromCsv = replace(fileCsvOld.readall, ",", "|") MsgBox ObjTxtFromCsv set fileTxtFromCsv = fso.OpenTextFile(workpath + "0_updated.txt", 2, true) fileTxtFromCsv.write objTxtFromCsv MsgBox "updated file is saved comletely!!"