返回列表 上一主題 發帖

[發問] VBA SQL 取得時間間隔 的其他欄位之平均值

回復 10# GBKEE

謝謝超版
用你指導的方法不用 ThisworkBook.fullname,跑一次是約 11秒快了許多

另外請教,Excel 資料庫要新增一欄位,用了 Alter Table [CT$] add NewColumn char(10) 得到運算無效的信息
ADO 不支援 Alter TABLE 嗎?
不曉得問題在哪裡,敬請指導
  1. Sub ExcelAddColumn()
  2.     Dim SQL As String
  3.     Dim j%, r%
  4.     Dim cnn As ADODB.Connection
  5.     Dim rs As ADODB.Recordset
  6.     Dim ws As Worksheet
  7.    
  8.     Time0 = Timer
  9.     Set ws = ThisWorkbook.Sheets(1)
  10.     Set cnn = New ADODB.Connection
  11.    
  12.     With cnn
  13.             .Provider = "Microsoft.ACE.OLEDB.12.0"
  14.               .ConnectionString = "Extended Properties= Excel 12.0;" _
  15.                 & "Data Source=D:\VBA SQL.xlsm"
  16.         '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  17.         '**********************************************************
  18. '        '2003版 引用:microsoft activex data objects 2.x library
  19. '        .Provider = "microsoft.jet.oledb.4.0"
  20. '        .ConnectionString = "Extended Properties= Excel 8.0;" _
  21. '        & "Data Source=D:\VBA SQL.xls"
  22. '        '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  23.         .Open
  24.     End With
  25.    
  26.     SQL = "Alter Table [CT$] ADD NewColumn char(10)"   '要增加一欄位名稱
  27.    
  28. '' 出現"無效的運算" 錯誤
  29.     cnn.Execute SQL
  30.    
  31.     Set rs = New ADODB.Recordset
  32.    
  33.     SQL = "select * from [CT$] "
  34.     rs.Open SQL, cnn
  35.     Debug.Print rs.Fields.Count
  36.    
  37. End Sub
複製代碼

TOP

回復 8# GBKEE


    以一個SQL敘述完成需求終於可以達成如下,供參考; 謝謝
  1. Sub ExcelSQL2()

  2.     Dim i&, j%
  3.     Dim cnn As ADODB.Connection
  4.     Dim rs As ADODB.Recordset
  5.     Dim SQL As String, FileName$
  6.     Dim ws1 As Worksheet
  7.     Dim ws2 As Worksheet
  8.     Dim TimeInterval#
  9.     Dim Time0#
  10.     Dim StartTime, EndTime, TimeStr$
  11.    
  12.     Set ws1 = Sheets("CT")
  13.     Set ws2 = Sheets("Temp")
  14.     ws2.Cells.Clear
  15.    
  16.     FileName = ThisWorkbook.FullName
  17.    
  18.     ws1.Select
  19. '    StartTime = #1/1/2015 12:00:01 AM#
  20. '    EndTime = #12/31/2030#
  21. '    StartTime = CDbl(StartTime)
  22. '    EndTime = CDbl(EndTime)
  23.     StartTime = "2015/01/01 00:00:01"
  24.    
  25.     TimeInterval = 10   '間隔 10 分鐘
  26.    
  27. '    Time0 = Timer
  28.     TimeStr = " format( iif(MINUTE([日期時間]) mod " & TimeInterval & " = 0,[日期時間], " & _
  29.                 "DateAdd(""n"", " & TimeInterval & "-MINUTE([日期時間]) mod " & TimeInterval & _
  30.                 " ,[日期時間])),""yyyy/mm/dd hh:mm"") "
  31.                
  32. '=== 處理數值欄位的每 10分鐘平均 ===========================
  33.     Set cnn = New ADODB.Connection
  34.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  35.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  36.         cnn.Open
  37.    
  38.       SQL = "SELECT " + TimeStr + " as [DateTime], CTcode, " & _
  39.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz] " & _
  40.         " From [" & ws1.Name & "$] " & _
  41.         " Where [日期時間] >= #" & StartTime & "# " & _
  42.         " Group by " + TimeStr + " ,CTcode " & _
  43.         " Order by " + TimeStr + " ,CTcode"
  44.         
  45.     Set rs = cnn.Execute(SQL)
  46.    
  47.     With ws2
  48.         For i = 0 To rs.Fields.Count - 1
  49.             .Cells(1, i + 1) = rs.Fields(i).Name
  50.         Next
  51.         .Cells(2, 1).CopyFromRecordset rs
  52.     End With
  53.       
  54.     rs.Close
  55.     cnn.Close
  56.     Set rs = Nothing
  57.     Set cnn = Nothing
  58. '    Debug.Print Timer - Time0
  59. End Sub
複製代碼

TOP

        靜思自在 : 【生命在呼吸間】佛陀說:「生命在呼吸間。」人無法管住自己的生命,更無法擋住死期,讓自己永住人間。既然生命去來這麼無常,我們更應該好好地愛惜它、利用它、充實它,讓這無常、寶貴的生命,散發它真善美的光輝,映照出生命真正的價值。
返回列表 上一主題