[µo°Ý] VBA¨Ï¥Î SQL «ü¥O¡AUser Defined Function(¨Ï¥ÎªÌ¦Û­q¨ç¼Æ)¡A¦p¦ó¿é¤J2­Ó°Ñ¼Æ¡C

Baracuda

°Ñ¼Æ¤º®e        °Ñ¼Æ1¡G¬°Äæ¦ì¤º®e¡A°Ñ¼Æ2¡G¬°³]©wªº±`¼Æ
VBA ¤¤³]©w¤Þ¥Î        ³]©w¤Þ¥Îªº¬O Microsoft ActiveX Data Objects 2.0 Library
¤w¸g¸Õ¹Lªº¤º®e        ª½±µ¤Þ¥Î±`¼Æ®É¥i¥H Work
        =Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(0.1)) + ')'  as ³Ò°h­Ó¤H " & _

        «ü©w°Ñ¼Æ(«Å§i¬°±`¼Æ)¤£Work
        Const Labor_Pension_Personal_rate = 0.1
        =Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as ³Ò°h­Ó¤H " & _
        ¿ù»~°T®§¡G°Ñ¼Æ¤Ó¤Ö¡A¹w´Á­Ó¼Æ1
°Ñ¦Ò¤º®e        Excel ¥¨¶°Å]ªk®Ñ  §õ¼ç·çµÛ page99
°ÝÃD­I´º        ·Q­n¦b Excel VBA¤¤¨Ï¥ÎSQL¨Ó­pºâ³Ò°·«O¤Î³Ò°hªº­Ó¤H­t¾áª÷ÃB       
            lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
                        " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
                        "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as ³Ò°h­Ó¤H " & _

Baracuda

1. Option Explicit
   
2. Option Base 0
   
3. 
   
4. Sub main()
   
5.     Call SQL_Insurance_Burden
   
6. End Sub
   
7. 
   
8. Function Multiply_2_No_Round_Down_4_Up_5(Number1 As Single, Number2 As Single) As Long
   
9. ' ¥Øªº
   
10. ' 2¼Æ¬Û­¼¦A°µ4±Ë5¤J
    
11.      Multiply_2_No_Round_Down_4_Up_5 = Int(Number1 * Number2 + 0.5)
    
12. End Function
    
13. 
    
14. Sub SQL_Insurance_Burden()
    
15. Const Output_Sht = "¿é¥Xªí"
    
16. Const Input_Sht = "¿é¥Xªí"
    
17. Const Labor_Pension_Personal_rate = 0.1
    
18.     Dim lcConnectionString As String
    
19.     Dim lcConnectString, lcCommandText As String
    
20.     Dim loADODBConnection As ADODB.Connection
    
21.     Dim loADODBRecordset As ADODB.Recordset
    
22.    
    
23.     lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
    
24.                             "DBQ=" + ActiveWorkbook.FullName + ";" & _
    
25.                             "ReadOnly=True"
    
26. 
    
27.                
    
28.     lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
    
29.                 " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
    
30.                 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as ³Ò°h­Ó¤H " & _
    
31.                 "FROM " & "[" & Input_Sht & "$" & "A1:P32]"
    
32.                
    
33. '    Work
    
34. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(0.1)) + ')'  as ³Ò°h­Ó¤H " & _
    
35. 
    
36. ' ¤£ Work
    
37. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as ³Ò°h­Ó¤H " & _
    
38. '2 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  Labor_Pension_Personal_rate + ')'  as ³Ò°h­Ó¤H " & _
    
39. 
    
40.     Set loADODBConnection = CreateObject("ADODB.Connection")
    
41.     Set loADODBRecordset = CreateObject("ADODB.Recordset")
    
42.    
    
43.     Debug.Print lcCommandText
    
44. 
    
45.     loADODBConnection.Open lcConnectionString
    
46.     loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1
    
47. 
    
48. 
    
49. 
    
50.     Sheets(Output_Sht).Select
    
51. 
    
52.     Dim r, f As Integer
    
53.     r = 1
    
54.     For f = 0 To loADODBRecordset.Fields.Count - 1
    
55.         Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name
    
56.     Next
    
57.      
    
58.     While Not loADODBRecordset.EOF
    
59.         r = r + 1
    
60.         For f = 0 To loADODBRecordset.Fields.Count - 1
    
61.             Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
    
62.         Next
    
63.         
    
64.         loADODBRecordset.MoveNext
    
65.     Wend
    
66. 
    
67. End Sub

½Æ»s¥N½X

kimbal

Baracuda µoªí©ó 2012-1-28 15:49

¸Õ¸Õ§âsql´«¦¨³o­Ó¼Ë¤l

1.         lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
   
2.                 " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
   
3.                 "  '=Multiply_2_No_Round_Down_4_Up_5(' + ³Ò°h + '," + Trim(Str(Labor_Pension_Personal_rate)) + ")'  as ³Ò°h­Ó¤H " & _
   
4.                 "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

½Æ»s¥N½X

Baracuda

Error Message °Ñ¼Æ¤Ó¤Ö¡A¹w´Á­Ó¼Æ1
ÁÙ¬O¦³°ÝÃD¡C
§Ú©|¤£ª¾¦p¦ó¸Ñ¡C
¥i¯à¥Î SQL ¬d¥X«á¦A¥t¥Î VBA ¼g¹L¡C

Hsieh

¦^´_ 4# Baracuda
§Aªº·N«ä¤£¬O­n±N¦r¦ê¤¤ªº±`¼Æ¥ÎÅܼƨú¥N¶Ü?
    lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
                " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(" & Labor_Pension_Personal_rate & ")) + ')'  as ³Ò°h­Ó¤H " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

Baracuda

¬Oªº¡Aª©¥D§Ú¸Õ¬Ý¬Ý¡C

Baracuda

Hsieh ª©¥D¡G
§Úªº·N«ä¤£¬O­n±N¦r¦ê¤¤ªº±`¼Æ¥ÎÅܼƨú¥N¶Ü¡C
¦ý¬O»yªk¦n¹³¥u¯à¥ÎÄæ¦ì¡C
¦pªGVBA ¤¤³]©wªºÅܼÆ(or±`¼Æ)·|¦³°ÝÃD¡C

Scott090

¦^´_ 7# Baracuda

¯ÂÄÝ°Q½×¤Î­Ó¤H¬Ýªk

VBA ADO SQL ¦n¹³¨Ã¤£¤ä´©ÅܼƹBºâ¡A
UDF¤]¥u¬O¦bµ²ªGÄæ¦ì©ñ¤J­pºâ¦¡¦Ó¤£¬OADO·|ª½±µ­pºâ
³o­ÓÃD¥Ø¡A­Ó¤Hªº¬Ýªk¬OADOªº¤ä´©¯à¤O°ÝÃD¦Ó¤£¬O UDF ¸Ì¦³´X­Ó°Ñ¼Æ

¥DÄæ¦ì¤]¤@¼Ë¤£¯à¥ÎÅܼƭpºâ¡G
ÀH·NÁ|¨Ò "®Ö©w¥»Á~"
Const aa=2

¥i¥H­pºâ¡F ®Ö©w¥»Á~ * ª½±µ±`¼Æ 2
lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~ * 2 as bb,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
                " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(0.1)) + ')'  as ³Ò°h­Ó¤H " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

¥i¥H­pºâ¡F ®Ö©w¥»Á~ *  " &  aa  & " ¡AÅܼƤw¦b¦¨¥ß  lcCommandText ¦r¦ê®ÉÂà¤Æ¤F aa ¬° 2¡A2 ¬O±`¼Æ¤£¬OÅܼÆ
lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~ *  " &  aa  & " as bb,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
                " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(0.1)) + ')'  as ³Ò°h­Ó¤H " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

¤£·|­pºâ¡F ®Ö©w¥»Á~ * aa  ªºÅÜ¼Æ aa ¬O lcCommandText ¦r¦êªº¤@³¡¤À¡A ADO¨S¤ä´©¦r¦ê¤ºªºÅܼÆ
lcCommandText = "SELECT §Ç¸¹, ©m¦W,  ®Ö©w¥»Á~ * aa as bb,  ®Ö©wÁZ®Ä , Á~¸êÁ`ÃB , ³Ò«OÁ~¸ê , ³Ò°hÁ~¸ê , °·«OÁ~¸ê, " & _
                " ³Ò°h , ¾¨a, ´¶³q¨Æ¬G, ´N·~«OÀI, ¤u¸ê¹ÔÀv, ¥þ¥Á°·«O, ¥þ¥Á°·«OÂÂ, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(0.1)) + ')'  as ³Ò°h­Ó¤H " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

Scott090

¦^´_ 7# Baracuda


    °²³]¡A
1. Excel VBA ADOSQL ¨Ã¤£¤ä´© ÅܼÆ
2. SQL¥u¬O¥i¥H§Þ¥©©Êªº¦b¥Í¦¨ªºÄæ¦ì©ñ¤J­pºâ¦¡¡A¦¹­pºâ¦¡¥i¥Hºtºâ­ì©lÄæ¦ì¸ê®Æ

¨º´N¦b Excel Àx¦s®æ ©ñ¤J­pºâ¦¡¨Ó©I¥s VBA¤ºªº UDF¡A
¦¹¦Û­q¨ç¼Æ´N¸û¦³¼e¼sªº¹B§@ªÅ¶¡¤F