本文共 2956 字,大约阅读时间需要 9 分钟。
'*********************************************************** '* Windows Script Source Code * '*Used for collecting CDW Missing data report * '*HM Senior IT: Eric Fu eric.fu@hm.com * '*Date: 18 Jan 2016 * '*Any Site can change infor depend on your request * '********************************************************** 'on error resume next sFolder = "C:\temp\cdw\source\" Dim WDay(7),Wformat WDay(1)="Sun" WDay(2)="Mon" WDay(3)="Tue" WDay(4)="Wed" WDay(5)="Thu" WDay(6)="Fri" WDay(7)="Sat" Wformat=WDay(Weekday(Now)) 'wscript.echo Wformat Dim fs, oFolder, oFiles, oSubFolders set fs = CreateObject("Scripting.FileSystemObject") set oFolder = fs.GetFolder(sFolder) '获取文件夹 set oFiles = oFolder.Files '获取文件集合 for each file in oFiles 'sExt = fs.GetExtensionName(file) '获取文件扩展名 'sExt = LCase(sExt) '转换成小写 'message = "文件名:" & file.Name & ", 扩展名:" & sExt '获得文件名(含扩展名,不含路径)和扩展名 excelmessage=file.Name 'get excel file name 'MsgBox excelmessage '====need to kill excel process first==== Next dim oExcel,oWb,oSheet Set oExcel= CreateObject("Excel.Application") Set oWb = oExcel.Workbooks.Open(sFolder & excelmessage) 'set oSheet = oWb.Sheets("Sheet1") 'MsgBox oSheet.Range("C4").Value oExcel.WorkSheets(1).Activate k=oExcel.ActiveSheet.Range("A65536").End(-4162).Row '获取A列最后个非空单元格行号 'wscript.echo k 'Msgbox oExcel.Cells(6,4).Value set flog = fs.OpenTextFile("C:\temp\cdw\MissingStoreLog-"&Wformat&".log", 2,True) flog.WriteLine("-----Missing Store Log data equal to 0 -----") for i = 1 to k if oExcel.Cells(i,4).Value = "0" then 'msgbox oExcel.Cells(i,3).Value set fcase=fs.opentextfile("C:\temp\cdw\case.txt",1,false) do until fcase.atendofstream j = 0 strline=fcase.readline MyArray=Split(strline," ",-1,1) 'msgbox MyArray(0) 'msgbox MyArray(1) if oExcel.Cells(i,3).value = MyArray(0) then j = j + 1 exit do end if loop if j = 1 then flog.WriteLine (oExcel.Cells(i,3).value & chr(9) & MyArray(1)) else flog.WriteLine (oExcel.Cells(i,3).Value & chr(9) &"Create New Case") end if set MyArray = nothing j = 0 fcase.close 'flog.WriteLine (oExcel.Cells(i,3).Value) end if Next '=========missing store log data equal to null========== flog.WriteLine("-----Missing Store Log data equal to null -----") for i = 1 to k if oExcel.Cells(i,4).Value = "" then 'msgbox oExcel.Cells(i,3).Value 'flog.WriteLine (oExcel.Cells(i,3).Value) set fcase=fs.opentextfile("C:\temp\cdw\case.txt",1,false) do until fcase.atendofstream j = 0 strline=fcase.readline MyArray=Split(strline," ",-1,1) 'msgbox MyArray(0) 'msgbox MyArray(1) if oExcel.Cells(i,3).value = MyArray(0) then j = j + 1 exit do end if loop if j = 1 then flog.WriteLine (oExcel.Cells(i,3).value & chr(9) & MyArray(1)) else flog.WriteLine (oExcel.Cells(i,3).Value & chr(9) &"Create New Case") end if set MyArray = nothing j = 0 fcase.close end if Next wscript.sleep 1000 oExcel.WorkBooks.Close oExcel.Quit flog.Close() set flog = nothing set fs = nothing
本文转自 bilinyee博客,原文链接: http://blog.51cto.com/ericfu/1736338 如需转载请自行联系原作者