¦bEXCEL VBA¤¤ªºMS SQL¬d¸ß»yªk¡A¦p¦ó¥Î¿é¤Jªº¤è¦¡²£¥Í¡H
- ©«¤l
- 8
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 15
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-11-8
- ³Ì«áµn¿ý
- 2012-12-12
|
¦^´_ 2# stillfish00
«D±`·PÁ¤j¤j¡A§Ú«ö·Ó¤j¤jªº¤è¦¡µ{¦¡½X§ï¤F¡A¦ý¬O§Ú·Qn¥ô·N¿é¤J¤é´Á½d³ò¸Ó«ç»ò§ï©O¡H§Ú³]p¨âÓ¿é¤J¤å¦r¤è¶ô¤é´Á°_ÂITextBox1©M¤é´Áµ²§ô TextBox2¡A
§Ú¥Ø«eVBA»yªk¬O±NMS SQL¬d¸ß¸ê®ÆŪ¨ú¨ìEXCEL sheet¤W¡A¦pªG«ö·Ó¤j¤j¤è¦¡¬O¥i¥HŪ¨ú¡A¦ý¬O§Ú¥[¤JTextBox1,TextBox2´N¦³°ÝÃD¤F¡C
-----------------------------------------------------------------------------------------------------------------------------------------------------
Sub «ö¶s2_Click() '©w¸q¹Lµ{¦WºÙ
Dim i As Integer, j As Integer, sht As Worksheet 'i,j¬°¾ã¼ÆÅܼơFsht ¬°excel¤u§@ªíª«¥óÅܼơA«ü¦V¬Y¤@¤u§@ªí
Set cn = New ADODB.Connection '©w¸q¸ê®Æ³sµ²ª«¥ó ¡A«O¦s³s±µ¸ê®Æ®w¸ê°T¡F½Ð¥ý¤Þ¥ÎADO
Dim rs As New ADODB.Recordset '©w¸q°O¿ýª«¥ó
Dim strCn As String, strSQL As String '¦r²Å¦êÅܶq
Dim sStartDate As String, sEndDate As String '¦r²Å¦êÅܶq
sStartDate = "20120101" <====§Ú§ï¦¨sStartDate = "TextBox1"
sEndDate = "20121231" <====§Ú§ï¦¨sEndDate = "TextBox2" ·|¦³¿ù»~°T®§(±q¦r¤¸¦r¦êÂà´«¨ìdatetime®ÉÂà´«¥¢±Ñ)
strCn = ("driver={SQL Server};server=10.0.X.X;uid=XX;pwd=XXXXXXXX;database=XXXX_XXXX;") '©w¸q¸ê®Æ®w³sµ²¦r¦ê
strSQL = "SELECT ApplyerDeptName,ApplyName,HR10day,sum(HRdays)as UsedDay,(HR10day-sum(HRdays))as UnusedDay FROM afu_form_HR10 inner join TEST on afu_form_HR10.ApplyName=TEST.HR10name where ApplyDateTime Between '" & sStartDate & " 'AND' " & sEndDate & "' and Excuse='¯S¥ð°²' group by ApplyerDeptName,ApplyName,HR10day"
cn.Open strCn '»P¸ê®Æ®w«Ø¥ß³s±µ¡A¦pªG¦¨¥\¡Aªð¦^³s½uª«¥ócn
rs.Open strSQL, cn '°õ¦æstrSQL©Ò§tªºSQL©R¥O¡Aµ²ªG«O¦s¦brs°O¿ý¶°ª«¥ó¤¤
i = 2
Set sht = ThisWorkbook.Worksheets("sheet1") '§âsht«ü¦V·í«e¤u§@諸sheet1¤u§@ªí
Do While Not rs.EOF
sht.Cells(i, 1) = rs("ApplyerDeptName")
sht.Cells(i, 2) = rs("ApplyName")
sht.Cells(i, 3) = rs("HR10day")
sht.Cells(i, 4) = rs("UsedDay")
sht.Cells(i, 5) = rs("UnusedDay")
rs.MoveNext
i = i + 1
Loop
rs.Close
End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------- |
|
¿·
|
|
|
|
|
- ©«¤l
- 1018
- ¥DÃD
- 15
- ºëµØ
- 0
- ¿n¤À
- 1058
- ÂI¦W
- 0
- §@·~¨t²Î
- win7 32bit
- ³nÅ骩¥»
- Office 2016 64-bit
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ®ç¶é
- µù¥U®É¶¡
- 2012-5-9
- ³Ì«áµn¿ý
- 2022-9-28
|
¦^´_ 1# badboy01
¦]¬°¨Sµ¹«e«áªºµ{¦¡½X , °²³]§A³o¬q¬°¦r¦ê..........
"......where ApplyDateTime Between '20120101' and '20121231' and Excuse='¯S¥ð°²'......"
'«Å§i¦r¦êÅܼÆ
Dim sStartDate as string , sEndDate as string
sStartDate = "20120101"
sEndDate = "20121231"
'¤W±ªº¨º¬q¦r¦ê§ï¬°
"......where ApplyDateTime Between '" & sStartDate & "' and '" & sEndDate & "' and Excuse='¯S¥ð°²'......" |
|
|
|
|
|
|