最新wincc生成excel.docx

1、最新wincc生成excel给wincc中通过vbs写excel的方式做报表的初学者一点脚本我对该帖子不做任何限制，都可以自由拷贝使用。方便初学者学习！/log子程序，40个数据记录，总共记录一周时间，写入excel，放到wincc 全局脚本中一分钟运行一次/如果是要整点报表也简单，判断一下当前的系统时间，t的初始值根据当前时间定义。/本程序主要是演示数据记录的过程。/在c:建立一个excel文件，该文件可以是你的报表模版Option ExplicitFunction actionDim objExcelApp,objExcelbook,objExcelSheetDim tagshijian,

2、sheetname,username,zhushiDim tagdayDim a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40Dim i,j,t,countDim msgDim patch,fnDim d,m,y,oh,mm,ssd= m=-y=-oh=:mm=:ss=msg=记录报表成功fn=CStr(Year(Now)&CStr(Month(

3、Now)&CStr(Day(Now)patch=c:&fn&.xlsSet a1=HMIRuntime.Tags(a1)Set a2=HMIRuntime.Tags(a2)Set a3=HMIRuntime.Tags(a3)Set a4=HMIRuntime.Tags(a4)Set a5=HMIRuntime.Tags(a5)Set a6=HMIRuntime.Tags(a6)Set a7=HMIRuntime.Tags(a7)Set a8=HMIRuntime.Tags(a8)Set a9=HMIRuntime.Tags(a9)Set a10=HMIRuntime.Tags(a10)Set

4、a11=HMIRuntime.Tags(a11)Set a12=HMIRuntime.Tags(a12)Set a13=HMIRuntime.Tags(a13)Set a14=HMIRuntime.Tags(a14)Set a15=HMIRuntime.Tags(a15)Set a16=HMIRuntime.Tags(a16)Set a17=HMIRuntime.Tags(a17)Set a18=HMIRuntime.Tags(a18)Set a19=HMIRuntime.Tags(a19)Set a20=HMIRuntime.Tags(a20)Set a21=HMIRuntime.Tags(

5、a21)Set a22=HMIRuntime.Tags(a22)Set a23=HMIRuntime.Tags(a23)Set a24=HMIRuntime.Tags(a24)Set a25=HMIRuntime.Tags(a25)Set a26=HMIRuntime.Tags(a26)Set a27=HMIRuntime.Tags(a27)Set a28=HMIRuntime.Tags(a28)Set a29=HMIRuntime.Tags(a29)Set a30=HMIRuntime.Tags(a30)Set a31=HMIRuntime.Tags(a31)Set a32=HMIRunti

6、me.Tags(a32)Set a33=HMIRuntime.Tags(a33)Set a34=HMIRuntime.Tags(a34)Set a35=HMIRuntime.Tags(a35)Set a36=HMIRuntime.Tags(a36)Set a37=HMIRuntime.Tags(a37)Set a38=HMIRuntime.Tags(a38)Set a39=HMIRuntime.Tags(a39)Set a40=HMIRuntime.Tags(a40)Set count=HMIRuntime.Tags(count)sheetname=Sheet1tagshijian=NowSe

7、t objExcelApp=CreateObject(Excel.Application) objExcelApp.visible=False objExcelApp.workbooks.open C:Table.xls objExcelApp.worksheets(sheetname).activate count.Readt=count.Value +1If t10080 Thencount.Write 3t=4End IfFor t=t To t With objExcelApp.worksheets(sheetname) .cells(t,1).value=tagshijian a1.

8、Read .cells(t,2).value=a1.Value a2.Read .cells(t,3).value=a2.Value a3.Read .cells(t,4).value=a3.Value a4.Read .cells(t,5).value=a4.Value a5.Read .cells(t,6).value=a5.Value a6.Read .cells(t,7).value=a6.Value a7.Read .cells(t,8).value=a7.Value a8.Read .cells(t,9).value=a8.Value a9.Read .cells(t,10).va

9、lue=a9.Value a10.Read .cells(t,11).value=a10.Value a11.Read .cells(t,12).value=a11.Value a12.Read .cells(t,13).value=a12.Value a13.Read .cells(t,14).value=a13.Value a14.Read .cells(t,15).value=a14.Value a15.Read .cells(t,16).value=a15.Value a16.Read .cells(t,17).value=a16.Value a17.Read .cells(t,18)

10、.value=a17.Value a18.Read .cells(t,19).value=a18.Value a19.Read .cells(t,20).value=a19.Value a20.Read .cells(t,21).value=a20.Value a21.Read .cells(t,22).value=a21.Value a22.Read .cells(t,23).value=a22.Value a23.Read .cells(t,24).value=a23.Value a24.Read .cells(t,25).value=a24.Value a25.Read .cells(t

11、,26).value=a25.Value a26.Read .cells(t,27).value=a26.Value a27.Read .cells(t,28).value=a27.Value a28.Read .cells(t,29).value=a28.Value a29.Read .cells(t,30).value=a29.Value a30.Read .cells(t,31).value=a30.Value a31.Read .cells(t,32).value=a31.Value a32.Read .cells(t,33).value=a32.Value a33.Read .cel

12、ls(t,34).value=a33.Value a34.Read .cells(t,35).value=a34.Value a35.Read .cells(t,36).value=a35.Value a36.Read .cells(t,37).value=a36.Value a37.Read .cells(t,38).value=a37.Value a38.Read .cells(t,39).value=a38.Value a39.Read .cells(t,40).value=a39.Value a40.Read .cells(t,41).value=a40.Value End With

13、Next objExcelApp.ActiveWorkbook.Save objExcelApp.ActiveWorkbook.close objExcelApp.ActiveWorkbook.Quit Set objExcelApp=Nothing End Function/SAVE子程序/保存文件/读出系统时间，把文件保存为以日期为文件名的文件/每周日的23:59:10执行一次Option ExplicitFunction actionDim objExcelApp,objExcelbook,objExcelSheetDim sheetname,usernameDim tagdayDim

14、i,j,tDim msgDim patch,fnfn=CStr(Year(Now)&CStr(Month(Now)&CStr(Day(Now)+CStr(Hour(Now)&CStr(Minute(Now)patch=c:&fn&.xlssheetname=Sheet1Set objExcelApp=CreateObject(Excel.Application) objExcelApp.visible=False objExcelApp.workbooks.open C:Table.xls objExcelApp.worksheets(sheetname).activate objExcelA

15、pp.ActiveWorkbook.SaveAs patch objExcelApp.ActiveWorkbook.Save objExcelApp.ActiveWorkbook.close objExcelApp.ActiveWorkbook.Quit Set objExcelApp=NothingEnd Function/delete子程序/删除模版，拷贝空白模版到当前目录/有人会问，为什么不用vb清空当前已经记录数据的模版文件/问的好，如果这个模版里面已经存了几万条数据了呢？/所以最快的方法是用vc脚本，将当前模版删除，拷贝一个空的模版到c:/每周日的23:59:30执行一次#inclu

16、de apdefap.hint gscAction( void )#pragma option(mbcs)/ WINCC:TAGNAME_SECTION_START/ syntax: #define TagNameInAction DMTagName/ next TagID : 1/ WINCC:TAGNAME_SECTION_END#pragma code(kernel32.dll)long CopyFileA(LPCTSTR,LPCTSTR,long);long DeleteFileA(LPCTSTR);#pragma code()DeleteFileA(C:Table.xls);CopyFileA(C:bakTable.xls,C:Table.xls,TRUE);/ WINCC:PICNAME_SECTION_START/ syntax: #define PicNameInAction PictureName/ next PicID : 1/ WINCC:PICNAME_SECTION_ENDreturn 0;