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

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

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

­Ó¤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ªí

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


¦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)

¦^´_ 1# colinyang

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

TOP

¦^´_ 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. Private Sub ComboBox2_Click()
  14.     ComboBox3.Clear
  15.     SQL = "SELECT ¾Ç®Õ" & _
  16.           " FROM [¾Ç®Õ¦W³æ$]" & _
  17.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
  18.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
  19.     Set myRs = myCon.Execute(SQL)
  20.     ComboBox3.List = Application.Transpose(myRs.GetRows)
  21. End Sub

  22. Private Sub CommandButton1_Click() '¿é¤H¸ê®Æ
  23.     Dim ro
  24.     With Sheets("¤u§@ªí1")
  25.         ro = .Cells(Rows.Count, 2).End(xlUp).Row + 1
  26.         .Cells(ro, 2) = ComboBox1.Value
  27.         .Cells(ro, 3) = ComboBox2.Value
  28.         .Cells(ro, 4) = ComboBox3.Value
  29.     End With
  30.     ComboBox3.Clear
  31.     ComboBox2.Clear
  32.     ComboBox1.Value = ""
  33. End Sub

  34. Private Sub CommandButton2_Click() 'Â÷¶}
  35.     Unload Me
  36. End Sub

  37. Private Sub UserForm_Initialize()
  38.     Set myCon = CreateObject("ADODB.Connection")
  39.     Set myRs = CreateObject("ADODB.Recordset")
  40.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
  41.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
  42.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
  43.     SQL = "SELECT °Ï°ì" & _
  44.           " FROM [¾Ç®Õ¦W³æ$]" & _
  45.           " GROUP BY °Ï°ì;"
  46.     Set myRs = myCon.Execute(SQL)
  47.     ComboBox1.List = Application.Transpose(myRs.GetRows)
  48.     Set myRs = Nothing
  49. '    myCon.Close
  50. '    Set myCon = Nothing
  51. End Sub
½Æ»s¥N½X

TOP

¦^´_ 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!!!

TOP

¦^´_ 3# lpk187

Dear lpk187,

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

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

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


¥H¤U¬°µ{¦¡½X¡A¨Ãªþ¤WÀɮסAÁٽбz«ü±Ð..ÁÂÁÂ
File2.rar (29.22 KB)
  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. Private Sub ComboBox2_Click()
  15.     ComboBox3.Clear
  16.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
  17.     SQL = "SELECT ¾Ç®Õ" & _
  18.           " FROM [& ComboBox4.Value &$]" & _
  19.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
  20.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
  21.     Set myRs = myCon.Execute(SQL)
  22.     ComboBox3.List = Application.Transpose(myRs.GetRows)
  23. End Sub

  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. Private Sub CommandButton2_Click() 'Â÷¶}
  37.     Unload Me
  38. End Sub

  39. Private Sub UserForm_Initialize()
  40.     Set myCon = CreateObject("ADODB.Connection")
  41.     Set myRs = CreateObject("ADODB.Recordset")
  42.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
  43.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
  44.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
  45.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
  46.     SQL = "SELECT °Ï°ì" & _
  47.           " FROM [& ComboBox4.Value &$]" & _
  48.           " GROUP BY °Ï°ì;"
  49.     Set myRs = myCon.Execute(SQL)
  50.     ComboBox1.List = Application.Transpose(myRs.GetRows)
  51.     Set myRs = Nothing
  52.    
  53. '   ¥[¤JComboBox4ªºVlaue
  54.     ComboBox4.AddItem "¾Ç®Õ¦W³æ"
  55.     ComboBox4.AddItem "¾Ç®Õ¦W³æ2"
  56. '    myCon.Close
  57. '    Set myCon = Nothing
  58. End Sub
½Æ»s¥N½X
Best Regards,
Colin

TOP

¦^´_ 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. Private Sub ComboBox2_Click()
  15.     ComboBox3.Clear
  16.     '¼W¥[¤FComboBox4.Value¦Ü FROMªº«á­±¨Ó¤Á´«¤u§@ªí
  17.     SQL = "SELECT ¾Ç®Õ" & _
  18.           " FROM [" & ComboBox4.Value & "$]" & _
  19.           " GROUP BY ¾Ç®Õ, [°Ï°ì] & [¤½¨p¥ß]" & _
  20.           " HAVING [°Ï°ì] & [¤½¨p¥ß] Like '" & ComboBox1.Value & ComboBox2.Value & "';"
  21.     Set myRs = myCon.Execute(SQL)
  22.     ComboBox3.List = Application.Transpose(myRs.GetRows)
  23. End Sub

  24. Private Sub ComboBox4_Change()
  25.     ComboBox3.Clear
  26.     ComboBox2.Clear
  27.     ComboBox1.Clear
  28.     SQL = "SELECT °Ï°ì" & _
  29.           " FROM [" & ComboBox4.Value & "$]" & _
  30.           " GROUP BY °Ï°ì;"
  31.     Set myRs = myCon.Execute(SQL)
  32.     ComboBox1.List = Application.Transpose(myRs.GetRows)
  33.     Set myRs = Nothing
  34. End Sub

  35. Private Sub CommandButton1_Click() '¿é¤H¸ê®Æ
  36.     Dim ro
  37.     With Sheets("¤u§@ªí1")
  38.         ro = .Cells(Rows.Count, 2).End(xlUp).Row + 1
  39.         .Cells(ro, 2) = ComboBox1.Value
  40.         .Cells(ro, 3) = ComboBox2.Value
  41.         .Cells(ro, 4) = ComboBox3.Value
  42.     End With
  43.     ComboBox3.Clear
  44.     ComboBox2.Clear
  45.     ComboBox1.Clear
  46.     ComboBox4.Value = ""
  47. End Sub

  48. Private Sub CommandButton2_Click() 'Â÷¶}
  49.    
  50.     Set myRs = Nothing
  51.     myCon.Close
  52.     Set myCon = Nothing
  53.     Unload Me
  54. End Sub

  55. Private Sub UserForm_Initialize()
  56.     Set myCon = CreateObject("ADODB.Connection")
  57.     Set myRs = CreateObject("ADODB.Recordset")
  58.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
  59.                "Data Source=" & ThisWorkbook.Path & "\data.xlsx;" & _
  60.                "Extended Properties=""Excel 12.0;HDR=Yes;"";"
  61. '   ¥[¤JComboBox4ªºVlaue
  62.     ComboBox4.AddItem "¾Ç®Õ¦W³æ"
  63.     ComboBox4.AddItem "¾Ç®Õ¦W³æ2"
  64. End Sub
½Æ»s¥N½X

TOP

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2016-4-29 23:56 ½s¿è

¦^´_ 2# colinyang

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

  2. Private Sub ComboBox1_Change()
  3. ex
  4. End Sub

  5. Private Sub ComboBox2_Change()
  6. ex
  7. End Sub

  8. Private Sub ComboBox4_Change()
  9. Set d = CreateObject("Scripting.Dictionary")
  10. Set d1 = CreateObject("Scripting.Dictionary")
  11. Set d2 = CreateObject("Scripting.Dictionary")
  12. fs = ThisWorkbook.Path & "\data.xlsx" '½Ð±N2Àɮשñ¦b¦P¤@¥Ø¿ý
  13. Set databook = Workbooks.Open(fs)
  14. With databook.Sheets(ComboBox4.Text)
  15. For Each a In .Range(.[A2], .[A2].End(xlDown))
  16.    d(a.Value) = "" 'AÄ椣­«½Æ²M³æ
  17.    d1(a.Offset(, 1).Value) = "" 'BÄ椣­«½Æ²M³æ
  18.    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
  19. Next
  20. ComboBox1.List = d.keys
  21. ComboBox2.List = d1.keys
  22. End With
  23. databook.Close 0

  24. End Sub

  25. Private Sub CommandButton1_Click()
  26. Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 3) = Array(ComboBox1, ComboBox2, ComboBox3) '¿é¤J¸ê®Æ
  27. End Sub

  28. Private Sub UserForm_Initialize()
  29. fs = ThisWorkbook.Path & "\data.xlsx" '½Ð±N2Àɮשñ¦b¦P¤@¥Ø¿ý
  30. Set databook = Workbooks.Open(fs)
  31. For Each sh In databook.Sheets
  32.    ComboBox4.AddItem sh.Name
  33. Next
  34. databook.Close 0
  35. End Sub
  36. Sub ex()
  37. mystr = ComboBox1 & ComboBox2
  38. If d2(mystr) <> "" Then ComboBox3.List = Split(d2(mystr), ",") 'Combobox3ªº²M³æ
  39. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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!!!

TOP

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

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

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

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD