不能完全退出EXCEL,请高手见教。。全部的代码如下:
Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
Dim database,server,uid,pwd
Dim strcn,cn,is_sql,comm
Dim LocalMachineName,strFileName,strSourceFileName
Dim rs,xlBook,xlsheet,n1_ban,dd,a,b
Dim i,row,dd_begin,dd_end,countSet LocalMachineName= HMIRuntime.tags ("@LocalMachineName")
LocalMachineName.Read()
strcn=LocalMachineName.Value strSourceFileName = "e:\mb\n1w_bb.xls"
'server = "SERVER="& strcn &"\WINCC"
server = "SERVER=SERVER1\WINCC"
database="northwind"
uid="LSK"
pwd="LSK"Set a=HMIRuntime.tags ("N1_BB_DATE")
a.Read ()
dd = FormatDateTime(a.Value,2)
strFileName = DatePart("yyyy",a.Value)
If DatePart("m",a.Value)<10 Then
strFileName = DatePart("yyyy",a.Value)&"0"&DatePart("m",a.Value)
Else
strFileName = DatePart("yyyy",a.Value)&DatePart("m",a.Value)
End if
If DatePart("d",a.Value)<10 Then
strFileName = strFileName&"0"&DatePart("d",a.Value)
Else
strFileName = strFileName&DatePart("d",a.Value)
End IfSet a=HMIRuntime.tags ("N1_BB_BAN")
a.Read ()
b = a.Value
Select Case b
Case 1:
n1_ban = "08:00-20:00"
dd_begin = dd & " 12:00:00"
dd_end = dd & " 23:59:59"
strFileName = "e:\n1w\"& strFileName & "_bb1.xls"
Case 2:
n1_ban = "20:00-08:00"
dd_begin =dd & " 00:00:00"
dd_end = dd & " 11:59:59"
strFileName = "e:\n1w\"& strFileName & "_bb2.xls"
End Select
strcn="Provider=SQLOLEDB.1;DRIVER=SQL SERVER;"& SERVER &";DATABASE="& Database &";UID="& Uid &";PWD="&pWd
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = strcn
cn.Openis_sql = "select * from N1_GD where (N1_DATE_C >= '"&dd_begiN&"' And N1_DATE_C <= '"&dd_end&"') order by N1_DATE"Set rs =CreateObject("ADODB.Recordset")
Set comm=CreateObject("ADODB.Command")
comm.ActiveConnectiOn = cn
comm.CommandText = is_sql
Set rs = comm.ExecuteIf rs.eof = True And rs.bof = True Then
MsgBox "ÄãÒª²éѯµÄÊý¾Ý²»´æÔÚ!", VBINFormation + vbOKOnly, "ϵͳÌáʾ"
Set comm = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
Else
i=0
rs.MoveFirst
Set xlBook = GetObject(strSourceFileName)
Set xlsheet = xlBook.Worksheets(1)
xlBook.Application.Visible = True
xlsheet.PageSetup.PrintGridlines = True Do While rs.eof = False
xlsheet.Cells(6, "b") = dd
xlsheet.Cells(7, "b") = n1_ban
xlsheet.Cells(i+10, "a") = CDate(rs.Fields(0).value)
xlsheet.Cells(i+10, "d") = rs.Fields(2).value
xlsheet.Cells(6, "e") = xlsheet.Cells(6, "e") + rs.Fields(2).value
i = i+1
xlsheet.Cells(7, "e") = i
rs.MoveNext
Loop
xlBook.SaveAs strFileName
xlsheet.PrintPreview True
xlsheet.Activate True
'
xlBook.Close
objExcelApp.Workbooks.Close
objExcelApp.Quit
set ObjExcelApp=nothing
End IfSet comm = Nothing
rs.Close
Set rs = Nothing
cn.close
Set cn=Nothing End Sub
Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
Dim database,server,uid,pwd
Dim strcn,cn,is_sql,comm
Dim LocalMachineName,strFileName,strSourceFileName
Dim rs,xlBook,xlsheet,n1_ban,dd,a,b
Dim i,row,dd_begin,dd_end,countSet LocalMachineName= HMIRuntime.tags ("@LocalMachineName")
LocalMachineName.Read()
strcn=LocalMachineName.Value strSourceFileName = "e:\mb\n1w_bb.xls"
'server = "SERVER="& strcn &"\WINCC"
server = "SERVER=SERVER1\WINCC"
database="northwind"
uid="LSK"
pwd="LSK"Set a=HMIRuntime.tags ("N1_BB_DATE")
a.Read ()
dd = FormatDateTime(a.Value,2)
strFileName = DatePart("yyyy",a.Value)
If DatePart("m",a.Value)<10 Then
strFileName = DatePart("yyyy",a.Value)&"0"&DatePart("m",a.Value)
Else
strFileName = DatePart("yyyy",a.Value)&DatePart("m",a.Value)
End if
If DatePart("d",a.Value)<10 Then
strFileName = strFileName&"0"&DatePart("d",a.Value)
Else
strFileName = strFileName&DatePart("d",a.Value)
End IfSet a=HMIRuntime.tags ("N1_BB_BAN")
a.Read ()
b = a.Value
Select Case b
Case 1:
n1_ban = "08:00-20:00"
dd_begin = dd & " 12:00:00"
dd_end = dd & " 23:59:59"
strFileName = "e:\n1w\"& strFileName & "_bb1.xls"
Case 2:
n1_ban = "20:00-08:00"
dd_begin =dd & " 00:00:00"
dd_end = dd & " 11:59:59"
strFileName = "e:\n1w\"& strFileName & "_bb2.xls"
End Select
strcn="Provider=SQLOLEDB.1;DRIVER=SQL SERVER;"& SERVER &";DATABASE="& Database &";UID="& Uid &";PWD="&pWd
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = strcn
cn.Openis_sql = "select * from N1_GD where (N1_DATE_C >= '"&dd_begiN&"' And N1_DATE_C <= '"&dd_end&"') order by N1_DATE"Set rs =CreateObject("ADODB.Recordset")
Set comm=CreateObject("ADODB.Command")
comm.ActiveConnectiOn = cn
comm.CommandText = is_sql
Set rs = comm.ExecuteIf rs.eof = True And rs.bof = True Then
MsgBox "ÄãÒª²éѯµÄÊý¾Ý²»´æÔÚ!", VBINFormation + vbOKOnly, "ϵͳÌáʾ"
Set comm = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
Else
i=0
rs.MoveFirst
Set xlBook = GetObject(strSourceFileName)
Set xlsheet = xlBook.Worksheets(1)
xlBook.Application.Visible = True
xlsheet.PageSetup.PrintGridlines = True Do While rs.eof = False
xlsheet.Cells(6, "b") = dd
xlsheet.Cells(7, "b") = n1_ban
xlsheet.Cells(i+10, "a") = CDate(rs.Fields(0).value)
xlsheet.Cells(i+10, "d") = rs.Fields(2).value
xlsheet.Cells(6, "e") = xlsheet.Cells(6, "e") + rs.Fields(2).value
i = i+1
xlsheet.Cells(7, "e") = i
rs.MoveNext
Loop
xlBook.SaveAs strFileName
xlsheet.PrintPreview True
xlsheet.Activate True
'
xlBook.Close
objExcelApp.Workbooks.Close
objExcelApp.Quit
set ObjExcelApp=nothing
End IfSet comm = Nothing
rs.Close
Set rs = Nothing
cn.close
Set cn=Nothing End Sub
是不是SF啊,哈哈