c_c_lai

¦^´_ 1# pipe
½Ð°Ñ¦Ò¡G
Ãö©ó½L¤¤DDE¬ö¿ý

c_c_lai

¦^´_  c_c_lai

CC¤j©êºp,§Ú§V¤Oª¦¤å¤]¬Ý§¹±zªº½d¨ÒÀÉ®×,¦ý¯uªºµLªk¹ïÀ³­×§ï¦¨§Ú¦Û¤vªº»Ý¨D, ¤@¬¤£³qªü ...
pipe µoªí©ó 2012-5-30 15:53

¹ê¦b¬O¯uªA¤F§A¡A·Q·QÁÙ¬OÀ°§A¼g¦n¤F¡C

1. Option Explicit
   
2. Dim timerEnabled As Boolean
   
3. Dim Pos As Integer      ' ±N¨C¹j "¦h¤Ö®É¶¡" ³B²z®É¬q¡A²¾¨ì¦¹³B¨Ó³B²z¡C
   
4. 
   
5. Private Sub Workbook_Open()
   
6.     Pos = Sheets("sheet1").Range("C" & Rows.Count).End(xlUp).Row
   
7.     timerEnabled = False
   
8. 
   
9.     Call timerStart      ' µ{¦¡¤@±Ò©l¡A«K¥h¦Û°Ê°õ¦æ timerStart
   
10. End Sub
    
11. 
    
12. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
13.     On Error Resume Next
    
14.     Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.Timer", , False
    
15.    
    
16.     Me.Save
    
17. End Sub
    
18. 
    
19. Public Sub Timer()
    
20.     On Error Resume Next
    
21.     If (TimeValue(Now) > Sheets("sheet1").Range("AV2").Value) Then Exit Sub
    
22.    
    
23.     If (TimeValue(Now) >= Sheets("sheet1").Range("AV1").Value) Then         ' ¶}½L¡B¦¬½L®É¬q³]©w
    
24.         ' ½L¤¤³B²z¡A±N¸ê®Æ¶×¤J¼g¤J¤u§@ªí³æ¤ºÀx¦s¡C
    
25. 
    
26.         With Sheets("sheet1")
    
27.             Pos = Pos + 1                  ' ±NÅܰʦ渹¥[¤@¦æ
    
28.             
    
29.             .Cells(Pos, 1).Value = Date     ' ¤é´Á
    
30.             .Cells(Pos, 2).Value = Time     ' ®É¶¡
    
31.             .Cells(Pos, 3).Resize(1, 12) = .Range("C8:N8").Value    ' ¤j§¡¡B¤j®t¡B´Á§¡¡B´Á®t¡B¥x«ü¡B°ªÂI¡B§CÂI¡B¤¤Ãö¡B»ù®t¡B®¶ºÖ¡B¼¯¥x¡B¥[Åv
    
32.             
    
33.             .Cells(2, 49).Value = Pos       ' AW2
    
34.         End With
    
35.     End If
    
36.         
    
37.     Call timerStart
    
38. End Sub
    
39. 
    
40. Sub timerStart()
    
41.     If timerEnabled Then
    
42.         Application.OnTime (Now + Sheets("sheet1").Range("AW1").Value), "ThisWorkbook.Timer"
    
43.     Else
    
44.         timerEnabled = True
    
45.         
    
46.         ' ¨t²Î­è³s¤W DDE ¦Ü¸ê®Æ¶×¤JExcel¤u§@ªí³æ¡A¶·¦³¤@­Ó½w½Ä®É¬q¡A
    
47.         ' ³o®É¦pªG°¨¤W¥h§ì¨úDDE¸ê®Æ¡A·|¦³«¬ºA¤£²Åªº¿ù»~°T®§²£¥Í¡A¨Ã¤¤Â_°õ¦æ§Çªº§@·~¡C
    
48.         Application.OnTime (Now + TimeValue("00:00:05")), "ThisWorkbook.Timer"
    
49.     End If
    
50. End Sub

½Æ»s¥N½X

°Å¤U«áª½±µ¶K¤J¨ì ThisWorkbook µ{¦¡°Ï´N OK ¤F¡C

c_c_lai

¦^´_ 6# pipe
§Aªº "AV1"¡B¥H¤Î "AV2" Äæ¦ì¤£´N¤w¸g¸ü©ú¤F¶Ü¡H
¥»¬qµ{¦¡¥¦¬O¥i¥H¥Ñ¥~³¡¨Ó±±¨îªº (¤è«K´ú¸Õ¥Î)¡A°²³]´ú¸Õ®É¶¡¬O¦b±ß¤W¤KÂI¨ì¤EÂI¤§¶¡¡A¬O¥H¨C¹j 20 ¬í¼g¤J¤@µ§¸ê®Æ¡A§A«K¥i¥H
³o»ò°µ¡A  ±N¥H¤UÄæ¦ì¤º®e²§°Ê¦¨ (¤d¸U¤£­n¥[¤W "" ²Å¸¹¡A§Yª½±µ¥´¤J 20:00:00¡B21:00:00¡B¥H¤Î 00:00:20 µ¥®É¶¡¼Æ¾Ú¡A ¥H¦¹Ãþ°ï)¡G
AV1 = "20:00:00"¡AAV2 = "21:00:00"¡A AW1 = "00:00:20"¡A µM«á¥h¦A¦æ´ú¸Õµª®×§Y¥X¡I
¼gµ{¦¡­n¤Á°O¤@ÂI¡A¤d¸U¤£­n¼g¦ºµ{¦¡¡A§_«h·|¦¨¬°¤@­ÓÙT¸}¥P¡I
My E-Mail:  [email protected]
§A¥i¥H°OÀɮר즹¡AÁÂÁ¡I

c_c_lai

¦^´_ 8# pipe
½L¤¤´ú¸Õ
S.zip (13.96 KB)

¤U¸ü¦¸¼Æ: 17

2016-10-24 20:17

c_c_lai

¦^´_ 8# pipe

¤U¸ü (49.89 KB)

2016-10-25 10:36

1. Option Explicit
   
2. Dim timerEnabled As Boolean
   
3. 
   
4. Private Sub Workbook_Open()
   
5.     timerEnabled = False
   
6. 
   
7.     Call timerStart      ' µ{¦¡¤@±Ò©l¡A«K¥h¦Û°Ê°õ¦æ timerStart
   
8. End Sub
   
9. 
   
10. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
11.     On Error Resume Next
    
12.     Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.Timer", , False
    
13.    
    
14.     Me.Save
    
15. End Sub
    
16. 
    
17. Public Sub Timer()
    
18.     Dim ws As Worksheet
    
19.     Dim Pos As Integer      ' ±N¨C¹j "¦h¤Ö®É¶¡" ³B²z®É¬q¡A²¾¨ì¦¹³B¨Ó³B²z¡C
    
20.    
    
21.     timerEnabled = True
    
22.         
    
23.     On Error Resume Next
    
24.     Set ws = Sheets("¤u§@ªí1")
    
25.     If (TimeValue(Now) > ws.Range("I2").Value) Then Exit Sub
    
26.    
    
27.     If (TimeValue(Now) >= ws.Range("I1").Value) Then         ' ¶}½L¡B¦¬½L®É¬q³]©w
    
28.         ' ½L¤¤³B²z¡A±N¸ê®Æ¶×¤J¼g¤J¤u§@ªí³æ¤ºÀx¦s¡C
    
29. 
    
30.         With Sheets("¤u§@ªí2")
    
31.             Pos = .Range("A" & Rows.Count).End(xlUp).Row + 1   ' ±NÅܰʦ渹¥[¤@¦æ
    
32.             
    
33.             .Cells(Pos, 1).Value = Date        ' ¤é´Á
    
34.             .Cells(Pos, 2).Value = ws.[B3]     ' ®É¶¡ (=DDEEXCEL|FUTURE!TXF16.®É¶¡)
    
35.             .Cells(Pos, 3).Resize(1, 8) = ws.Range("B4:B11").Value    ' ¥«»ù    º¦¶^    º¦¶^´T  ¶}½L»ù  ³Ì°ª»ù  ³Ì§C»ù  ²Ö­p¥æ©ö¶q  ¨ÄÂ÷²v
    
36.         End With
    
37.     End If
    
38.         
    
39.     Call timerStart
    
40. End Sub
    
41. 
    
42. Sub timerStart()
    
43.     If timerEnabled = False Then
    
44.         Application.OnTime (Now + Sheets("¤u§@ªí1").Range("I1").Value), "ThisWorkbook.Timer"
    
45.     Else
    
46.         ' ¨t²Î­è³s¤W DDE ¦Ü¸ê®Æ¶×¤JExcel¤u§@ªí³æ¡A¶·¦³¤@­Ó½w½Ä®É¬q¡A
    
47.         ' ³o®É¦pªG°¨¤W¥h§ì¨úDDE¸ê®Æ¡A·|¦³«¬ºA¤£²Åªº¿ù»~°T®§²£¥Í¡A¨Ã¤¤Â_°õ¦æ§Çªº§@·~¡C
    
48.         Application.OnTime (Now + TimeValue("00:00:05")), "ThisWorkbook.Timer"
    
49.     End If
    
50. End Sub

½Æ»s¥N½X