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

[µo°Ý] Ãö©ó½L¤¤DDE¬ö¿ý

[µo°Ý] Ãö©ó½L¤¤DDE¬ö¿ý

¥»©«³Ì«á¥Ñ irene83 ©ó 2012-5-17 10:46 ½s¿è

ÁöµM¬Ý¨ì¯¸¤º«Ü¦h¬ÛÃö©«¤l¡A¦ý§Ú¤´µLªk¦Û¤v³]©w§¹¦¨
·Q½Ð±Ð¯¸¤ºªº°ª¤â­Ì¡A
¥Ø«e§Ú¨Ï¥Îexcel±µ¦¬¬Ý½L³nÅéddeªº¸ê°T¡A¨Ã°µ¦¨§Y®É°ÊºAªí®æ¦b("DATA"A2:V2)¡A
·Q³z¹LVBAÅý¥L¨C¶¡¹j5¤ÀÄÁ¬ö¿ý¤U¨Ó¨ì("Sheet1"A2:V66)¶K¤W­È¡A®É¶¡±q8:30~13:45¡A

ÁÂÁ¤j¤j­ÌªºÀ°¦£~¦³³Ò¤F!!!

ªþ¤WÀɮסA¤w°Ñ¦Ò¤j¤j«Øijªºµ{¦¡½X°µ­×§ï¡A¦ý¤´µLªk°õ¦æ
¦³½Ð±ÐC¤j¡AÁÂÁÂ!

20120517.rar (13.72 KB)

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-5-17 06:42 ½s¿è

¦^´_ 1# irene83
½Ð°Ñ¾\
EXCEL§ì¨ú¸ê®Æ°ÝÃD
§ó§ï¤@¤U¶¡®æ³]©w®É¶¡§Y¥i¡C ¨Ò¦p:  00:00:10 (¨C¹j¤Q¬í)¡B00:01:00 (¤@¤ÀÄÁ)¡B 00:05:00 (¤­¤ÀÄÁ)¡C

TOP

ªþ¤WÀɮסA¤w°Ñ¦Ò¤j¤j«Øijªºµ{¦¡½X°µ­×§ï¡A¦ý¤´µLªk°õ¦æ
¦³½Ð±ÐC¤j¡AÁÂÁÂ!

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-5-18 08:23 ½s¿è
ªþ¤WÀɮסA¤w°Ñ¦Ò¤j¤j«Øijªºµ{¦¡½X°µ­×§ï¡A¦ý¤´µLªk°õ¦æ
¦³½Ð±ÐC¤j¡AÁÂÁÂ!
irene83 µoªí©ó 2012-5-18 00:19

§Ú¬Ý¨ì§AªºÀɮפF¡A½ÐÀ˹î§A­ì¥»³]©wÄæ¦ì®y¼Ð¡G
"Y5"  --->  Range("X5").Value = "08:30:00"
"Y6"  --->  Range("X6").Value = "13:50:00"
"Z5"  --->  Range("Y5").Value = "00:05:00"
"Z6"  --->  Range("Y6").Value = 2  
Ãö©ó½L¤¤DDE¬ö¿ý 20120517.rar (13.89 KB)
§Aªºµ¥¯Å¥i¯àµLªk¤U¸ü¡A©Ò¥H§Ú±N¥¦¶K¥X¨Ó¡A§A¦A¸Õ¸Õ¬Ý¡A
¦p¦³°ÝÃD¦A¨Ó¨ç¡A¦ý¦^ÂЮɡA½Ð«ö¤UºÝªº"¦^ÂÐ"¶s¡A
§_«h§Ú¬O¤£ª¾¹D§A¤w¦^ÂФF¡C
  1. ' DDE ¸ê®Æ¬ö¿ý°ÝÃD
  2. Option Explicit
  3. Dim actEnabled As Boolean
  4. Dim cIndex As Single

  5. Private Sub Workbook_Open()
  6.     ' ¥H¤U¥|¦C¸ê®Æ¤§³]©w¡A¥i°t¦X¹ê§@¡B©Î´ú¸Õ¤§¥Øªº¡Aª½±µ¦b "sheet1" «ü©w¤§³]©wÄæ¡A±oÀH®É¤©¥H²§°Ê¡C
  7.    '  **************************************************************************************************
  8.     If (Sheets("DATA").Range("X5").Value = "") Then Sheets("DATA").Range("X5").Value = "08:30:00"   ' °²³]C6Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
  9.     If (Sheets("DATA").Range("X6").Value = "") Then Sheets("DATA").Range("X6").Value = "13:50:00"   ' D6Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
  10.     If (Sheets("DATA").Range("Y5").Value = "") Then Sheets("DATA").Range("Y5").Value = "00:05:00"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤@¤ÀÄÁ¼g¤J¤@¦¸¡C
  11.     If (Sheets("DATA").Range("Y6").Value = "") Then Sheets("DATA").Range("Y6").Value = 2            ' ¬ö¿ý¤w¶×¤J¸ê®Æ¦C¼Æ¡C
  12.    '  *************************************************************************************************

  13.     If (TimeValue(Now) > Sheets("DATA").Range("X6").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹L D6 ªºÀç·~®É¬q¡A«h©I¥s.......
  14.         Call stopProcedure
  15.     Else                                                                 ' ¤Ï¤§¦b D6 ³]©w®É¶¡¥H«e¡A«h©I¥s.......
  16.         Call startProcedure
  17.     End If
  18. End Sub

  19. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  20.     On Error Resume Next
  21.     Call actStop
  22. End Sub

  23. Sub startProcedure()       ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
  24.     Call actStart
  25. End Sub

  26. Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
  27.    Call actStop
  28. End Sub

  29. Sub newTitle()
  30.     Sheets(Sheet1).[A1].Resize(, 22) = Sheets(DATA).[A1:V1].Value  ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
  31. End Sub

  32. Sub Starter()
  33.     If (actEnabled = True And TimeValue(Now) >= Sheets("DATA").Range("X5").Value And TimeValue(Now) <= Sheets("DATA").Range("X6").Value) Then
  34.         cIndex = Sheets("DATA").Range("Y6").Value

  35.         If (cIndex = 0) Then Call newTitle  ' newTitle µ{§Ç (¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q) ¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ìsheet2¡F¦p¤é´Á¡B®É¶¡ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C

  36.         Sheets("DATA").Range("X6").Value = cIndex + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C

  37.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p A1¡BB1¡BC1¡BD1 ¹ïÀ³ªº¥i¯à¬O¤º½L¡B¥~½L¡B¦¨¥æ¡Bº¦¶^µ¥µ¥¡A¥H¦¹Ãþ±À¡C
  38.         Sheets(Sheet1).[A65536].End(xlUp).Offset(1).Resize(, 22) = Sheets(DATA).[A2:V22].Value

  39.         cIndex = Sheets("sheet1").Range("X6").Value      ' ¤Á°O Counter (­p¼Æ¾¹) ­n¥[¤@¡A§_«h¥Ã»·¬°¹s (·íµM¤w¤]¥i¥H¤£¤©¬ö¿ý¸ê®Æ¦C­z¡A¨Ì­Ó¤H²ß©Ê)¡C
  40.     End If
  41. End Sub

  42. Sub actStart()

  43. actEnabled = True

  44. Application.OnTime (Now + Sheets("DATA").Range("Y5").Value), "ThisWorkBook.onStarter"         ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤­¤ÀÄÁ¼g¤J¤@¦¸)

  45. End Sub


  46. Sub onStarter()
  47.     If Not IsError(Sheets(DATA).[A2]) Then Call Starter
  48.     If actEnabled Then Call actStart
  49. End Sub


  50. Sub actStop()
  51.     actEnabled = False

  52.     On Error Resume Next
  53.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
  54. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# c_c_lai
¦]¬°vba©ñ¦b¼Ò²Õ¬G
Application.OnTime (Now + Sheets("DATA").Range("Y5").Value), "onStarter"  
¥B           ' Sheets(Sheet1).[A65536].End(xlUp).Offset(1).Resize(, 22) = Sheets("DATA").[A2:V22].Value  ´«¦¨
Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 22) = Sheets("DATA").[A2:v2].Value
2007 ¥i°õ¦æ

' DDE ¸ê®Æ¬ö¿ý°ÝÃD
Option Explicit
Dim actEnabled As Boolean
Dim cIndex As Single

Private Sub Workbook_Open()
    ' ¥H¤U¥|¦C¸ê®Æ¤§³]©w¡A¥i°t¦X¹ê§@¡B©Î´ú¸Õ¤§¥Øªº¡Aª½±µ¦b "sheet1" «ü©w¤§³]©wÄæ¡A±oÀH®É¤©¥H²§°Ê¡C
    If (Sheets("DATA").Range("X5").Value = "") Then Sheets("DATA").Range("X5").Value = "08:30:00"   ' °²³]C6Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
    If (Sheets("DATA").Range("X6").Value = "") Then Sheets("DATA").Range("X6").Value = "13:50:00"   ' D6Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
    If (Sheets("DATA").Range("Y5").Value = "") Then Sheets("DATA").Range("Y5").Value = "00:05:00"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤@¤ÀÄÁ¼g¤J¤@¦¸¡C
    If (Sheets("DATA").Range("Y6").Value = "") Then Sheets("DATA").Range("Y6").Value = 2            ' ¬ö¿ý¤w¶×¤J¸ê®Æ¦C¼Æ¡C

    If (TimeValue(Now) > Sheets("DATA").Range("X6").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹L D6 ªºÀç·~®É¬q¡A«h©I¥s.......
        Call stopProcedure
    Else                                                                 ' ¤Ï¤§¦b D6 ³]©w®É¶¡¥H«e¡A«h©I¥s.......
        Call startProcedure
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Call actStop
End Sub

Sub startProcedure()       ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    Call actStart
End Sub

Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
   Call actStop
End Sub

Sub newTitle()
    Sheets(Sheet1).[A1].Resize(, 22) = Sheets(DATA).[A1:V1].Value  ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
End Sub

Sub Starter()
    If (actEnabled = True And TimeValue(Now) >= Sheets("DATA").Range("X5").Value And TimeValue(Now) <= Sheets("DATA").Range("X6").Value) Then
        cIndex = Sheets("DATA").Range("Y6").Value

        If (cIndex = 0) Then Call newTitle  ' newTitle µ{§Ç (¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q) ¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ìsheet2¡F¦p¤é´Á¡B®É¶¡ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C

        Sheets("DATA").Range("X6").Value = cIndex + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C

        ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p A1¡BB1¡BC1¡BD1 ¹ïÀ³ªº¥i¯à¬O¤º½L¡B¥~½L¡B¦¨¥æ¡Bº¦¶^µ¥µ¥¡A¥H¦¹Ãþ±À¡C
       ' Sheets(Sheet1).[A65536].End(xlUp).Offset(1).Resize(, 22) = Sheets("DATA").[A2:V22].Value
Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 22) = Sheets("DATA").[A2:v2].Value
        cIndex = Sheets("sheet1").Range("X6").Value      ' ¤Á°O Counter (­p¼Æ¾¹) ­n¥[¤@¡A§_«h¥Ã»·¬°¹s (·íµM¤w¤]¥i¥H¤£¤©¬ö¿ý¸ê®Æ¦C­z¡A¨Ì­Ó¤H²ß©Ê)¡C
    End If
End Sub

Sub actStart()

actEnabled = True

Application.OnTime (Now + Sheets("DATA").Range("Y5").Value), "onStarter"         ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤­¤ÀÄÁ¼g¤J¤@¦¸)

End Sub


Sub onStarter()
    If Not IsError(Sheets("DATA").[A2]) Then Call Starter
    If actEnabled Then Call actStart
End Sub


Sub actStop()
    actEnabled = False

    On Error Resume Next
    Application.OnTime Now, "ThisWorkBook.onStarter", , False
End Sub

Ãö©ó½L¤¤DDE¬ö¿ý 20120517---1.rar (17.03 KB)

TOP

¦^´_ 4# c_c_lai
  
ÁÂÁÂC¤jªº¼ö¤ß«ü¾É¡A¥Ø«e¥i¥H¥¿±`°õ¦æ¤F
¯uªº«D±`ÁÂÁ§A~~

¦P®É¤]ÁÂÁÂf¤jªº«ü±Ð¡A¦³¯¸¤º³o»ò¦h°ª¤âªº¨ó§U¡A
§Ú¯u¬O¤Ó©¯¹B¤F^_____^

TOP

½Ð±Ð¦¹½L¤¤DDE¬ö¿ý¬O¾A¥Î¤¸¤jªº¤U³æ³nÅé¶Ü?
devidlin

TOP

¦^´_ 7# devidlin
¦¹³B±´°Qªº¬O¦p¦ó±N½L¤¤DDE¸ê®Æ¶×¤J¨ì§A±ýÀx¦sªº Excel ªí³æ¤º¡A
¨Ñ°µ½L¤¤°Ñ¦Ò©ÎÆ[¹î¬ö¿ýªº²§°Ê¡C¥u­n¬O¨é°Ó¦³¤ä´© DDE ¥\¯à¡A
§A³£¥i¥H¦Û¦æ³sµ²¨ì§AªºExcel¡C(¨é°Ó³nÅé <---> DDE <---> Excel)

TOP

Ãö©ó½L¤¤DDE¬ö¿ý 20120517---1.rar     Àɮקì¤U¨Ó´ú¸Õ¤£¯à¨Ï¥Î¡A¥i¥H¥t´£¨ÑÀɮ׶Ü?ÁÂÁ¡C
devidlin

TOP

¦^´_ 9# devidlin
·Ð½Ð»¡©ú¨º¸Ì¥X°ÝÃD
1:¨S¨Ó·½dde·|¥X²{" #REF!  "
2:sheets("data")¤§ x5, x6 ®É¶¡¶·½Õ¾ã¨ì±z»Ý­nªº°Ï¶¡
°Ñ¦Ò¥ý

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD