starbox520

¦^´_ 1# rouber590324


    ¤USQL«ü¥O?

starbox520

¦^´_ 1# rouber590324

1. Sub test()
   
2.     Dim sql$, pn$
   
3.     Sheet2.Cells.ClearContents
   
4.     Sheet2.Range("b:b").NumberFormatLocal = "yyyy/m/d h:mm;@"
   
5.     pn = ThisWorkbook.FullName
   
6.     sql = "select «~¦W,last(®É¶¡) as ®É¶¡,last(¤º®e) as ¤º®e from [sheet1$] where «~¦W<>null group by «~¦W"
   
7.     SqCopy pn, sql, Sheet2.[a2], Sheet2.[a1]
   
8. End Sub
   
9. Sub SqCopy(pt As String, sq As String, Optional Rg As Range, Optional rg1 As Range)
   
10.     Dim conn As Object, rst As Object, i%
    
11.     Set conn = CreateObject("adodb.connection")
    
12.     Set rst = CreateObject("ADODB.recordset")
    
13.     Select Case Application.Version * 1
    
14.     Case Is <= 11
    
15.         conn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='excel 8.0;hdr=yes;imex=1';data source=" & pt
    
16.     Case Is >= 12
    
17.         conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';data source=" & pt
    
18.     End Select
    
19.     Set rst = conn.Execute(sq)
    
20.     If Not rg1 Is Nothing Then
    
21.         For Each Field In rst.Fields
    
22.             rg1.Offset(0, i) = Field.Name
    
23.             i = i + 1
    
24.         Next
    
25.     End If
    
26.     If Not Rg Is Nothing Then
    
27.         Rg.CopyFromRecordset rst
    
28.     End If
    
29.     conn.Close
    
30.     Set rst = Nothing
    
31.     Set conn = Nothing
    
32. End Sub

½Æ»s¥N½X