我在WINCC做了一个启动EXCEL的按钮,按下这个按钮WINCC就不断的向EXCEL传输数据,我现在在EXCEL模板的VB里做了一个程序,每天23.59.59自动将当天的数据以当天的日期为名存到文件夹中,第一天存进了文件夹,但是第二天就没有存进去了
不知道为什么,

Option Explicit

Option Base 1
Const ServerName = "OPCServer.WinCC"
Dim WithEvents MyOPCServer As OPCServer

Dim WithEvents MyOPCGroup As OPCGroup

Dim MyOPCGroupColl As OPCGroups

Dim MyOPCItemColl As OPCItems

Dim MyOPCItems As OPCItems

Dim MyOPCItem As OPCItem

Dim ClientHandles(12) As Long ‘此处需要将6变成所要读取的变量数n

Dim ServerHandles() As Long

Dim Values(1) As Variant

Dim Errors() As Long

Dim ItemIDs(12) As String ‘此处需要将6变成所要读取的变量数n

Dim GroupName As String

Dim NodeName As String
Dim itemv(24) As Variant ‘此处将12改成变量数n的2倍
Dim ii As Integer
Dim i As Integer
Dim j As Integer
Private Sub MyOPCServer_ServerShutDown(ByVal Reason As String)
End Sub
Private Sub Workbook_Deactivate()
‘MyOPCGroupColl.RemoveAll

‘MyOPCServer.Disconnect

‘Set MyOPCItemColl = Nothing

‘Set MyOPCGroup = Nothing

‘Set MyOPCGroupColl = Nothing

‘Set MyOPCServer = Nothing
End Sub

Private Sub Workbook_Open()

i = 11 ‘此处将11换成所要写入的数据的行数，如果报表格式没有添加新行，不要改动
data_write


Application.OnTime "23:59:57", "ThisWorkbook.file_save", , True ‘每天23：59：59保存报表，到“D:\报表”文件夹中。需要在相应硬盘添加该文件夹（切记）


For ii = 1 To 12 ‘此处需要将6变成所要读取的变量数n
ClientHandles(ii) = ii

Next ii

GroupName = "MyGroup"

‘－－－－－－－－－－在excel单元格A2中写入计算机名

NodeName = Range("a2").Value

‘－－－－－－－－－－在excel单元格b2－g2中写入变量名。如果变量超过6个，按照下列格式添加n个变量

ItemIDs(1) = Range("b2").Value

ItemIDs(2) = Range("c2").Value

ItemIDs(3) = Range("d2").Value

ItemIDs(4) = Range("e2").Value

ItemIDs(5) = Range("f2").Value

ItemIDs(6) = Range("g2").Value

ItemIDs(7) = Range("h2").Value

ItemIDs(8) = Range("i2").Value

ItemIDs(9) = Range("j2").Value

ItemIDs(10) = Range("k2").Value

ItemIDs(11) = Range("l2").Value

ItemIDs(12) = Range("m2").Value

Set MyOPCServer = New OPCServer

MyOPCServer.Connect ServerName, NodeName

Set MyOPCGroupColl = MyOPCServer.OPCGroups

MyOPCGroupColl.DefaultGroupIsActive = True
Set MyOPCGroup = MyOPCGroupColl.Add(GroupName)
Set MyOPCItemColl = MyOPCGroup.OPCItems
MyOPCItemColl.AddItems 12, ItemIDs(), ClientHandles(), ServerHandles(), Errors ‘如果有n个变量，此处将6改成n的数值
MyOPCGroup.IsSubscribed = True
Exit Sub
ErrorHandler:

MsgBox "Error: " & Err.Des cription, vbCritical, "ERROR"
End Sub
Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, itemvalues() As Variant, Qualities() As Long, TimeStamps() As Date)
For ii = 1 To NumItems

itemv(ClientHandles(ii)) = itemvalues(ii)

Next ii


‘－－－－－－－－－－在excel单元格b3－g3中显示对应上行变量的数值。如果变量超过6个，按照下列格式添加n个变量

Range("b3").Value = CStr(itemv(1))

Range("c3").Value = CStr(itemv(2))

Range("d3").Value = CStr(itemv(3))

Range("e3").Value = CStr(itemv(4))

Range("f3").Value = CStr(itemv(5))

Range("g3").Value = CStr(itemv(6))

Range("h3").Value = CStr(itemv(7))

Range("i3").Value = CStr(itemv(8))

Range("J3").Value = CStr(itemv(9))

Range("K3").Value = CStr(itemv(10))

Range("L3").Value = CStr(itemv(11))

Range("M3").Value = CStr(itemv(12))

End Sub
Public Sub data_write()
Dim dtime

dtime = Now + TimeValue("00:30:00") ‘记录间隔为30分钟，如果是其它时间，改对应的时间

Application.OnTime dtime, "ThisWorkbook.data_write", , True


Cells(i, 1).Value = Time

For j = 2 To 13 ‘此处将7改成变量数n+1
Cells(i, j).Value = Cells(3, j).Value
Next j
i = i + 1
If i > 58 Then
i = 11
End If

End Sub
Sub file_save() ‘以当前时间为名字另存报表到d:\报表 文件夹中

Dim nm, wb As Workbook

i = 11 ‘初始化，数据改为从11行开始重新记录

Cells(2, 12).Value = Date
Cells(6, 12).Value = Cells(2, 12)
nm = Cells(6, 12).Value ‘将文件名改为W2中显示的日期，此处需要改动，由于添加了变量，对应显示日期的单元格已经不是W2了，需要将Cells里面改成相应的行，列数
‘将W2中的日期写入W6单元格中，此处需要改动，由于添加了变量，对应显示日期的单元格已经不是W2了，需要将Cells里面改成相应的行，列数

Application.ScreenUpdating = False
Cells.Copy
Set wb = Workbooks.Add
With wb
.Sheets(1).Paste
Application.CutCopyMode = False
.SaveAs "d:\输配水泵房报表\" & nm & ".xls"
.Close
End With
Set wb = Nothing
Application.ScreenUpdating = True

End Sub