[µo°Ý] ¦p¦ó§Q¥ÎVBA¹ï¥t­ÓÀɮתº¸ê®Æ®w¶i¦æ¿z¿ï¡AµM«á±N¿ï¥Xªº­È¦s¤Jªí®æ¤¤

colinyang

­Ó¤H¹ïVBA¸û¬°¥Í²¨¡A¦ý¬O¥Ø«e¥\¯à¨ç¼Æ¤wµLªkº¡¨¬¡A¬Gµo°Ý«e¨Ó¨D§U­Ó¦ìVBA±M®a..
¤]§Æ±æÂǦ¹¨Ó´£¤É¦Û¨­VBAªºµ{«×¡A«áÄò¤]·|§V¤O¦^õX¦U¦ìªº!!!

Àɮצ³¨â­Ó
data.xlsx : ¸ê®Æ¦Cªí

¤U¸ü (27.98 KB)

2016-4-27 13:22

list.xlsm : VBAªºForm»P¬ö¿ý°Ï°ì

¤U¸ü (19.4 KB)

2016-4-27 13:35

¦pªG§Æ±ælist.xlsmªºComboBox°w¹ïdata.xlsx¸ê®Æ¦Cªí¶i¦æ¿z¿ï
(Ãþ¦ü¦Û°Ê¿z¿ï¡A©¼¦¹Ãö«Y¬O³s°Êªº¡A®Ú¾Ú½d³òªº¿ï©w«á¡A«á­±¥i¿ïªº¿ï¶µ·|ÁY¤p¡A¨Ò¦p¥_³¡/°ê¥ß/A¾Ç®Õ¡A«n³¡/¨p¥ß/H¾Ç®Õ)
µM«á³Ì«álist.xlsmªº¤T­ÓComboBox¿ï©w«á¦A«ö¿é¤J¸ê®Æ«á¡A·|¿é¤J¦blist.xlsmªº¿é¤J¸ê®ÆÄæ¦ì¤º¡A­Y¤W¤@¦C¦³¸ê®Æ«h·|Ä~Äò¦V¤U»¼¸É

§Æ±æ¦U¦ì¥i¥HÀ°¦£¤@¤U¡A·PÁ¦U¦ìªº¨ó§U¡AÁÂÁ¤j®a.. file.rar (16.42 KB)

¸ê®Æ¦Cªí»PVBA¬ö¿ý°Ï°ì
¤U¸ü¦¸¼Æ: 10

2016-4-27 13:37

colinyang

¦^´_ 1# colinyang

À³¸Ó¬O»¡ComboBox¤ñ¸û¹³¬O³s°Ê¿ï³æ¡A¦]¬°¨S¦³­n°w¹ï­ì©l¸ê®Æ¶i¦æ¿z¿ï¡A¥u¦³¤U©Ô¦¡¿ï³æ­n¸ê®Æ³s°Ê¦Ó¤w..
¤£¦n·N«ä..

lpk187

¦^´_ 2# colinyang

§âµ{¦¡½X¶K¨ìUserForm1¤¤¸Õ¸Õ

1. Option Explicit
   
2. Dim myCon As Object, myRs As Object, SQL$
   
3. Private Sub ComboBox1_Click()
   
4.     ComboBox2.Clear
   
5.     ComboBox3.Clear
   
6.     SQL = "SELECT ¤½¨p¥ß" & _
   
7.           " FROM [¾Ç®Õ¦W³æ$]" & _
   
8.           " Where °Ï°ì Like '" & ComboBox1.Value & "'" & _
   
9.           " GROUP BY ¤½¨p¥ß;"
   
10.     Set myRs = myCon.Execute(SQL)
    
11.     ComboBox2.List = Application.Transpose(myRs.GetRows)
    
12. End Sub
    
13. 
    
14. Private Sub ComboBox2_Click()
    
15.     ComboBox3.Clear
    
16.     SQL = "SELECT ¾Ç®Õ" & _
    
17.           " FROM [¾Ç®Õ¦W³æ$]" & _
    
18.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
    
19.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
    
20.     Set myRs = myCon.Execute(SQL)
    
21.     ComboBox3.List = Application.Transpose(myRs.GetRows)
    
22. End Sub
    
23. 
    
24. Private Sub CommandButton1_Click() '¿é¤H¸ê®Æ
    
25.     Dim ro
    
26.     With Sheets("¤u§@ªí1")
    
27.         ro = .Cells(Rows.Count, 2).End(xlUp).Row + 1
    
28.         .Cells(ro, 2) = ComboBox1.Value
    
29.         .Cells(ro, 3) = ComboBox2.Value
    
30.         .Cells(ro, 4) = ComboBox3.Value
    
31.     End With
    
32.     ComboBox3.Clear
    
33.     ComboBox2.Clear
    
34.     ComboBox1.Value = ""
    
35. End Sub
    
36. 
    
37. Private Sub CommandButton2_Click() 'Â÷¶}
    
38.     Unload Me
    
39. End Sub
    
40. 
    
41. Private Sub UserForm_Initialize()
    
42.     Set myCon = CreateObject("ADODB.Connection")
    
43.     Set myRs = CreateObject("ADODB.Recordset")
    
44.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
    
45.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
    
46.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
    
47.     SQL = "SELECT °Ï°ì" & _
    
48.           " FROM [¾Ç®Õ¦W³æ$]" & _
    
49.           " GROUP BY °Ï°ì;"
    
50.     Set myRs = myCon.Execute(SQL)
    
51.     ComboBox1.List = Application.Transpose(myRs.GetRows)
    
52.     Set myRs = Nothing
    
53. '    myCon.Close
    
54. '    Set myCon = Nothing
    
55. End Sub

½Æ»s¥N½X

colinyang

¦^´_ 3# lpk187

¹ê»Ú´ú¸Õ¡A¤U©Ô¿ï³æ»P¿é¤J¬ÒµL²§±`¡A³o¹ï§Ú¦Ó¨¥¯uªº¬O¤@­Ó¸g¨åªº½d¨Ò¡A¯uªº¤Q¤Àªº·PÁÂ
«áÄò±N·|¦A¶i¦æ¤À­¶ªº¼W¥[¦A¶i¦æ´ú¸Õ¡A¦n¦n¸ÑªR¤@¤Uµ{¦¡½X¤F¡A­Y¦³¥ô¦ó°ÝÃD·|¦A»P±z½Ð±Ð
¯uªº¬O¤Ó·PÁ¤F!!!

colinyang

¦^´_ 3# lpk187

Dear lpk187,

¦pªG§ÚªºData¤u§@ªí¦A¥[¤J¤@­Ó"¾Ç®Õ¦W³æ2"

¤U¸ü (25.99 KB)
¼W¥[¤@¤u§@ªí

2016-4-29 20:03

§Ú¦bList¦b·s¼W1­ÓComboBox4¨Ó¶i¦æ¤Á´«§@·~
¤w¦bForm Initialªº®É­ÔaddItem¶iComboBox4¤F

¤U¸ü (9.92 KB)
·s¼W¤@­ÓComboBox4¨Ó¶i¦æ¤u§@ªí¤Á´«

2016-4-29 20:03

µ²ªG¦bSQL»yªk¤¤§Q¥Î"&"¶i¦æSQL¦r¦ê»PÅܼƳs±µ®É¡A°õ¦æ®É«o³y¦¨¥H¤U²§±`¡A§Ú¸Ó¦p¦ó­×¥¿§Úªº»yªk¨Ó¹F¨ìÅܧ󪺥تº©O...

¤U¸ü (11.3 KB)
ERROR

2016-4-29 20:03

¥H¤U¬°µ{¦¡½X¡A¨Ãªþ¤WÀɮסAÁٽбz«ü±Ð..ÁÂÁÂ
File2.rar (29.22 KB)

¸ê®Æªí»P¿é¤Jªí
¤U¸ü¦¸¼Æ: 5

2016-4-29 20:07

1. Option Explicit
   
2. Dim myCon As Object, myRs As Object, SQL$
   
3. Private Sub ComboBox1_Click()
   
4.     ComboBox2.Clear
   
5.     ComboBox3.Clear
   
6.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
   
7.     SQL = "SELECT ¤½¨p¥ß" & _
   
8.           " FROM [& ComboBox4.Value &$]" & _
   
9.           " Where °Ï°ì Like '" & ComboBox1.Value & "'" & _
   
10.           " GROUP BY ¤½¨p¥ß;"
    
11.     Set myRs = myCon.Execute(SQL)
    
12.     ComboBox2.List = Application.Transpose(myRs.GetRows)
    
13. End Sub
    
14. 
    
15. Private Sub ComboBox2_Click()
    
16.     ComboBox3.Clear
    
17.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
    
18.     SQL = "SELECT ¾Ç®Õ" & _
    
19.           " FROM [& ComboBox4.Value &$]" & _
    
20.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
    
21.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
    
22.     Set myRs = myCon.Execute(SQL)
    
23.     ComboBox3.List = Application.Transpose(myRs.GetRows)
    
24. End Sub
    
25. 
    
26. Private Sub CommandButton1_Click() '¿é¤H¸ê®Æ
    
27.     Dim ro
    
28.     With Sheets("¤u§@ªí1")
    
29.         ro = .Cells(Rows.Count, 2).End(xlUp).Row + 1
    
30.         .Cells(ro, 2) = ComboBox1.Value
    
31.         .Cells(ro, 3) = ComboBox2.Value
    
32.         .Cells(ro, 4) = ComboBox3.Value
    
33.     End With
    
34.     ComboBox3.Clear
    
35.     ComboBox2.Clear
    
36.     ComboBox1.Value = ""
    
37. End Sub
    
38. 
    
39. Private Sub CommandButton2_Click() 'Â÷¶}
    
40.     Unload Me
    
41. End Sub
    
42. 
    
43. Private Sub UserForm_Initialize()
    
44.     Set myCon = CreateObject("ADODB.Connection")
    
45.     Set myRs = CreateObject("ADODB.Recordset")
    
46.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
    
47.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
    
48.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
    
49.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
    
50.     SQL = "SELECT °Ï°ì" & _
    
51.           " FROM [& ComboBox4.Value &$]" & _
    
52.           " GROUP BY °Ï°ì;"
    
53.     Set myRs = myCon.Execute(SQL)
    
54.     ComboBox1.List = Application.Transpose(myRs.GetRows)
    
55.     Set myRs = Nothing
    
56.    
    
57. '   ¥[¤JComboBox4ªºVlaue
    
58.     ComboBox4.AddItem "¾Ç®Õ¦W³æ"
    
59.     ComboBox4.AddItem "¾Ç®Õ¦W³æ2"
    
60. '    myCon.Close
    
61. '    Set myCon = Nothing
    
62. End Sub

½Æ»s¥N½X

Best Regards,
Colin

lpk187

¦^´_ 5# colinyang

¤U­±¥N½XºUÂøµÛADODB¤ÎSQL»y¨¥¡A­Y»Ý¨D§ó¦h»¡©ú¡A½Ð¦Û¦æGoogle

1. Option Explicit
   
2. Dim myCon As Object, myRs As Object, SQL$
   
3. Private Sub ComboBox1_Click()
   
4.     ComboBox2.Clear
   
5.     ComboBox3.Clear
   
6.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
   
7.     SQL = "SELECT ¤½¨p¥ß" & _
   
8.           " FROM [" & ComboBox4.Value & "$]" & _
   
9.           " Where °Ï°ì Like '" & ComboBox1.Value & "'" & _
   
10.           " GROUP BY ¤½¨p¥ß;"
    
11.     Set myRs = myCon.Execute(SQL)
    
12.     ComboBox2.List = Application.Transpose(myRs.GetRows)
    
13. End Sub
    
14. 
    
15. Private Sub ComboBox2_Click()
    
16.     ComboBox3.Clear
    
17.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
    
18.     SQL = "SELECT ¾Ç®Õ" & _
    
19.           " FROM [" & ComboBox4.Value & "$]" & _
    
20.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
    
21.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
    
22.     Set myRs = myCon.Execute(SQL)
    
23.     ComboBox3.List = Application.Transpose(myRs.GetRows)
    
24. End Sub
    
25. 
    
26. Private Sub ComboBox4_Change()
    
27.     ComboBox3.Clear
    
28.     ComboBox2.Clear
    
29.     ComboBox1.Clear
    
30.     SQL = "SELECT °Ï°ì" & _
    
31.           " FROM [" & ComboBox4.Value & "$]" & _
    
32.           " GROUP BY °Ï°ì;"
    
33.     Set myRs = myCon.Execute(SQL)
    
34.     ComboBox1.List = Application.Transpose(myRs.GetRows)
    
35.     Set myRs = Nothing
    
36. End Sub
    
37. 
    
38. Private Sub CommandButton1_Click() '¿é¤H¸ê®Æ
    
39.     Dim ro
    
40.     With Sheets("¤u§@ªí1")
    
41.         ro = .Cells(Rows.Count, 2).End(xlUp).Row + 1
    
42.         .Cells(ro, 2) = ComboBox1.Value
    
43.         .Cells(ro, 3) = ComboBox2.Value
    
44.         .Cells(ro, 4) = ComboBox3.Value
    
45.     End With
    
46.     ComboBox3.Clear
    
47.     ComboBox2.Clear
    
48.     ComboBox1.Clear
    
49.     ComboBox4.Value = ""
    
50. End Sub
    
51. 
    
52. Private Sub CommandButton2_Click() 'Â÷¶}
    
53.    
    
54.     Set myRs = Nothing
    
55.     myCon.Close
    
56.     Set myCon = Nothing
    
57.     Unload Me
    
58. End Sub
    
59. 
    
60. Private Sub UserForm_Initialize()
    
61.     Set myCon = CreateObject("ADODB.Connection")
    
62.     Set myRs = CreateObject("ADODB.Recordset")
    
63.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
    
64.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
    
65.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
    
66. '   ¥[¤JComboBox4ªºVlaue
    
67.     ComboBox4.AddItem "¾Ç®Õ¦W³æ"
    
68.     ComboBox4.AddItem "¾Ç®Õ¦W³æ2"
    
69. End Sub

½Æ»s¥N½X

colinyang

¦^´_ 6# lpk187
ÁÂÁ§A¡A³y¦¨±zªº§xÂZ¤F...©êºp

Hsieh

¦^´_ 2# colinyang

ª½Ä±¬OCombobox3²M³æ·|¨Ì¾ÚCombobox1¡ACombobox2¤º®eÅÜ°Ê
¿ï©w«á«ö¤U«ö¶s¨Ì§Ç¿é¤J

1. Public d2
   
2. 
   
3. Private Sub ComboBox1_Change()
   
4. ex
   
5. End Sub
   
6. 
   
7. Private Sub ComboBox2_Change()
   
8. ex
   
9. End Sub
   
10. 
    
11. Private Sub ComboBox4_Change()
    
12. Set d = CreateObject("Scripting.Dictionary")
    
13. Set d1 = CreateObject("Scripting.Dictionary")
    
14. Set d2 = CreateObject("Scripting.Dictionary")
    
15. fs = ThisWorkbook.Path & "\data.xlsx" '½Ð±N2Àɮשñ¦b¦P¤@¥Ø¿ý
    
16. Set databook = Workbooks.Open(fs)
    
17. With databook.Sheets(ComboBox4.Text)
    
18. For Each a In .Range(.[A2], .[A2].End(xlDown))
    
19.    d(a.Value) = "" 'AÄ椣­«½Æ²M³æ
    
20.    d1(a.Offset(, 1).Value) = "" 'BÄ椣­«½Æ²M³æ
    
21.    d2(a & a.Offset(, 1)) = IIf(d2(a & a.Offset(, 1)) = "", a.Offset(, 2), d2(a & a.Offset(, 1)) & "," & a.Offset(, 2)) 'A&BÄæ²M³æ¤º®e
    
22. Next
    
23. ComboBox1.List = d.keys
    
24. ComboBox2.List = d1.keys
    
25. End With
    
26. databook.Close 0
    
27. 
    
28. End Sub
    
29. 
    
30. Private Sub CommandButton1_Click()
    
31. Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 3) = Array(ComboBox1, ComboBox2, ComboBox3) '¿é¤J¸ê®Æ
    
32. End Sub
    
33. 
    
34. Private Sub UserForm_Initialize()
    
35. fs = ThisWorkbook.Path & "\data.xlsx" '½Ð±N2Àɮשñ¦b¦P¤@¥Ø¿ý
    
36. Set databook = Workbooks.Open(fs)
    
37. For Each sh In databook.Sheets
    
38.    ComboBox4.AddItem sh.Name
    
39. Next
    
40. databook.Close 0
    
41. End Sub
    
42. Sub ex()
    
43. mystr = ComboBox1 & ComboBox2
    
44. If d2(mystr) <> "" Then ComboBox3.List = Split(d2(mystr), ",") 'Combobox3ªº²M³æ
    
45. End Sub

½Æ»s¥N½X

colinyang

¦^´_ 8# Hsieh

ÁÂÁª©¥D¡A¬Ý°_¨Ó¤S¬O¥t¥~¤@ºØ¤è¦¡¨Ó¶i¦æ¿ï¨ú©O!·|¦n¦n§l¦¬ªº!¤£¹L¦bCombobox4¿ï¨ú®É·|¦³µe­±§ó·sªº°Ê§@
¥[¤JApplication.ScreenUpdating = False¡A¥i¥H§í¤î§ó·sª¬ªp¡A¦ý¬O¦P¼Ë¿é¤Jªº®É­Ô¸ê®Æ¤£·|¯B²{..¥²¶·­«·s¥´¶}Àɮפ~¬Ýªº¨ì
¦ý¬Oµ{¦¡½XÁÙ¬O«Ü­È±o¾Ç²ß¡AÁÂÁ©O!!!

colinyang

¦U¦ì¦n..¦³µo²{­Ó°ÝÃD·Q°Ý¤F..¬OÃö©ó¸ê®Æ¦³Nullªº³¡¤À

¤U¸ü (41.62 KB)
¸ê®Æ¦³NULL

2016-5-2 12:51

¦bÂà´«®É¦]¬°·|¦³¦r¦ê»PNULLªº°ÝÃD¡A©Ò¥HÂà´«¸ê®Æ·|¥X²{²§±`

¤U¸ü (20.41 KB)
Âà´«®É¥X²{²§±`

2016-5-2 12:51

¦³¬d¸ß¨ìSQL¥i¨Ï¥ÎCASE¶i¦æIS NOT NULL§PŪ..¥i¬O±µµÛ¦bCASE IS NOT NULL "WHEN"ªº®É­Ô
ADODB»{¤£¥XWHENªº¤èªk..½Ð°Ý¸Ó¦p¦ó±NNULL²¾°£©O..ÁÂÁÂ