博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
[实战5]-find out missing data from cdw report
阅读量:6232 次
发布时间:2019-06-21

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

你可能感兴趣的文章
zabbix监控windows主机网卡流量
查看>>
Hadoop基础-MapReduce的Join操作
查看>>
amCharts图表组件
查看>>
Lock
查看>>
SpringCloud分布式系统的演进.
查看>>
使用java反射机制获取javabean中的私有属性
查看>>
react-native 基础知识的学习
查看>>
poj 2440 DNA (mid)
查看>>
接口一一默认方法
查看>>
16.4 Connect your app to Google
查看>>
单片机C语言基础编程源码六则2
查看>>
圆角button
查看>>
2017-2018-1 20155203 实验三 实时系统
查看>>
架构师速成7-高中 分类: 架构师速成 2015-0...
查看>>
内建函数之:reduce()使用
查看>>
python输入字符串
查看>>
下载的chm打不开 chm看不了 已取消到该网页的导...
查看>>
qboimathtest1 t2 配对
查看>>
p4570 [BJWC2011]元素
查看>>
基础数据结构-线性表-顺序表的合并操作
查看>>