EXCEL报表制作方法之一 点击：1000 | 回复：5
1、采用DDE把需要的实时数据加入EXCEL表格
2、把EXCEL表格的内容整点写入ACCESS，作为报表数据库
代码如下：
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim myPath As String
Dim myTable As String
Dim SQL As String
myPath = ThisWorkbook.Path & "\test.accdb"
myTable = "表1"
On Error GoTo errmsg
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath '连接数据库
'生成数据库不存在记录的SQL语句
SQL = "select a.* from [Excel 12.0;Database=" & ActiveWorkbook.FullName & "].[Sheet1$" & Range("a1").CurrentRegion.Address(0, 0) _
& "] a left join " & myTable & " b on a.时间=b.时间 where b.时间 is null"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
'插入数据库不存在记录
If rs.RecordCount > 0 Then '如果工作表中含有数据库不存在记录
SQL = "insert into " & myTable & " " & SQL '插入新记录SQL语句
cnn.Execute SQL
'关闭连接释放内存
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
errmsg:
MsgBox Err.Description, , "错误报告"
3、制作EXCEL查询模板，在WINCC打开此EXCEL即可。
查询代码：
Private Sub CommandButton1_Click()
Dim conn As Object
Dim Sql$
Dim qp1, qp2
Rows("6:21").Select
Selection.ClearContents
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
qp1 = Format(DTPicker1.value, "yyyy-mm-dd" & " " & Format(DTPicker2.value, "HH:mm:ss"))
qp2 = Format(DTPicker3.value, "yyyy-mm-dd" & " " & Format(DTPicker4.value, "HH:mm:ss"))
Sql = "SELECT * FROM form WHERE ID BETWEEN # " & CDate(qp1) & " # AND #" & CDate(qp2) & " #"
Set conn = CreateObject("adodb.connection")
With conn
.Provider = "microsoft.ACE.oledb.12.0"
.ConnectionString = "Data Source =d:\a.mdb"
.Open
End With
Sheet1.[a6].CopyFromRecordset conn.Execute(Sql)
conn.Close
Set conn = Nothing
End Sub
- 搞工控/自动化的，是VB还是LA...
[5076] - 哪里有三菱F930GOT-BWD-C触摸...[5816]
- WinCC项目实例[2363]
- 台达PLC上载时，密码功能无效...[2698]
- 请问大家画电气图纸用什么软...[2759]
- 组态王，怎样制作下拉式选择框...[2477]
- STEP 7 Micro WIN V4.0 SP9下...[33948]
- TRACE MODE中国培训中心已于...[2415]
- eplan p8改变导线颜色时有一...[9844]
- 免费送出专业电气绘图软件PC...[2379]
官方公众号
智造工程师
-
客服
-
小程序
-
公众号
工控网智造工程师好文精选
