singo1232001

感謝此帖讓我練習 感謝前面各位大大 分享練習檔案


    Sub sql抓檔()
With CreateObject("adodb.connection"): V = Application.Version:
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.Path & "\" & "Data1.xls"  
For Each Z In Sheets("總表").Range("a2:a15").Value
If Z <> "" Then T = T & ",'" & Z & "'"
Next: T = Mid(T, 2, 9999)
q = "select a.*,cstr(a.日期)+a.部門+a.名稱+left(料號,7) as NN from  [Sheet1$A5:L] as a where a.部門 in(" & T & ") "
Sheets("tmp").Cells.ClearContents
Sheets("tmp").[A1].Resize(1, 14) = Split("a1,a2,日期,a4,a5,部門,a7,名稱,料號,a10,數量,地區,NN,key", ",")
Sheets("tmp").[A2].CopyFromRecordset  .Execute(q)
.Close: .Open V & "Data Source=" & ThisWorkbook.Path & "\" & "Data2.xls"
r = Sheets("tmp").Cells(Rows.Count, 3).End(3).Row
Sheets("tmp").Cells(r + 1, 1).CopyFromRecordset .Execute(q)
.Close: .Open V & "Data Source=" & ThisWorkbook.FullName
Set rs = .Execute("select * from [tmp$A1:N] order by 日期,數量 desc")
Sheets("tmp").Cells(2, 1).CopyFromRecordset rs
r = Sheets("tmp").Cells(Rows.Count, 3).End(3).Row - 1
ReDim ar(1 To r, 0): For i = 1 To r: ar(i, 0) = i: Next
Sheets("tmp").[N2].Resize(r, 1) = ar

q = "select * from ( "
q = q & "SELECT C.a1,C.a2,C.日期,C.a4,C.a5,C.部門,C.a7,C.名稱,C.料號,C.a10,C.數量,C.地區 FROM ("
q = q & " SELECT NN,MIN(KEY) as K FROM [tmp$A1:N] GROUP BY NN "
q = q & " ) B INNER JOIN [tmp$A1:N] C ON B.K = C.KEY"
q = q & " ) D order by D.日期,D.名稱"
Sheets("總表").Cells(6, 2).Resize(1000, 15).ClearContents
Sheets("總表").Cells(6, 2).CopyFromRecordset .Execute(q)
End With
End Sub




Sub sql統計名稱_地區_部門()
With CreateObject("adodb.connection"): V = Application.Version:
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("統計")
s.Rows("4:30").ClearContents
q = "select 部門,名稱,料號,count(名稱) as cnt from [總表$B5:P] group by 名稱,料號,部門 order by 部門,名稱,料號 "
s.Range("C4").CopyFromRecordset .Execute(q)
q = "select 部門,地區,count(部門) as cnt from [總表$B5:P] group by 部門,地區 order by 部門,地區 "
s.Range("J4").CopyFromRecordset .Execute(q)
q = "select 部門,count(部門) as cnt from [總表$B5:P] group by 部門 order by 部門 "
s.Range("P4").CopyFromRecordset .Execute(q)
End With: s.Select
End Sub