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

´Æ¤âªºexcel¹Bºâ°ÝÃD¡A¦p¦ó§ïµ½??

¦^´_ 40# c_c_lai


    ¥i¥H°O¿ý¡A¦ý¤£¥i²Î­p¡AC¤jÁÂÁ¡A§Ú·Q§ÚÁÙ¬O§ï¥ÎAPI+EXCELªº¤è¦¡¶i¦æ¦n¤F¡A¤£¥Î¦A¶O¤ß¤F¡A¯uªº«D±`ªºÁÂÁ©p

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-2-3 10:34 ½s¿è

¦^´_ 38# ÂŤÑÄR¦À

ªþÀɸոլݬݥt¤@§@ªk

EX.rar (28.72 KB)
   



ThisWorkbook¼Ò²Õ
  1. Option Explicit
  2. Private Sub Workbook_BeforeClose(Cancel As Boolean) '
  3.     'ÀÉ®×Ãö³¬:Ãö³¬Àɮ׳sµ²
  4.     '**Àɮצb¶}±Ò®É,¤£±Ò°Ê¸ß°Ý§ó·s¸ê®Æªºµøµ¡
  5.    
  6.     ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
  7.     'UpdateLinks ÄÝ©Ê ¶Ç¦^©Î³]©w XlUpdateLink ±`¼Æ¡A¦¹±`¼Æ¥i«ü¥X¬¡­¶Ã¯§ó·s¤º´O OLE ³s½uªº³]©w¡CŪ/¼g¡C
  8.    
  9.     'XlUpdateLinks ¥i¥H¬O³o¨Ç XlUpdateLinks ±`¼Æ¤§¤@¡C
  10.     'xlUpdateLinksAlways ¥Ã»·§ó·s«ü©w¬¡­¶Ã¯ªº¤º´O OLE ³s½u¡C
  11.     'xlUpdateLinksNever ¥Ã»·¤£§ó·s«ü©w¬¡­¶Ã¯ªº¤º´O OLE ³s½u¡C
  12.     'xlUpdateLinksUserSetting  ®Ú¾Ú¨Ï¥ÎªÌ¹ï«ü©w¬¡­¶Ã¯ªº³]©w¨Ó§ó·s¤º´Oªº OLE ³s½u¡C
  13. End Sub

  14. Private Sub Workbook_Open()
  15.     Application.Calculation = xlAutomatic  ' ¬¡­¶Ã¯³]¬°¦Û°Ê­«ºâ
  16.     'Àɮצb¶}±Ò®É:¦Û°Ê§ó·s³sµ²
  17.     With ActiveWorkbook
  18.         .UpdateRemoteReferences = True
  19.         .SaveLinkValues = True
  20.     End With
  21. End Sub
½Æ»s¥N½X
Sheet1(Sheets("RTD")) ¼Ò²Õªºµ{¦¡½X
  1. Option Explicit
  2. Dim D As Object, xTime As Date, Volume As Double
  3. Private Sub Worksheet_Calculate()
  4.     If IsError([E2]) Or Time < #8:45:00 AM# Then Application.StatusBar = "µ¥­Ô¶}½L¤¤": Exit Sub
  5.    
  6.     '[E2] = "--" ¶}½L«eªº²Å¸¹
  7.    If Volume <> [E2] And [E2] <> "--" And Time >= #8:45:00 AM# And Time < #1:46:00 PM# Then
  8.         If D Is Nothing Then
  9.             Application.OnTime #1:46:00 PM#, "SHEET1.¬ö¿ý"  '¦¬½L«á±j¨î¼g¥X³Ì«á¤@¤ÀÄÁªº¸ê®Æ
  10.             Application.StatusBar = False
  11.             Set D = CreateObject("scripting.dictionary")
  12.             Range("A" & Rows.Count).End(xlUp).CurrentRegion.Offset(1) = ""
  13.             Sheets("¬ö¿ý").UsedRange.Clear
  14.             xTime = TimeSerial(Hour(Time), Minute(Time), 0)
  15.         End If
  16.         If TimeSerial(Hour([B2]), Minute([B2]), 0) <> xTime And D.Count > 0 Then ¬ö¿ý '¤U¤@¤ÀÄÁ¶}©l®É,¬ö¿ý¤W¤@¤ÀÄÁªº¬ö¿ý
  17.         D([C2].Value) = D([C2].Value) + IIf([D2] <= 10, -1, 1)    '¦r¨åª«¥ó:¬ö¿ý¦¨¥æ³æ¶q¤½¦¡ªº­È
  18.         Volume = [E2]
  19.         xTime = TimeSerial(Hour([B2]), Minute([B2]), 0)
  20.         '**************** °O¿ý¨C¦¸¦¨¥æ¬ö¿ý***************
  21.          With Range("A" & Rows.Count).End(xlUp).Offset(1)
  22.             .Cells(1) = [B2]                        '®É¶¡
  23.             .Cells(1, 2) = [C2]                     '¦¨¥æ»ù
  24.             .Cells(1, 3) = [D2]                     '¦¨¥æ³æ¼Æ
  25.             .Cells(1, 4) = IIf([D2] <= 10, -1, 1)   '¦¨¥æ³æ¶q¤½¦¡ªº­È
  26.         End With
  27.         '************************************************
  28.     End If
  29. End Sub
  30. Private Sub ¬ö¿ý()
  31.     Dim R As Integer, C As Integer, X As Integer
  32.     Application.EnableEvents = False
  33.     With Sheets("¬ö¿ý")
  34.         If .[A1] = "" Then .[A1] = "®É¶¡"
  35.         With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
  36.             R = .Row
  37.             .NumberFormat = "HH:MM"
  38.             .Value = xTime
  39.             .Resize(2).Merge
  40.         End With
  41.         C = 2
  42.         '°j°é:¦r¨åª«¥óªºKEY(ÃöÁä¦r) ³Ì¤j­È - ³Ì¤p­È.
  43.         For X = Application.Max(D.KEYS) To Application.Min(D.KEYS) Step -1
  44.             If D.EXISTS(X) Then   '¦r¨åª«¥ó¦³³o­ÓKEY(ÃöÁä¦r)
  45.                 If .Cells(1, C) = "" Then .Cells(1, C) = C - 1
  46.                 .Cells(R, C) = X
  47.                 .Cells(R, C).Interior.ColorIndex = 40
  48.             
  49.                 .Cells(R + 1, C) = D(X)
  50.                 C = C + 1
  51.             End If
  52.         Next
  53.     End With
  54.     D.RemoveAll   '­«³],¦r¨åª«¥ó(¬ö¿ý¦¨¥æ»ùªº¤½¦¡ªº­È)
  55.    
  56.    '³o¦æªºµ{¦¡½X¥i§R°£¤W¤@¤ÀÄÁªº¸ê®Æ,¥[³tµ{¦¡ªº¹B¦æ
  57.     Range("A" & Rows.Count).End(xlUp).CurrentRegion.Offset(1) = ""    '¦p­n«O¯d¥iµù¸Ñ±¼¤£¥²°õ¦æ
  58.     Application.EnableEvents = True
  59. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 42# GBKEE


    G¤j¤µ¤Ñ¥xªÑ«ÊÃö¤F¡A­n¸Õ¤]­nµ¥¹L¦~«á¤F¡AÁÂÁ©p

TOP

¦^´_ 43# ÂŤÑÄR¦À


    «ÊÃö¤@¼Ë¥i¸Õ

TOP

¦^´_ 40# c_c_lai

http://forum.twbts.com/viewthread.php?tid=16452&extra=
C¤j·s¦~§Ö¼Ö¡A¦³ªÅ¥i¥H³Â·ÐÀ°§Ú¬Ý¬Ý¶Ü??

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD