winccdatabaseread.txt

WINCC数据库读取 读取WINCC数据库数据,存放在EXCEL里

 Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Sub Form_Load()



 
  Dim cn     As String
  Dim ls     As Long
  Dim res     As Long
    
  cn = String(1024, 0)      '或cn   =   vb.Space(100)
  ls = 1024
    
  res = GetComputerName(cn, ls)
    
  If res <> 0 Then
  ComputerName = Mid(cn, 1, InStr(cn, Chr(0)) - 1)
  Else
  ComputerName = ""
  End If
 
 

 ComputerName = ComputerName + "\WINCC"









'定义连接字符串并建立连接(目的:读取所有归档变量名)
 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=master;Data Source =" & ComputerName & ""
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr
 

'*********************从数据库读取数据库名***************************************


 sql = "select * from sysdatabases where name like 'CC%R'"
 

 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)
 rs.MoveFirst
 

 database_name = (rs.Fields(0))



 rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'*********************读取变量号结束*********************************************





















Dim str As String

 str = "p"
'定义连接字符串并建立连接(目的:读取所有归档变量名)
 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=" & database_name & ";Data Source =" & ComputerName & ""
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr
 

'*********************从数据库读取变量号***************************************
'从数据库读取变量号并填充进变量显示清单中
'把变量的相关数据存储进数组中
'表PDE#TAGs中的变量全是归档的变量

 sql = "SELECT * FROM Archive"
 

 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)
 rs.MoveFirst
 
 i = 0
 
Do While Not rs.EOF And Not rs.BOF
 
 Form1.List1.AddItem (rs.Fields(1))
 Archive_Variable_Name(i) = (rs.Fields(1))

 rs.MoveNext
 
 i = i + 1
 
Loop


 rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'*********************读取变量号结束*********************************************


 



 '显示打印参数设置界面
  Form1.Show
  
'*********************界面显示完成***********************************************




End Sub



Private Sub Command1_Click()



'Form1.Hide
  
 
'****************获取用户选择的变量*********************
i = 0
j = 0
Selected_Archive_Variable_Count = 0

For i = 0 To (Form1.List1.ListCount - 1)
  
    If Form1.List1.Selected(i) = True Then
   
      Selected_Archive_Variable_Count = Selected_Archive_Variable_Count + 1
      
      Selected_Archive_Variable_Name(j) = Archive_Variable_Name(i)
      
      
      
      
      'Mid(strin,start[,length])

      'Selected_Archive_Variable_Name(j) = Mid(Selected_Archive_Variable_Name(j), 12, 20)
      
      'RTrim (Selected_Archive_Variable_Name(j))

      
      
      j = j + 1
  
    Else
   
              
    End If

Next


'***************获取用户选择的变量结束****************

'***************清空数据库先**************************

 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=MYARCHIVE;Data Source =ComputerName"
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr

sql = "DELETE FROM MYARCHIVE"


 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)


'**************清空完成******************************









'***************把选择好的归档变量写进数据库**********
Set conn = New ADODB.Connection
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=MYARCHIVE;Data Source =ComputerName"
conn.Open connstr

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "MYARCHIVE", conn, , , adCmdTable



i = 0
j = 0


 
          
For i = 0 To (Form1.List1.ListCount - 1)
    
            
     If Form1.List1.Selected(i) = True Then
     
    rs.AddNew
    rs!Name = Archive_Variable_Name(i)
    
    
      rs.Update
      booRecordAdded = True
      



   Else
   
    
      
   End If
   

   

Next


rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'MsgeBox ("成功！")

 Unload Form1
End Sub























'*****************************定义公共变量**************************
'和数据库有关的数据
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public connstr As String
Public sql As String

Public BNM(50) As String
Public Riqi(50) As String
Public shijian1 As Date
Public shijian2 As Date
Public shijian3 As String
Public shijian4 As String

Public database_name As String
'Public ComputerName As String

'和归档变量相关的数据
Public Archive_Variable_Name(300)
Public Archive_Variable_TimeFactor(300)

Public Selected_Archive_Variable_Name(100)
Public Selected_Archive_Variable_TimeFactor(100)
Public Selected_Archive_Variable_Count
Public Selected_Archive_Variable_Position(50)

'和用户操作有关的数据
Public quanxuan_button As Integer
Public User_Select_Public_Time_Zone '用户是否选择公共时间轴

'和电子表格相关的数据
Public FirstVariablePostion_In_SheetBook

'******************************************************************

'*****************************定义共用函数*************************
'********检查到数据库的连接状态************
Public Function GetConState(intState As Integer) As String

  Select Case intState
   Case 0
    GetConState = "adstateclosed"
   Case 1
    GetConState = "adstateopen"
  End Select
  
End Function
'*****************************************

'*****************************************