- ©«¤l
- 522
- ¥DÃD
- 55
- ºëµØ
- 0
- ¿n¤À
- 599
- ÂI¦W
- 43
- §@·~¨t²Î
- win 10
- ³nÅ骩¥»
- []
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-19
- ³Ì«áµn¿ý
- 2025-2-20
           
|
¦^´_ 5# GBKEE
¦p¤Uªº ADO recordset ¤è¦¡¥i¥H¹F¨ì¥Øªº¡G
1.¥ý³B²z®É¶¡Äæ¦ì¨Ï¨ä¥i¦¨¬°¤À²Õªº·sÄæ¦ì
2.¥H®É¶¡²Õ³B²z¤À²Õªº¼ÆÈÄæ¦ì¥§¡È
¦ýÁÙ¬O´Á±æ¥Î¤@Ó SQL ¦r¦ê§¹¦¨¥Øªº- '¥Øªº¡G ¨C¶¡¹j 10 ¤ÀÄÁ¡B¨C¤@Ó CTcode °µ Äæ¦ì Volts,Hz ªº¥§¡È¡A
- ' ¨Ã¨Ì¤w¶¡¹j¤§®É¶¡§Ç¤ÎCTcode §Ç§e²{µ²ªG
- Option Explicit
- Option Base 1
- Option Private Module
- 'Definitions for ADODB SQL
- Public cnn As ADODB.Connection
- Public rs As ADODB.Recordset
- Public SQL As String, FileName$
- Public ws1 As Worksheet
- Public ws2 As Worksheet
- Public TimeInterval#
- Public Time0#
- Public StartTime$, EndTime$, TimeStr$
- Sub ExcelSQL()
- Dim i&, j%
- Set ws1 = Sheets("CT")
- Set ws2 = Sheets("Temp")
- ws2.Cells.Clear
-
- FileName = ThisWorkbook.FullName
-
- ws1.Select
- StartTime = Format(Cells(2, 1), "yyyy/mm/dd hh:mm")
- EndTime = Format(Cells([A1].CurrentRegion.Rows.Count, 1), "yyyy/mm/dd hh:mm")
- TimeInterval = 10 '¶¡¹j 10 ¤ÀÄÁ
-
- Time0 = Timer
-
- '=== ½á¤©®É¶¡¶¡®æªº·sÄæ¦ì =============
- 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 format( iif(minute([¤é´Á®É¶¡]) mod " & TimeInterval & " = 0,[¤é´Á®É¶¡], " & _
- "DateAdd(""n"", " & TimeInterval & "-minute([¤é´Á®É¶¡]) mod " & TimeInterval & _
- " ,[¤é´Á®É¶¡])),""yyyy/mm/dd HH:mm"") " & _
- " From [" & ws1.Name & "$]" & _
- " where [¤é´Á®É¶¡] >= #" & StartTime & "# " & _
- " order by [¤é´Á®É¶¡]"
-
- Set rs = cnn.Execute(SQL)
-
- With ws1
- j = .[A1].End(xlToRight).Column
- .Cells(1, j + 1) = "DateTime1"
- .Cells(2, j + 1).CopyFromRecordset rs
- End With
- rs.Close
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- '=== ³B²z¼ÆÈÄæ¦ìªº¥§¡ ===========================
- 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 format([DateTime1],""yyyy/mm/dd HH:mm"") as [DateTime], CTCode, " & _
- "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz], AVG([kW]) as [avgkW] " & _
- " From [" & ws1.Name & "$] " & _
- " Where [¤é´Á®É¶¡] >= #" & StartTime & "# and [DateTime1] <= #" & EndTime & "# " & _
- " Group by [DateTime1],CTCode " & _
- " Order by [DateTime1] ,CTCode"
- Debug.Print SQL
-
- 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
-
- ws1.Select
- Cells(1, ws1.UsedRange.Columns.Count).EntireColumn.Clear
-
- rs.Close
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- ' Debug.Print Timer - Time0
- End Sub
½Æ»s¥N½X |
|