ªð¦^¦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

¦^´_ 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

¦^´_ 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

¦^´_ 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

¦^´_ 10# GBKEE

ÁÂÁ¶Wª©
¥Î§A«ü¾Éªº¤èªk¤£¥Î ThisworkBook.fullname¡A¶]¤@¦¸¬O¬ù 11¬í§Ö¤F³\¦h

¥t¥~½Ð±Ð¡AExcel ¸ê®Æ®w­n·s¼W¤@Äæ¦ì¡A¥Î¤F Alter Table [CT$] add NewColumn char(10) ±o¨ì¹BºâµL®Äªº«H®§
ADO ¤£¤ä´© Alter TABLE ¶Ü?
¤£¾å±o°ÝÃD¦b­þ¸Ì¡A·q½Ð«ü¾É
  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.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
  17.         '**********************************************************
  18. '        '2003ª© ¤Þ¥Î¡Gmicrosoft 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. '        '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
  23.         .Open
  24.     End With
  25.    
  26.     SQL = "Alter Table [CT$] ADD NewColumn char(10)"   '­n¼W¥[¤@Äæ¦ì¦WºÙ
  27.    
  28. '' ¥X²{"µL®Äªº¹Bºâ" ¿ù»~
  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
½Æ»s¥N½X

TOP

¦^´_ 8# GBKEE


    ¥H¤@­ÓSQL±Ô­z§¹¦¨»Ý¨D²×©ó¥i¥H¹F¦¨¦p¤U¡A¨Ñ°Ñ¦Ò¡F ÁÂÁÂ
  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   '¶¡¹j 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. '=== ³B²z¼Æ­ÈÄæ¦ìªº¨C 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
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD