Hsieh

¦^´_ 1# wl02353427

1. Public NameOfThisProcedure
   
2. Public NextTime
   
3. Sub Stop_Update()
   
4. Yn = MsgBox("½T©w°±¤î" & NameOfThisProcedure & "µ{§Ç" & Chr(10) & NextTime & "ªº§ó·s?", vbYesNo)
   
5. If Yn = 6 Then Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure, schedule:=False
   
6. End Sub
   
7. Sub Awesome()
   
8. '****************************ºI¨ú¸ê®Æ¨Ã°O¿ý****************************'
   
9.     Dim SG As SparklineGroup
   
10.     Dim SL As Sparkline
    
11.     Dim WSD As Worksheet
    
12.     Dim WSL As Worksheet
    
13.     Dim WSQ As Worksheet
    
14.     Set WSQ = Worksheets("¤u§@ªí1")
    
15.    
    
16.     hiji = Now()
    
17.    
    
18.     WaitSec = 30 '©µ¿ð®É¶¡
    
19.     NameOfThisProcedure = "Awesome"
    
20.    
    
21. 
    
22.     NextTime = Now + TimeSerial(0, 0, WaitSec) ' ©µ¿ð
    
23.    
    
24.     Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure ' §Q¥ÎOnTime«ü¥O±Æ©w Awesome ªº°õ¦æ®É¶¡
    
25.    
    
26.     WSQ.Range("B3").QueryTable.Refresh BackgroundQuery:=False '­«·s¾ã²zweb¬d¸ß
    
27.    
    
28.     Application.Wait (Now + TimeValue("0:00:05")) ' ½T»{¸ê®Æ§ó·s
    
29. 
    
30.     NextRow = WSQ.Cells(Rows.Count, 2).End(xlUp).Row + 1
    
31.     WSQ.Range("B3:K3").Copy WSQ.Cells(NextRow, 2)
    
32.    
    
33. '**********************************************************************'
    
34. 
    
35. On Error Resume Next
    
36.    Application.DisplayAlerts = False
    
37.    Worksheets("¤u§@ªí2").Delete
    
38. On Error GoTo 0
    
39. 
    
40. Set WSD = Worksheets("¤u§@ªí1") ' ¸ê®ÆŪ¨ú
    
41. Set WSL = ActiveWorkbook.Worksheets.Add ' »ö¿öªO¿é¥X
    
42.     WSL.Name = "¤u§@ªí2"
    
43. 
    
44. WSL.Select
    
45. 
    
46. '***»ö¿öªO¤j¤p***'
    
47. With WSL.Range("B2")
    
48.     .ColumnWidth = 100 ' °ª«×
    
49.     .RowHeight = 100  ' ¼e«×
    
50. End With
    
51. 
    
52. '****¼ÐÃD¦WºÙ****'
    
53. With WSL.Range("B1")
    
54.     .Value = Array("±i¼Æ")
    
55.     .HorizontalAlignment = xlCenter
    
56.     .ColumnWidth = 39
    
57.     .Offset(1, 0).RowHeight = 200
    
58. End With
    
59. 
    
60. Set SG = WSL.Range("B2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="¤u§@ªí1!G8:G" & NextRow)
    
61.     SG.SeriesColor.Color = RGB(0, 0, 255) ' ½u±øÃC¦â
    
62. 
    
63. Set SL = SG.Item(1)
    
64. 
    
65. '***­I´ºÃC¦â***'
    
66. With WSL.Range("B2").Interior
    
67.     .Color = RGB(255, 255, 255)
    
68.     .TintAndShade = 0
    
69. End With
    
70.    
    
71. Set SL = SG.Item(1)
    
72. 
    
73. '***³Ì¤j³Ì¤p­È***'
    
74. Set AF = Application.WorksheetFunction
    
75.     AllMin = AF.Min(WSD.Range("G8:G65536")) ' «ü©w°Ï°ì¤¤¿ï¥X³Ì¤p­È
    
76.     AllMax = AF.Max(WSD.Range("G8:G65536"))
    
77.     AllMin = Int(AllMin) - AllMin ' ¾ã¼Æ³]©w
    
78.     AllMax = Int(AllMax)
    
79. 
    
80. With SG.Axes.Vertical
    
81.     .MinScaleType = xlSparkScaleCustom
    
82.     .MaxScaleType = xlSparkScaleCustom
    
83.     .CustomMinScaleValue = AllMin
    
84.     .CustomMaxScaleValue = AllMax
    
85. End With
    
86. 
    
87. '***[A2]¤W¤U¶¡¶Z***'
    
88. With WSL.Range("A2")
    
89.      .Value = AllMax & vbLf & vbLf & vbLf & vbLf _
    
90.             & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & AllMin
    
91.      .HorizontalAlignment = xlRight
    
92.      .VerticalAlignment = xlTop
    
93.      .Font.Size = 8
    
94.      .Font.Bold = True
    
95.      .WrapText = True
    
96.     End With
    
97. 
    
98. '***¨ä¥L³]©w***'
    
99. Worksheets("¤u§@ªí2").Range("B3") = "§ó·s¤é´Á : " & hiji
    
100. 
     
101. Number = WSD.Range("G8:G" & NextRow).Count
     
102. Worksheets("¤u§@ªí1").Range("M2") = "¸ê®Æµ§¼Æ :"
     
103. Worksheets("¤u§@ªí1").Range("N2") = Number
     
104. 
     
105. Workbooks(1).Save
     
106. 
     
107. Set WSQ = Nothing
     
108. Set QSL = Nothing
     
109. 
     
110. End Sub

½Æ»s¥N½X