¦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
|
¦bEXCEL VBA¤¤ªºMS SQL¬d¸ß»yªk¡A¦p¦ó¥Î¿é¤Jªº¤è¦¡²£¥Í¡H
§ÚªºEXCEL VBA ¤¤¦³¤@¬q¬d¸ßMS SQL»yªk (............where ApplyDateTime Between '20120101' and '20121231' and Excuse='¯S¥ð°²'...........)¡A¸Ó¦p¦ó±N¤é´Á»P°²§Oªº³¡¤À¥ÎVBA¤å¦r¤è¶ô¤è¦¡¿é¤J¤é´Á½d³ò©M°²§O¥i¬d¸ß¥X§Únªºµ²ªG¡HÁÂÁ¡I
PS¡G¦]¬°¦bVBA¤¤¥i¥H°õ¦æSQL»yªk¡A¬d¸ß¸ê®Æ¡A©Ò¥H§Ú·Qn³o¬qSQL»yªk¦bVBA¤¤¥i¥H¥ÎÅܼƤ覡¥h¬d¸ß¡C |
|
¿·
|
|
|
|
|
- ©«¤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¥ð°²'......" |
|
|
|
|
|
|
- ©«¤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
|
¦^´_ 3# badboy01
sStartDate =TextBox1.Text
sEndDate =TextBox2.Text |
|
|
|
|
|
|
- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-12-7 16:42 ½s¿è
¦^´_ 3# badboy01 - where ApplyDateTime Between '" & Format(TextBox1.Text, "yyyymmdd") & "' AND '" & Format(TextBox2.Text, "yyyymmdd") & "' and Excuse='¯S¥ð°²'
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤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
|
¦^´_ badboy01
sStartDate =TextBox1.Text
sEndDate =TextBox2.Text
stillfish00 µoªí©ó 2012-12-7 16:31
§Ú¤j·§µo²{°ÝÃD¦bþ¸Ì¤F¡C§Ú³£¬O¥Î¼Ò²Õ¤è¦¡¨Ó³]p¡A§Ú¬Oª½±µ¦bEXCEL¤W³]p¤å¦r¤è¶ô¡A©Ò¥H¤@ª½¦³¿ù»~¡A¦ý¬O§Ú´¡¤Jªí³æ¦b«ö·Ó¤j¤jªº¤è¦¡´N¦¨¥\¤F¡A¥u¬O¦pªG§Ú¦bEXCEL sheet ¤W´N¦³°ÝÃD~~§ä¤£¥Xì¦]= ="¡A¤£¹LÁÙ¬O·PÁ¤j¤j¡A²×©ó·d©w¤F¡A·P®¦¡I |
|
¿·
|
|
|
|
|
- ©«¤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
|
¦^´_ 5# c_c_lai
§Ú«ö·Ó¤j¤j¤è¦¡¦n¹³¤£¦æ(¼Ò²Õ¤è¦¡)¡A¤£¹LÁÙ¬OÁÂÁ´£¨Ñ¤èªk¡C |
|
¿·
|
|
|
|
|
- ©«¤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
|
¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-12-7 17:28 ½s¿è
¦^´_ 6# badboy01
OK , ©Ò¥H§A¬Oµ{¦¡¼g¦b¼Ò²Õ¤º ,
°²³]§A¬Oª½±µ¦b¤u§@ªí1 (¤u§@ªí¦WºÙ) , ´¡¤JActiveXªº¤å¦r¤è¶ô
³o¼Ëªº¸Ü§AªºTextbox1n«ü©w¬OþÓ¤u§@ªí ,
¤]´N¬O§â쥻ªº Textbox1.Text §ï¼g¦¨ sheets("¤u§@ªí1").Textbox1.Text
Textbox2¦P²z |
|
|
|
|
|
|
- ©«¤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
|
¦^´_ 8# stillfish00
·PÁÂ~~¤j¤j¦¨¥\¤F~¤§«e·d¦n¤[³£·d¤£©w¡A¼Ò²Õ¤]¥i¥H¥Î¤F~~·P®¦^____^ |
|
¿·
|
|
|
|
|
- ©«¤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
|
¦^´_ 8# stillfish00
¥t½Ð±Ð¤j¤j¡A§Ú¦bEXCEL sheet¤¤³]pªº«ö¶sn«ç»òÂê©wµLªk«ö¥kÁä¥h½s¿è¥¨¶°ªºì©l½X¡HÁÂÁ¡I(¥¿±`¤U¤@¯ë·Æ¹«¥kÁä¥i¥H¿ï¾Ü«ü©w¥¨¶°) |
|
¿·
|
|
|
|
|