'***********************************************************
'* 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 如需转载请自行联系原作者
