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

[µo°Ý] EXCEL³sµ²DDE°O¿ý°ÝÃD

[µo°Ý] EXCEL³sµ²DDE°O¿ý°ÝÃD

°Ý¤@¤U¦U¦ì«e½ú­Ì,¦³§ä¨ì¨C¤ÀÄÁ¬ö¿ýªÑ²¼ªº¶}°ª¦¬§C»ù¦ìVBA,¦ý¦³¤@­Ó°ÝÃD·Q¤F¸Ñ,¦³¥i¯à¦b¤@­Ó¤u§@ªí¸Ì§â¦hÀɪѲ¼µ²DDEµM«á¨C­ÓªÑ²¼¶}¤@¤u§@ªí¦P®É­Ó§O°O¿ý¨C¤@¤ÀÄÁ¶}°ª§C¦¬»ù¶Ü?

°Ý¤@¤U¦U¦ì«e½ú­Ì,¦³§ä¨ì¨C¤ÀÄÁ¬ö¿ýªÑ²¼ªº¶}°ª¦¬§C»ù¦ìVBA,¦ý¦³¤@­Ó°ÝÃD·Q¤F¸Ñ,¦³¥i¯à¦b¤@­Ó¤u§@ªí¸Ì§â¦hÀÉ ...
daniel5168 µoªí©ó 2015-7-27 20:13

¥u­n§A¯à§â¬ÛÃöªÑ²¼ªº¶}°ª¦¬§C»ù¦ì¶×¤J¨ì¤u§@ªí¸Ì¡A¤§«á§A«K¯à¦pÄ@°õ¦æ¤F¡C

TOP

¥»©«³Ì«á¥Ñ daniel5168 ©ó 2015-7-28 08:29 ½s¿è

¦^´_ 2# c_c_lai


ublic uMode&, StartTime, EndTime
Public MyBook As Workbook, Sht1 As Worksheet, Sht2 As Worksheet, xRow&

Sub ¦@¥Î°Ñ·Ó()
Set MyBook = ThisWorkbook
Set Sht1 = MyBook.Sheets("Sheet1")
StartTime = "08:44:50"  '¶}½L®É¶¡¡]´£¦­¤Q¬í¶}©l¡A¤~¥i°O¿ý¶}½L¶q»ù¡^"08:44:50"
EndTime = "13:45:10"    '¦¬½L®É¶¡¡]©µ«á¤Q¬í°±¤î°O¿ý¡^¦¹¬°´ú¸Õ¥Î®É¶¡¡A½Ð¦Û¦æ§ó§ï¥¿¦¡®É¶¡ "13:45:10"
End Sub

Sub ¦Û°Ê°O¿ý()
If uMode = 0 Then Exit Sub
If Time > TimeValue(EndTime) Then '¦¬½L®É¶¡¥H«á¤£°õ¦æ
    Sht1.[O8] = "¤w¹L¦¬½L"
    uMode = 0
    Exit Sub
End If
Sht1.Range("B8") = Time '·í«e®É¶¡¡]®É¶¡½Xªí¡^
'----------------------------------------------------------
'¨C¢°¤ÀÄÁ°O¿ý¡A¢´¤ÀÄÁ«h§ï¬° Mod 5
If Second(Time) = 0 And Minute(Time) Mod 1 = 0 Then
   xRow = Sht1.Range("A65536").End(xlUp).Row + 1
   If xRow < 9 Then xRow = 9
   Sht1.Range("A" & xRow & ":N" & xRow).Value = Sht1.Range("A8:N8").Value
   '------------------------------------------------
   If ActiveSheet.Name = Sht1.Name And xRow > 20 Then
      ActiveWindow.ScrollRow = xRow - 18 'Åý³Ì·s¸ê®Æ«O«ù¦b¥i¨£µøµ¡¤¤
   End If
   ThisWorkbook.Save   '¦sÀÉ
   Beep
End If
Application.OnTime Now + TimeValue("00:00:01"), "¦Û°Ê°O¿ý"   '¨C¤@¬í»¼°j¤@¦¸
End Sub

Sub ¶}©l°õ¦æ()
If uMode = 1 Then Exit Sub
Call ¦@¥Î°Ñ·Ó
uMode = 1
Sht1.[O8] = "°õ¦æ¤¤"
Call ¦Û°Ê°O¿ý
End Sub

Sub °±¤î°õ¦æ()
uMode = 0
Call ¦@¥Î°Ñ·Ó
Sht1.[O8] = "STOP"
End Sub

Sub ²M°£°O¿ý¸ê®Æ()
Beep
If MsgBox("¡°½T©w­n²M°£¡eSheet1¡fªº°O¿ý¶Ü¡H¡@", 4 + 32 + 256) = vbNo Then Exit Sub
Call ¦@¥Î°Ñ·Ó
Sht1.Rows("9:65536").ClearContents
If ActiveSheet.Name = Sht1.Name Then ActiveWindow.ScrollRow = 1
Beep
End Sub

³o­Ó¬O§Ú§ä¨ìªºVBA ¨C¤@¤ÀÄÁ°O¿ý¤@¦¸
§Ú¸Ó«ç»ò§ï¦¨§Ú·Q­nªº§â°Ó«~¦P®É¤À¦¨¼Æ­¶¤u§@ªí¦P®É¤À§O¨C¤ÀÄÁ°O¿ý¤@¦¸

20120531a02(DDE¨C1¤À°O¿ý).rar (25.74 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-7-28 13:05 ½s¿è

¦^´_ 3# daniel5168
¸Õ¸Õ¬Ý
  1. Sub ¦Û°Ê°O¿ý()
  2.     Dim E As Variant, Rng As Range, t As Date, S As Integer, Shts()
  3.     Shts = Array("Sheet1", "°Ó«~1", "°Ó«~2", "°Ó«~3") '¤u§@ªí¦b¬¡­¶Ã¯ªº¦WºÙ
  4.     If uMode = 0 Then Exit Sub
  5.     For Each E In Shts
  6.         With Sheets(E)
  7.             ' .Activate ¦¨¬°§@¥Î¤¤ªº¤u§@ªí
  8.             If Time > TimeValue(EndTime) Then '¦¬½L®É¶¡¥H«á¤£°õ¦æ
  9.                 .[O8] = "¤w¹L¦¬½L"
  10.                 uMode = 0
  11.                 GoTo For_Next 'Exit Sub
  12.             End If
  13.             .Range("B8") = Time '·í«e®É¶¡¡]®É¶¡½Xªí¡^
  14.             '----------------------------------------------------------
  15.             Set Rng = .Range("A65536").End(xlUp).Offset(1)
  16.             .Range("A" & Rng.Row & ":N" & Rng.Row).Value = .Range("A8:N8").Value
  17.             '------------------------------------------------
  18.             ActiveWindow.ScrollRow = IIf(Rng.Row > 20, Rng.Row - 18, 1) 'Åý³Ì·s¸ê®Æ«O«ù¦b¥i¨£µøµ¡¤¤
  19.             Beep
  20.         End With
  21. For_Next:
  22.     Next
  23.     '------------------------------------------------
  24.     '¤U¤@­Ó¶¡¹jªº¤ÀÄÁ
  25.     S = 1  '¨C¢°¤ÀÄÁ°O¿ý¡A¢´¤ÀÄÁ«h§ï¬°5
  26.     t = Int(Application.Text(Time, "[m]") / S) + 1 '(Á`¤ÀÄÁ¼Æ/¶¡¹jªº¤ÀÄÁ¼Æ) + 1
  27.     t = DateAdd("N", S * t, 0)                     '±q0¤ÀÄÁ«áªº T * ¶¡¹jªº¤ÀÄÁ¼Æ
  28.     ThisWorkbook.Save   '¦sÀÉ
  29.     Application.OnTime t, "¦Û°Ê°O¿ý"
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE


    ¥¿¦b«ä¦Ò¦p¦ó®M¤J¤¤,¥Ñ©ó¨S¦³µ{¦¡³]­pªº°ò¦,¥¿¦b¤@¦æ¦æªº¾Ç²ß,¦pªG§Ú·Q¤£³z¤SGOOGLE¤£¥X¨Óµª®×¥i¯à¤S­n½Ð±Ð±z¤F,·PÁ±zªº¼ö¤ß¸Ñµª

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD