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

¦bEXCEL VBA¤¤ªºMS SQL¬d¸ß»yªk¡A¦p¦ó¥Î¿é¤Jªº¤è¦¡²£¥Í¡H

¦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§Ú·Q­n³o¬qSQL»yªk¦bVBA¤¤¥i¥H¥ÎÅܼƤ覡¥h¬d¸ß¡C
¿­·

¦^´_ 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¥ð°²'......"

TOP

¦^´_ 2# stillfish00
«D±`·PÁ¤j¤j¡A§Ú«ö·Ó¤j¤jªº¤è¦¡µ{¦¡½X§ï¤F¡A¦ý¬O§Ú·Q­n¥ô·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
----------------------------------------------------------------------------------------------------------------------------------------------------
¿­·

TOP

¦^´_ 3# badboy01
sStartDate =TextBox1.Text
sEndDate =TextBox2.Text

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-12-7 16:42 ½s¿è

¦^´_ 3# badboy01
  1. where ApplyDateTime Between '" & Format(TextBox1.Text, "yyyymmdd") & "' AND '" & Format(TextBox2.Text, "yyyymmdd") & "' and Excuse='¯S¥ð°²'
½Æ»s¥N½X

TOP

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

TOP

¦^´_ 5# c_c_lai


    §Ú«ö·Ó¤j¤j¤è¦¡¦n¹³¤£¦æ(¼Ò²Õ¤è¦¡)¡A¤£¹LÁÙ¬OÁÂÁ´£¨Ñ¤èªk¡C
¿­·

TOP

¥»©«³Ì«á¥Ñ 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ªºTextbox1­n«ü©w¬O­þ­Ó¤u§@ªí ,
¤]´N¬O§â­ì¥»ªº Textbox1.Text §ï¼g¦¨  sheets("¤u§@ªí1").Textbox1.Text
Textbox2¦P²z

TOP

¦^´_ 8# stillfish00

·PÁÂ~~¤j¤j¦¨¥\¤F~¤§«e·d¦n¤[³£·d¤£©w¡A¼Ò²Õ¤]¥i¥H¥Î¤F~~·P®¦^____^
¿­·

TOP

¦^´_ 8# stillfish00

¥t½Ð±Ð¤j¤j¡A§Ú¦bEXCEL sheet¤¤³]­pªº«ö¶s­n«ç»òÂê©wµLªk«ö¥kÁä¥h½s¿è¥¨¶°ªº­ì©l½X¡HÁÂÁ¡I(¥¿±`¤U¤@¯ë·Æ¹«¥kÁä¥i¥H¿ï¾Ü«ü©w¥¨¶°)
¿­·

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD