- ©«¤l
- 353
- ¥DÃD
- 5
- ºëµØ
- 0
- ¿n¤À
- 386
- ÂI¦W
- 0
- §@·~¨t²Î
- windows7
- ³nÅ骩¥»
- vba,vb,excel2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2017-1-8
- ³Ì«áµn¿ý
- 2024-5-4
|
¦^´_ 4# singo1232001
ÁקK¹L©óµ{§Ç¹Lªøºë²
Sub test2ºë²ª©()
Set CN = 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;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("q³æ¥¼¥æ"): s.[C3:C9999].ClearContents
q = "select t2.®Æ¼Æ©M from [q³æ¥¼¥æ$B2:B] as t1 left join ( "
q = q & "select ²£«~½s¸¹,IIf(IsNull(sum(dif)), 0, sum(dif)) as ®Æ¼Æ©M from ( "
q = q & "select ²£«~½s¸¹,(¼Æ¶q - Á`¥X³f¼Æ¶q) as dif from [axmr450$A1:T] where ²£«~½s¸¹ in( "
q = q & "select ®Æ¥ó½s¸¹ from [®w¦s$A1:G] where Ü®w½s¸¹ like '%" & s.[c1] & "%' "
q = q & " ) "
q = q & " ) group by ²£«~½s¸¹ "
q = q & " ) as t2 on t1.®Æ¸¹ = t2.²£«~½s¸¹ "
s.[c3].CopyFromRecordset CN.Execute(q)
End Sub
------------------------------------------------------
Sub test3³v¨B±À¶iºë²ª©()
Set CN = 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;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("q³æ¥¼¥æ"): s.[G:AZ].ClearContents
q = "select ²£«~½s¸¹,(¼Æ¶q - Á`¥X³f¼Æ¶q) as dif from [axmr450$A1:T] where ²£«~½s¸¹ in( "
q = q & "select ®Æ¥ó½s¸¹ from [®w¦s$A1:G] where Ü®w½s¸¹ like '%" & s.[c1] & "%' "
p = q & " ) "
s.[G3].CopyFromRecordset CN.Execute(p)
q = "select ²£«~½s¸¹,IIf(IsNull(sum(dif)), 0, sum(dif)) as ®Æ¼Æ©M from ( " & p
p = q & " ) group by ²£«~½s¸¹ "
s.[J3].CopyFromRecordset CN.Execute(p)
q = "select t1.®Æ¸¹,t2.®Æ¼Æ©M from [q³æ¥¼¥æ$B2:B] as t1 left join ( " & p
p = q & " ) as t2 on t1.®Æ¸¹ = t2.²£«~½s¸¹ "
s.[M3].CopyFromRecordset CN.Execute(p)
s.[G2:N2] = Array("axmr©Ò¦³", "³v¶µ®t¼Æ", "", "¦X¨Ö", "¥¼¥æÁ`¼Æ", "", "¹ï·Ó¥¼¥æ", "¥¼¥æ¼Æ")
End Sub
¥i¦bC1¿é¤J¬d¸ß½s¸¹
q³æ¥¼¥æpºâ v1.zip (802.92 KB)
|
|