- 帖子
- 529
- 主題
- 56
- 精華
- 0
- 積分
- 607
- 點名
- 163
- 作業系統
- win 10
- 軟體版本
- []
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-3-19
- 最後登錄
- 2025-7-8
           
|
12#
發表於 2015-8-23 10:55
| 只看該作者
回復 8# GBKEE
以一個SQL敘述完成需求終於可以達成如下,供參考; 謝謝- Sub ExcelSQL2()
- Dim i&, j%
- Dim cnn As ADODB.Connection
- Dim rs As ADODB.Recordset
- Dim SQL As String, FileName$
- Dim ws1 As Worksheet
- Dim ws2 As Worksheet
- Dim TimeInterval#
- Dim Time0#
- Dim StartTime, EndTime, TimeStr$
-
- Set ws1 = Sheets("CT")
- Set ws2 = Sheets("Temp")
- ws2.Cells.Clear
-
- FileName = ThisWorkbook.FullName
-
- ws1.Select
- ' StartTime = #1/1/2015 12:00:01 AM#
- ' EndTime = #12/31/2030#
- ' StartTime = CDbl(StartTime)
- ' EndTime = CDbl(EndTime)
- StartTime = "2015/01/01 00:00:01"
-
- TimeInterval = 10 '間隔 10 分鐘
-
- ' Time0 = Timer
- TimeStr = " format( iif(MINUTE([日期時間]) mod " & TimeInterval & " = 0,[日期時間], " & _
- "DateAdd(""n"", " & TimeInterval & "-MINUTE([日期時間]) mod " & TimeInterval & _
- " ,[日期時間])),""yyyy/mm/dd hh:mm"") "
-
- '=== 處理數值欄位的每 10分鐘平均 ===========================
- Set cnn = New ADODB.Connection
- cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
- cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
- cnn.Open
-
- SQL = "SELECT " + TimeStr + " as [DateTime], CTcode, " & _
- "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz] " & _
- " From [" & ws1.Name & "$] " & _
- " Where [日期時間] >= #" & StartTime & "# " & _
- " Group by " + TimeStr + " ,CTcode " & _
- " Order by " + TimeStr + " ,CTcode"
-
- Set rs = cnn.Execute(SQL)
-
- With ws2
- For i = 0 To rs.Fields.Count - 1
- .Cells(1, i + 1) = rs.Fields(i).Name
- Next
- .Cells(2, 1).CopyFromRecordset rs
- End With
-
- rs.Close
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- ' Debug.Print Timer - Time0
- End Sub
複製代碼 |
|