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

[µo°Ý] ½Ð°Ý¤é»ù®æ«ç»òÂàÅܦ¨¶g»ù®æ©O?

¦^´_ 10# GBKEE
ÁÂÁ«ü¾É¡I
¥Ø«e§Ú¹ï©ó Set Rng ªºÀ³¥Î©|¥¼¤Q¤À±E±x¡A¥i¯à¬O¥H©¹¨S±µÄ²¹LExcel
ªº½t¬G¡C §Ú·|¦A¥[±j³o¤è­±ªºÀ³¥Î¡A ÁÂÁ±z¡I

TOP

¦^´_ 11# c_c_lai
¦^´_ 10# GBKEE
¨S¦³·Q¨ì¦³·sªºª©¥»¥X²{,ÁöµM§ÚµLªk¬Ý¨ìªø¤°»ò¼Ë¤l¤]ÁÂÁ¨â¦ìªº¥[±j

TOP

¤u¨ã¡÷³]©w¤Þ¥Î¶µ¥Ø¡÷Microsoft ActiveX Data Objects 2.8 Library

¥Î SQL¨Ó¸Ñ¨M¤éÂà¶g¡BÂà¤ë¡BÂà©u......ÂàÀÉ°ÝÃD
  1. Sub ¤é½uÂà¶g½u()

  2.    '«Ø¥ß¤é´Á»P¦~¶g¹ï·Ó¦r¨åÀÉ
  3.     Dim d
  4.     Set d = CreateObject("Scripting.Dictionary")
  5.     Dim c As Range
  6.     For Each c In Sheets("¤é»ù®æ").Range("A2:A" & Sheets("¤é»ù®æ").[A2].End(xlDown).Row)
  7.    
  8.        '±N¤é´ÁÂର¦~¶g¡A¨Ò¦p201215ªí¥Ü2012¦~²Ä15¶g
  9.         yyyyww = Year(c.Value) & Format(DatePart("ww", c.Value), "00")
  10.         
  11.        'Àˬd¦~¶g¬O§_¦b¦r¨åÀɤ¤¡A­Y¤£¦s¦b«h¥[¤J
  12.         If Not d.Exists(yyyyww) Then
  13.             d.Add yyyyww, c.Value
  14.         End If
  15.     Next

  16.    '§R°£¡i©P»ù®æ2¡j¤u§@ªíº[¦sªº¸ê®Æ
  17.     With Sheets("©P»ù®æ2")
  18.         .[A1:E1].Value = Sheets("¤é»ù®æ").[A1:E1].Value
  19.         .Activate
  20.         .Rows("2:" & .[A2].End(xlDown).Row).ClearContents
  21.     End With
  22.    
  23.    '«Ø¥ßADODB Connectionª«¥óÅܼÆ
  24.     Dim cn As ADODB.Connection
  25.     Set cn = New ADODB.Connection
  26.    
  27.     With cn
  28.         .Provider = "MSDASQL"
  29.         .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
  30.          "DBQ=" & ThisWorkbook.FullName & ";"
  31.         .Open
  32.     End With
  33.    
  34.    'SQL¦r¦ê
  35.     mySQL = "Select ¦~¶g,FIRST(¶}½L»ù), MAX(³Ì°ª»ù), MIN(³Ì§C»ù), LAST(¦¬½L»ù) From ((SELECT (YEAR(¤é´Á)& FORMAT(DATEPART('ww',¤é´Á),'00')) AS ¦~¶g, ¶}½L»ù, ³Ì°ª»ù, ³Ì§C»ù, ¦¬½L»ù FROM [¤é»ù®æ$A:E] WHERE ¤é´Á IS NOT NULL) tmpTable) GROUP BY ¦~¶g"
  36.    
  37.     Set rs = cn.Execute(mySQL)

  38.     With Sheets("©P»ù®æ2")
  39.         .Activate
  40.         .Range("A2").CopyFromRecordset rs
  41.     End With
  42.    
  43.    '±N¦~¶gÂର¸Ó¶g²Ä¤@­Ó¥æ©ö¤é´Á
  44.     For Each c In Sheets("©P»ù®æ2").Range("A2:A" & Sheets("©P»ù®æ2").[A2].End(xlDown).Row)
  45.         c.Value = d.Item(c.Value)
  46.     Next
  47.    
  48.    'Ãö³¬³s½u²M°£°O¾ÐÅé
  49.     cn.Close
  50.     Set cn = Nothing
  51.    
  52.     MsgBox "ÂàÀɧ¹¦¨!"

  53. End Sub
½Æ»s¥N½X
diabo

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD