ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA SQL ¨ú±o®É¶¡¶¡¹j ªº¨ä¥LÄæ¦ì¤§¥­§¡­È

[µo°Ý] VBA SQL ¨ú±o®É¶¡¶¡¹j ªº¨ä¥LÄæ¦ì¤§¥­§¡­È

½Ð«üÂI°g¬z¡G

¤U­±ªº SQL ¦r¦ê µ²ºcµLªk±o¨ì¥Øªº¡F ¸ê®Æ¦pªþ¥ó(­ìÀɮ׸ê®Æ¬Æ¤j¡A¥uºI¨ú¤Ö¶q)
  1. Option Explicit

  2. '¥Øªº¡G ¨C¶¡¹j 10 ¤ÀÄÁ¡B¨C¤@­Ó CTcode °µ Äæ¦ì Volts,Hz ªº¥­§¡­È¡A
  3. '       ¨Ã¨Ì¤w¶¡¹j¤§®É¶¡§Ç¤ÎCTcode §Ç§e²{µ²ªG

  4. Sub ExcelSQL()
  5.     Dim SQL As String, TimeIntervalStr$
  6.     Dim j%, r%
  7.     Dim cnn As ADODB.Connection
  8.     Dim rs As ADODB.Recordset
  9.     Dim ws As Worksheet
  10.     Dim StartTime#
  11.     Set ws = Sheets("Temp")
  12.    
  13.     StartTime = Timer
  14.         Set cnn = New ADODB.Connection
  15.         With cnn
  16.             .Provider = "Microsoft.ACE.OLEDB.12.0"
  17.             .ConnectionString = "Extended Properties= Excel 12.0;" _
  18.                 & "Data Source=" & ThisWorkbook.FullName
  19.             .Open
  20.         End With
  21.         Set rs = New ADODB.Recordset

  22. ''        SQL = " select * from [CT$] where [¤é´Á®É¶¡] between #2015/6/1# and #2015/6/2#"
  23.         
  24.         TimeIntervalStr = " DateAdd(" + """n""" + ",10,[¤é´Á®É¶¡] )"        '¨C¶¡¹j10¤ÀÄÁ
  25.         
  26.         SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
  27.             "from [CT$] " & _
  28.             "where [¤é´Á®É¶¡] between #2015/06/01# and #2015/06/02# " & _
  29.             "Group by [DateTime],CTcode " & _
  30.             "Order by [DateTime]"
  31.         
  32.         Debug.Print SQL
  33.         rs.Open SQL, cnn
  34.         
  35.     With ws
  36.         .Cells.Clear
  37.         For j = 0 To rs.Fields.Count - 1
  38.             .Cells(1, j + 1) = rs.Fields(j).Name
  39.         Next
  40.         r = .Cells(.Rows.Count, 1).End(xlUp).Row
  41.         .Range("A" & r + 1).CopyFromRecordset rs
  42.     End With
  43.     rs.Close
  44.     cnn.Close
  45.     Set rs = Nothing
  46.     Set cnn = Nothing
  47.     Debug.Print Timer - StartTime
  48. End Sub
½Æ»s¥N½X
CT.rar (431.9 KB)

¦^´_ 1# Scott090


    ©|«Ý¤j«L°ª¤â¦ù¥X´©¤â

·P®¦

TOP

¦^´_ 2# Scott090

°Ñ¦Ò³o¸Ì
http://rfid.ctu.edu.tw/rueychi/3_study/db4.ppt#329
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE

·PÁ«ü¤Þ
±q 1# ªºµ{¦¡½X :
            SQL = " select * from [CT$] where [¤é´Á®É¶¡] between #2015/6/1# and #2015/6/2#"
ªº¹êÅç¡A°ò¥»ªºADO³sµ²³]©w¨S¦³°ÝÃD¡F
°ÝÃD¬O  '¨C¶¡¹j10¤ÀÄÁ­n¨ú¨ä¨ä¥LÄæ¦ìªº¥­§¡­È ³o¼Ëªº ¸ê®ÆÂ^¨ú ªº SQL ¦r¦êµ²ºc­n¦p¦ó°µ¡A¤~¯à¹F¨ì¥Øªº¡C
¥H¤U¬O¦b 1# ¤ºªº°ÝÃD©Ò¦b¡C
·Q¹L ¥Î¤l¬d¸ß¡A¦ý¥¼¯àºc«ä¨äÅÞ¿è¡C
        TimeIntervalStr = " DateAdd(" + """n""" + ",10,[¤é´Á®É¶¡] )"        '¨C¶¡¹j10¤ÀÄÁ        
        SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
            "from [CT$] " & _
            "where [¤é´Á®É¶¡] between #2015/06/01# and #2015/06/02# " & _
            "Group by [DateTime],CTcode " & _
            "Order by [DateTime]"

©|½ÐÄ~Äò¨ó§U

TOP

¦^´_ 4# Scott090

°Ñ¦Ò¤@¤U
    http://www.1keydata.com/tw/sql/sqlalias.html
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE


    ÁÂÁ¤j®v¼ö¤ß¦aÄ~Äò«ü¤Þ

Ä~Äò¬ã¨s¤¤¡A¦³¥i¯àªº¤è¦V¬O¥ý¥Î¤@ SQL ³B²z [¤é´Á®É¶¡]¤À§O¦¨ "10"¤ÀÄÁ¶¡¹jªº²Õ¡A
¦A¥Î¥t¤@SQL ¾ã²z¥X¨ä¥Lªº¥­§¡­È

¦³µ²½×¦A³ø§i¤j®v

TOP

¦^´_ 5# GBKEE


    ¦p¤Uªº ADO recordset ¤è¦¡¥i¥H¹F¨ì¥Øªº¡G
1.¥ý³B²z®É¶¡Äæ¦ì¨Ï¨ä¥i¦¨¬°¤À²Õªº·sÄæ¦ì
2.¥H®É¶¡²Õ³B²z¤À²Õªº¼Æ­ÈÄæ¦ì¥­§¡­È
¦ýÁÙ¬O´Á±æ¥Î¤@­Ó SQL ¦r¦ê§¹¦¨¥Øªº
  1. '¥Øªº¡G ¨C¶¡¹j 10 ¤ÀÄÁ¡B¨C¤@­Ó CTcode °µ Äæ¦ì Volts,Hz ªº¥­§¡­È¡A
  2. '       ¨Ã¨Ì¤w¶¡¹j¤§®É¶¡§Ç¤ÎCTcode §Ç§e²{µ²ªG
  3. Option Explicit
  4. Option Base 1
  5. Option Private Module

  6. 'Definitions for ADODB SQL
  7.     Public cnn As ADODB.Connection
  8.     Public rs As ADODB.Recordset
  9.     Public SQL As String, FileName$
  10.     Public ws1 As Worksheet
  11.     Public ws2 As Worksheet
  12.     Public TimeInterval#
  13.     Public Time0#
  14.     Public StartTime$, EndTime$, TimeStr$


  15. Sub ExcelSQL()

  16.     Dim i&, j%
  17.     Set ws1 = Sheets("CT")
  18.     Set ws2 = Sheets("Temp")
  19.     ws2.Cells.Clear
  20.    
  21.     FileName = ThisWorkbook.FullName
  22.    
  23.     ws1.Select
  24.     StartTime = Format(Cells(2, 1), "yyyy/mm/dd hh:mm")
  25.     EndTime = Format(Cells([A1].CurrentRegion.Rows.Count, 1), "yyyy/mm/dd hh:mm")
  26.     TimeInterval = 10   '¶¡¹j 10 ¤ÀÄÁ
  27.    
  28.     Time0 = Timer
  29.    
  30. '=== ½á¤©®É¶¡¶¡®æªº·sÄæ¦ì =============
  31.     Set cnn = New ADODB.Connection
  32.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  33.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  34.         cnn.Open
  35.             
  36.     SQL = "Select format( iif(minute([¤é´Á®É¶¡]) mod " & TimeInterval & " = 0,[¤é´Á®É¶¡], " & _
  37.                 "DateAdd(""n"", " & TimeInterval & "-minute([¤é´Á®É¶¡]) mod " & TimeInterval & _
  38.                 " ,[¤é´Á®É¶¡])),""yyyy/mm/dd HH:mm"") " & _
  39.     " From [" & ws1.Name & "$]" & _
  40.     " where [¤é´Á®É¶¡] >=  #" & StartTime & "#  " & _
  41.     " order by [¤é´Á®É¶¡]"
  42.    
  43.     Set rs = cnn.Execute(SQL)
  44.    
  45.     With ws1
  46.         j = .[A1].End(xlToRight).Column
  47.         .Cells(1, j + 1) = "DateTime1"
  48.         .Cells(2, j + 1).CopyFromRecordset rs
  49.     End With
  50.     rs.Close
  51.     cnn.Close
  52.     Set rs = Nothing
  53.     Set cnn = Nothing

  54. '=== ³B²z¼Æ­ÈÄæ¦ìªº¥­§¡ ===========================
  55.     Set cnn = New ADODB.Connection
  56.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  57.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  58.         cnn.Open
  59.     SQL = "SELECT format([DateTime1],""yyyy/mm/dd HH:mm"") as [DateTime], CTCode, " & _
  60.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz], AVG([kW]) as [avgkW] " & _
  61.         " From [" & ws1.Name & "$] " & _
  62.         " Where [¤é´Á®É¶¡] >=  #" & StartTime & "# and [DateTime1] <= #" & EndTime & "# " & _
  63.         " Group by [DateTime1],CTCode " & _
  64.         " Order by [DateTime1] ,CTCode"
  65.     Debug.Print SQL
  66.    
  67.     Set rs = cnn.Execute(SQL)
  68.    
  69.     With ws2
  70.         For i = 0 To rs.Fields.Count - 1
  71.             .Cells(1, i + 1) = rs.Fields(i).Name
  72.         Next
  73.         .Cells(2, 1).CopyFromRecordset rs
  74.     End With
  75.    
  76.     ws1.Select
  77.     Cells(1, ws1.UsedRange.Columns.Count).EntireColumn.Clear
  78.    
  79.     rs.Close
  80.     cnn.Close
  81.     Set rs = Nothing
  82.     Set cnn = Nothing
  83. '    Debug.Print Timer - Time0
  84. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# Scott090
§Ú¨Ï¥Î2003ª©
  1. Option Explicit
  2. Sub ExcelSQL()
  3.     Dim SQL As String, TimeIntervalStr As Double
  4.     Dim j%, r%
  5.     Dim cnn As ADODB.Connection
  6.     Dim rs As ADODB.Recordset
  7.     Dim ws As Worksheet
  8.     Set ws = ThisWorkbook.Sheets(1)
  9.     Set cnn = New ADODB.Connection
  10.     With cnn
  11.             '.Provider = "Microsoft.ACE.OLEDB.12.0"
  12.           '    .ConnectionString = "Extended Properties= Excel 12.0;" _
  13.                 & "Data Source=" & ThisWorkbook.FullName
  14.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
  15.         '**********************************************************
  16.         '2003ª© ¤Þ¥Î¡Gmicrosoft activex data objects 2.x library
  17.         .Provider = "microsoft.jet.oledb.4.0"
  18.         .ConnectionString = "Extended Properties= Excel 8.0;" _
  19.         & "Data Source=D:\VBA SQL.xls"
  20.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
  21.         .Open
  22.     End With
  23.     Set rs = New ADODB.Recordset
  24.     SQL = "select * from [ct$] "
  25.     rs.Open SQL, cnn
  26.     With ws
  27.         .[A1].CopyFromRecordset rs
  28.          TimeIntervalStr = .[A1]   '¨ú±o²Ä¤@­Ó®É¶¡
  29.         .UsedRange.Clear
  30.     End With
  31.     Do
  32.         Set rs = New ADODB.Recordset
  33.         SQL = "SELECT CTcode, avg(test.Volts) as Volts¥­§¡,avg(test.Hz) as Hz¥­§¡ from [CT$] " & _
  34.             " as test where ¤é´Á®É¶¡ >=" & TimeIntervalStr & " AND ¤é´Á®É¶¡ <" & CDbl(DateAdd("n", 10, TimeIntervalStr)) & "  Group by CTcode "
  35.         rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
  36.         If rs.RecordCount Then   'Ū¨ú¬ö¿ý
  37.             With ws
  38.                 If .UsedRange.Count = 1 Then
  39.                     .Cells(1) = "¤é´Á ®É¶¡"
  40.                     For j = 0 To rs.Fields.Count - 1
  41.                         .Cells(1, j + 2) = rs.Fields(j).Name
  42.                     Next
  43.                 End If
  44.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
  45.                 .Range("B" & r + 1).CopyFromRecordset rs
  46.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
  47.                 With .Range("A" & Rows.Count).End(xlUp).Offset(1)
  48.                     .Resize(r - .Row + 1) = DateAdd("n", 0, TimeIntervalStr) & vbLf & DateAdd("n", 10, TimeIntervalStr)
  49.                 End With
  50.             End With
  51.         End If
  52.         TimeIntervalStr = CDbl(DateAdd("n", 10, TimeIntervalStr)) '¤U¤@­Ó10¤ÀÄÁ
  53.     Loop Until rs.RecordCount = 0  'µL¬ö¿ý
  54.     rs.Close
  55.     cnn.Close
  56.     Set rs = Nothing
  57.     Set cnn = Nothing
  58. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE

ÁÂÁ¶Wª©ªº«ü¾É
¸Õ°õ¦æªº®É¶¡¬ù 40¬í
¾Ç²ß¨ì¡G
1.¤é´Á®É¶¡¥Î Double³B²z
2.¨C¤@®É¶¡¶¡®æ°µ¤@¦¸ªº SQL ¬d¸ß

¦³¤@ÂI½Ð±Ð
    "'«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé"
¥Î¬Æ»ò¤èªk¯à´î¤Ö°O¾ÐÅ骺¨Ï¥Î?
½Ð©ú¥Ü«ü¾É

TOP

¦^´_ 9# Scott090
¦pThisWorkbook¬O¸ê®Æ®w,¥ú¬O¸ü¤J´N«Ü¦û°O¾ÐÅé.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ÀR§¤±`®¦¤v¹L¡B¶¢½Í²ö½×¤H«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD