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) VALUES (Now(),"&a&","&b&","&c&","&d&","&e&","&f&");"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.ConnectionString = strConnectionString

objConnection.Open

Set objCommand = CreateObject("ADODB.Command")

With objCommand

.ActiveConnection = objConnection

.CommandText = strSQL

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 & "# ", conn, 1, 3

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

本文来源：https://www.2haoxitong.net/k/doc/f426d765a6c30c2259019ea5.html