wincc与access形成EXCEL报表的实现(自己写的)

使用wincc、access、excel生成生产报表，希望对大家有所帮助！

Excel需要安装datetime控件

Excel的使用：

在Excel表格中，将每个工位分为一个表格来用，右击底部表格，查看代码。

在代码段，将数据库的实际存放路径相对应。如需该表头，在底部的表格value后边括号中修改。

在wincc中打开excel文件的方法：

ProgramExecute(\"D:\\\\Program Files\\\\Microsoft Office\\\\OFFICE11\\\\excel.exe D:\\\\大自然报表.xls\");

//说明：双引号里边有两部分路径组成（execel安装路径、excel表格文件所在路径）路径之间//有空格隔离；路径阶层有双斜杠（\\\\）隔离。

Wincc中用于向access形成的数据源存储数据脚本程序：

数据库：wincce; 表3；

ODBC名称：wincce

1A:B计量泵电流

1B:B计量泵频率设置

1C:B计量泵频率

1D:A计量泵电流

1E:A计量泵频率设置

1F:A计量泵频率

程序：

Dim objConnection

Dim strConnectionString

Dim a,b,c,d,e,f,g,h,i,j,k

Dim strSQL

Dim objCommand

strConnectionString = \"Provider=MSDASQL;DSN=wincce;UID=;PWD=;\"

a = HMIRuntime.Tags(\"B计量泵电流\").Read

b = HMIRuntime.Tags(\"B计量泵频率设置\").Read

c = HMIRuntime.Tags(\"B计量泵频率\").Read

d = HMIRuntime.Tags(\"A计量泵电流\").Read

e = HMIRuntime.Tags(\"A计量泵频率设置\").Read

f = HMIRuntime.Tags(\"A计量泵频率\").Read

strSQL = \"INSERT INTO 表3 (时间,1A,1B,1C,1D,1E,1F) (Now(),\"&a&\

Set objConnection = CreateObject(\"ADODB.Connection\")

objConnection.ConnectionString = strConnectionString

objConnection.Open

Set objCommand = CreateObject(\"ADODB.Command\")

With objCommand

.ActiveConnection = objConnection

.CommandText = strSQL

VALUES

End With

objCommand.Execute

Set objCommand = Nothing

objConnection.Close

Set objConnection = Nothing

Excel中用于对数据进行通讯的VBA程序

Dim sstart, sstop, bstart, btop As String

Public Function a()

'首先引用ado --------- 點“工具-引用”再找到Microsoft ActiveX Date Objects X.X Library

Worksheets(\"蒸馏塔2\").StandardWidth = 14

Worksheets(\"蒸馏塔2\").Columns.Font.Size = 8

Dim YEAR, MONTH, DAY As String

Dim conn As New ADODB.Connection

Dim connstr As String

Dim db As String

Dim rs As New ADODB.Recordset

Dim i, j As Long, rowa As Long

db = \"D:\\winccb.mdb\"

connstr = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & db

On Error Resume Next

conn.Open connstr

rs.Open \"select * from 表3 where 时间 between #\" & DTPicker1.Value & \"# and #\" & DTPicker2.Value & \"# \

If rs.EOF Or rs.BOF Then

MsgBox \"It is nothing.\"

Else

rowa = 3

Worksheets(\"蒸馏塔2\").Cells(2, 1).Value = \"时间\"

Worksheets(\"蒸馏塔2\").Cells(2, 2).Value = \"测取泵流量\"

Worksheets(\"蒸馏塔2\").Cells(2, 3).Value = \"测取泵流量\"

Worksheets(\"蒸馏塔2\").Cells(2, 4).Value = \"E603温度\"

Worksheets(\"蒸馏塔2\").Cells(2, 5).Value = \"E504温度\"

Worksheets(\"蒸馏塔2\").Cells(2, 6).Value = \"E501温度\"

Worksheets(\"蒸馏塔2\").Cells(2, 7).Value = \"蒸馏塔冷却器温度\"

Worksheets(\"蒸馏塔2\").Cells(2, 8).Value = \"蒸馏塔回流流量\"

Do Until rs.EOF

For j = 0 To rs.Fields.Count

Worksheets(\"蒸馏塔2\").Cells(rowa, j).Value = rs(j - 1)

Next j

rowa = rowa + 1

rs.MoveNext

Loop

End If

End Function

Private Sub CommandButton1_Click()

a

End Sub

Private Sub CommandButton2_Click()

Worksheets(\"蒸馏塔2\").Cells.Clear

End Sub