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

±N¸ê®Æ§ï¬°Ä²µo«¬¦¡

±N¸ê®Æ§ï¬°Ä²µo«¬¦¡

¥»©«³Ì«á¥Ñ wl02353427 ©ó 2013-12-1 17:38 ½s¿è

§Ú·Q±NÂ^¨ú¸ê®Æ§ï¬°Ä²µo¦¡©Î¦b³]©w´Á¶¡¤º°õ¦æÂ^¨ú¡A´N¬O¥H¤@­Ó±±¨î¾¹¨Ó±Ò°Ê©M°±¤î¡A¥i¬O§ï¨Ó§ï¥h¤@ª½¥¢±Ñ...¥Ø«e¥u·|¤@ª½§ì...µM«á±o¦Û¤v¶i¤Jvba«ö°±¤î...¥i§_¦³¤j¤j¥i¥HÀ°À°¦£

¤W¶Ç¸ê®Æ.rar (43.06 KB)

ªì·sªÌ~«ü±ÐÅo~

¦^´_ 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.     NextTime = Now + TimeSerial(0, 0, WaitSec) ' ©µ¿ð
  22.    
  23.     Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure ' §Q¥ÎOnTime«ü¥O±Æ©w Awesome ªº°õ¦æ®É¶¡
  24.    
  25.     WSQ.Range("B3").QueryTable.Refresh BackgroundQuery:=False '­«·s¾ã²zweb¬d¸ß
  26.    
  27.     Application.Wait (Now + TimeValue("0:00:05")) ' ½T»{¸ê®Æ§ó·s

  28.     NextRow = WSQ.Cells(Rows.Count, 2).End(xlUp).Row + 1
  29.     WSQ.Range("B3:K3").Copy WSQ.Cells(NextRow, 2)
  30.    
  31. '**********************************************************************'

  32. On Error Resume Next
  33.    Application.DisplayAlerts = False
  34.    Worksheets("¤u§@ªí2").Delete
  35. On Error GoTo 0

  36. Set WSD = Worksheets("¤u§@ªí1") ' ¸ê®ÆŪ¨ú
  37. Set WSL = ActiveWorkbook.Worksheets.Add ' »ö¿öªO¿é¥X
  38.     WSL.Name = "¤u§@ªí2"

  39. WSL.Select

  40. '***»ö¿öªO¤j¤p***'
  41. With WSL.Range("B2")
  42.     .ColumnWidth = 100 ' °ª«×
  43.     .RowHeight = 100  ' ¼e«×
  44. End With

  45. '****¼ÐÃD¦WºÙ****'
  46. With WSL.Range("B1")
  47.     .Value = Array("±i¼Æ")
  48.     .HorizontalAlignment = xlCenter
  49.     .ColumnWidth = 39
  50.     .Offset(1, 0).RowHeight = 200
  51. End With

  52. Set SG = WSL.Range("B2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="¤u§@ªí1!G8:G" & NextRow)
  53.     SG.SeriesColor.Color = RGB(0, 0, 255) ' ½u±øÃC¦â

  54. Set SL = SG.Item(1)

  55. '***­I´ºÃC¦â***'
  56. With WSL.Range("B2").Interior
  57.     .Color = RGB(255, 255, 255)
  58.     .TintAndShade = 0
  59. End With
  60.    
  61. Set SL = SG.Item(1)

  62. '***³Ì¤j³Ì¤p­È***'
  63. Set AF = Application.WorksheetFunction
  64.     AllMin = AF.Min(WSD.Range("G8:G65536")) ' «ü©w°Ï°ì¤¤¿ï¥X³Ì¤p­È
  65.     AllMax = AF.Max(WSD.Range("G8:G65536"))
  66.     AllMin = Int(AllMin) - AllMin ' ¾ã¼Æ³]©w
  67.     AllMax = Int(AllMax)

  68. With SG.Axes.Vertical
  69.     .MinScaleType = xlSparkScaleCustom
  70.     .MaxScaleType = xlSparkScaleCustom
  71.     .CustomMinScaleValue = AllMin
  72.     .CustomMaxScaleValue = AllMax
  73. End With

  74. '***[A2]¤W¤U¶¡¶Z***'
  75. With WSL.Range("A2")
  76.      .Value = AllMax & vbLf & vbLf & vbLf & vbLf _
  77.             & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & AllMin
  78.      .HorizontalAlignment = xlRight
  79.      .VerticalAlignment = xlTop
  80.      .Font.Size = 8
  81.      .Font.Bold = True
  82.      .WrapText = True
  83.     End With

  84. '***¨ä¥L³]©w***'
  85. Worksheets("¤u§@ªí2").Range("B3") = "§ó·s¤é´Á : " & hiji

  86. Number = WSD.Range("G8:G" & NextRow).Count
  87. Worksheets("¤u§@ªí1").Range("M2") = "¸ê®Æµ§¼Æ :"
  88. Worksheets("¤u§@ªí1").Range("N2") = Number

  89. Workbooks(1).Save

  90. Set WSQ = Nothing
  91. Set QSL = Nothing

  92. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

·PÁ¶W¯Åª©¥D:'( ¤@ª½¹Á¸Õ¼g¤@­Ó±±¨î¾¹¥i¬O³£¥¢±Ñ¡A¸U¤À·PÁ¡COrz
ªì·sªÌ~«ü±ÐÅo~

TOP

¦^´_ 2# Hsieh

§ÚªºEXCEL¬O2007ª©¡A°õ¦æ«á¥X²{¿ù»~¡A¤£ª¾¹DÁÙ­n³]©w­þ¨ÇªFªF

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-12-4 23:35 ½s¿è

¦^´_ 4# ML089
SparklineGroup ¬O Excel 2010 ·s¼Wªºª«¥ó¡A¦ô­p2007¬O¨S¿ìªkª½±µ¥Î¤F¡C

TOP

¦^´_ 5# stillfish00

¤F¸Ñ! ÁÂÁ§Aªº¦^ÂÐ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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