- ©«¤l
- 132
- ¥DÃD
- 25
- ºëµØ
- 0
- ¿n¤À
- 199
- ÂI¦W
- 0
- §@·~¨t²Î
- window XP
- ³nÅ骩¥»
- Excel 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW ·s¦Ë
- µù¥U®É¶¡
- 2010-6-1
- ³Ì«áµn¿ý
- 2022-7-14
|
¿é¤J2Ó°Ñ¼Æ Source Code¡C
- Option Explicit
- Option Base 0
- Sub main()
- Call SQL_Insurance_Burden
- End Sub
- Function Multiply_2_No_Round_Down_4_Up_5(Number1 As Single, Number2 As Single) As Long
- ' ¥Øªº
- ' 2¼Æ¬Û¼¦A°µ4±Ë5¤J
- Multiply_2_No_Round_Down_4_Up_5 = Int(Number1 * Number2 + 0.5)
- End Function
- Sub SQL_Insurance_Burden()
- Const Output_Sht = "¿é¥Xªí"
- Const Input_Sht = "¿é¥Xªí"
- Const Labor_Pension_Personal_rate = 0.1
- Dim lcConnectionString As String
- Dim lcConnectString, lcCommandText As String
- Dim loADODBConnection As ADODB.Connection
- Dim loADODBRecordset As ADODB.Recordset
-
- lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
- "DBQ=" + ActiveWorkbook.FullName + ";" & _
- "ReadOnly=True"
-
- 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]"
-
- ' Work
- '1 " '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' + trim(str(0.1)) + ')' as ³Ò°hÓ¤H " & _
- ' ¤£ Work
- '1 " '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' + trim(str(Labor_Pension_Personal_rate)) + ')' as ³Ò°hÓ¤H " & _
- '2 " '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(³Ò°h)) + ',' + Labor_Pension_Personal_rate + ')' as ³Ò°hÓ¤H " & _
- Set loADODBConnection = CreateObject("ADODB.Connection")
- Set loADODBRecordset = CreateObject("ADODB.Recordset")
-
- Debug.Print lcCommandText
- loADODBConnection.Open lcConnectionString
- loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1
- Sheets(Output_Sht).Select
- Dim r, f As Integer
- r = 1
- For f = 0 To loADODBRecordset.Fields.Count - 1
- Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name
- Next
-
- While Not loADODBRecordset.EOF
- r = r + 1
- For f = 0 To loADODBRecordset.Fields.Count - 1
- Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
- Next
-
- loADODBRecordset.MoveNext
- Wend
- End Sub
½Æ»s¥N½X |
|