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

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

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

°Ñ¼Æ¤º®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 " & _

Macro_SQL_UDF.zip (13.07 KB)

Macro_SQL_UDF.zip (13.07 KB)

¿é¤J2­Ó°Ñ¼Æ Source Code¡C

  1. Option Explicit
  2. Option Base 0

  3. Sub main()
  4.     Call SQL_Insurance_Burden
  5. End Sub

  6. Function Multiply_2_No_Round_Down_4_Up_5(Number1 As Single, Number2 As Single) As Long
  7. ' ¥Øªº
  8. ' 2¼Æ¬Û­¼¦A°µ4±Ë5¤J
  9.      Multiply_2_No_Round_Down_4_Up_5 = Int(Number1 * Number2 + 0.5)
  10. End Function

  11. Sub SQL_Insurance_Burden()
  12. Const Output_Sht = "¿é¥Xªí"
  13. Const Input_Sht = "¿é¥Xªí"
  14. Const Labor_Pension_Personal_rate = 0.1
  15.     Dim lcConnectionString As String
  16.     Dim lcConnectString, lcCommandText As String
  17.     Dim loADODBConnection As ADODB.Connection
  18.     Dim loADODBRecordset As ADODB.Recordset
  19.    
  20.     lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
  21.                             "DBQ=" + ActiveWorkbook.FullName + ";" & _
  22.                             "ReadOnly=True"

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

  31. ' ¤£ Work
  32. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as ³Ò°h­Ó¤H " & _
  33. '2 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' +  Labor_Pension_Personal_rate + ')'  as ³Ò°h­Ó¤H " & _

  34.     Set loADODBConnection = CreateObject("ADODB.Connection")
  35.     Set loADODBRecordset = CreateObject("ADODB.Recordset")
  36.    
  37.     Debug.Print lcCommandText

  38.     loADODBConnection.Open lcConnectionString
  39.     loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1



  40.     Sheets(Output_Sht).Select

  41.     Dim r, f As Integer
  42.     r = 1
  43.     For f = 0 To loADODBRecordset.Fields.Count - 1
  44.         Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name
  45.     Next
  46.      
  47.     While Not loADODBRecordset.EOF
  48.         r = r + 1
  49.         For f = 0 To loADODBRecordset.Fields.Count - 1
  50.             Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
  51.         Next
  52.         
  53.         loADODBRecordset.MoveNext
  54.     Wend

  55. End Sub
½Æ»s¥N½X

TOP

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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

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

TOP

¦^´_ 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]"
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD