[µo°Ý] API³vµ§¹Bºâ­t¾á¤j¡A¥i§_ºë²

dreamsway

¦U¦ì¤j¤j¡A¤p§Ì³Ìªñªì¾ÇVBA¡A¸ÕµÛ¤gªk·Ò¿û§â·Q­nªº¤è¦¡§ï¥ÎVBA¾Þ§@¡A¥¨¶°Áö¥i¥H¨Ï¥Î¡A¦ýµ{¦¡ªº­t²ü«Ü­«¡A¬O§_¦³¿ìªkºë²©O
±Ä¥Îªº¬OAPI´Á³f³ø»ù¡A¶i¦æ³vµ§¹Bºâ(½d¨Òªºµ§¼Æ«Ü¤Ö¡A¹ê»Ú±¡ªp±NÀH®É¶¡Åܦ¨´X¸Uµ§¸ê®Æ)¡A¨Ã±NBÄ檺¦¨¥æ®É¶¡½Æ»s¨ìSht1.Range("C1")

¤U¸ü (110.29 KB)

2018-10-13 02:09

±µµÛ¨Ì·ÓSht1.Range("C1")ªº®É¶¡¡A±N¹Bºâªºµ²ªG(Sht2.Cells(4, "J")»PSht2.Cells(5, "J"))   ¶K¦bSht3ªºB.CÄæ
¨C

¤U¸ü (26.64 KB)

2018-10-13 02:14

1. Sub ¦hªÅ¬ö¿ý()
   
2. Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
   
3. 
   
4. If Sht1.Range("C1") >= TimeValue("08:45:00") And Sht1.Range("C1") < TimeValue("08:50:00") Then
   
5. Sht3.Cells(2, "B") = Sht2.Cells(4, "J")
   
6. Sht3.Cells(2, "C") = Sht2.Cells(5, "J")
   
7. End If
   
8. 
   
9. If Sht1.Range("C1") >= TimeValue("08:50:00") And Sht1.Range("C1") < TimeValue("08:55:00") Then
   
10. Sht3.Cells(3, "B") = Sht2.Cells(4, "J")
    
11. Sht3.Cells(3, "C") = Sht2.Cells(5, "J")
    
12. End If
    
13. If Sht1.Range("C1") >= TimeValue("08:55:00") And Sht1.Range("C1") < TimeValue("09:00:00") Then
    
14. Sht3.Cells(4, "B") = Sht2.Cells(4, "J")
    
15. Sht3.Cells(4, "C") = Sht2.Cells(5, "J")
    
16. End If
    
17. If Sht1.Range("C1") >= TimeValue("09:00:00") And Sht1.Range("C1") < TimeValue("09:05:00") Then
    
18. Sht3.Cells(5, "B") = Sht2.Cells(4, "J")
    
19. Sht3.Cells(5, "C") = Sht2.Cells(5, "J")

½Æ»s¥N½X

¦p¥H¤Wµ{¦¡¨C5¤ÀÄÁ¤@­Ó°Ï¶¡¡A¤@ª½°O¿ý¨ì13:45¬°¤î¡A
¥Ñ©ó²{¦b¬O¨C¤@µ§¦¨¥æ´N¶i¦æ¤@¦¸¹Bºâ¡A1¬í¤º¥i¯à¦¨¥æ«Ü¦hµ§¥æ©ö¡A
¦³·Q¹L¬O§_¥i¥H§ï¬°1¬í¶i¦æ¤@¦¸¬ö¿ý´N¦n¡A
¦A³Â·Ð¦U¦ì°ª¤âÀ°¦£¡AÁÂÁ¡I

GBKEE

¦^´_ 1# dreamsway
°Ñ¦Ò¬Ý¬Ý

1. Option Explicit
   
2. Public uMode&, StartTime, EndTime
   
3. Public MyBook As Workbook, Sht1 As Worksheet, Sht2 As Worksheet, Sht3 As Worksheet, xRow&
   
4. Sub ¦@¥Î°Ñ·Ó()
   
5.     Set MyBook = ThisWorkbook
   
6.     Set Sht1 = MyBook.Sheets("¦hªÅÂŹÏ")
   
7.     Set Sht2 = MyBook.Sheets("³ø»ù¼Æ¾Ú")
   
8.     Set Sht3 = MyBook.Sheets("¦hªÅ¼Æ¾Ú")
   
9.     StartTime = "08:44:50"  '¶}½L®É¶¡¡]´£¦­¤Q¬í¶}©l¡A¤~¥i°O¿ý¶}½L¶q»ù¡^
   
10. End Sub
    
11. Sub ³ø»ù¹Bºâ()
    
12.     Dim xTime As Date
    
13.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
14.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
    
15.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
    
16.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
    
17.     End If
    
18.     i = 1
    
19.     xTime = Time
    
20.     Do
    
21.                 If Time > xTime Then  'Time ¥H¬í­pºâªº ** Time > xTime = >¤U¤@¬í **
    
22.             i = i + 1: xTime = Time
    
23.             If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
    
24.                 Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
    
25.             End If
    
26.             If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
    
27.                 Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
    
28.             End If
    
29.             If Sht2.Cells(i, "H") <> 1 Then '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
    
30.                 Sht1.Cells(1, "C") = Sht2.Range("B" & i)
    
31.             End If
    
32.             Call ¦hªÅ¬ö¿ý
    
33.             Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
    
34.         End If
    
35.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
    
36. End Sub
    
37. Sub ¦hªÅ¬ö¿ý()
    
38.      Dim xMinute As Integer
    
39.         Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
40.         xMinute = Int(Application.Text(Time - #8:45:00 AM#, "[M]") / 5)
    
41.         '*** xMinute ¥H Time ¶Z 8:45 ªº¤ÀÄÁ¼Æ / 5 ¶Ç¦^ªº¾ã¼Æ
    
42.         '*** Time ¤p©ó 8:45 ±o¨ì­t¼Æ
    
43.         If xMinute > -1 And Time <= #1:45:00 PM# Then
    
44.             xMinute = xMinute + 2   '*** ±q²Ä2¦C¶}©l
    
45.             Sht3.Cells(xMinute, "B") = Sht2.Cells(4, "J")
    
46.             Sht3.Cells(xMinute, "C") = Sht2.Cells(5, "J")
    
47.         End If
    
48. End Sub
    
49. Sub ²M°£¼Ð°O()
    
50.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
51.     Sht2.Columns("H").Clear
    
52.     Sht2.Cells(1, "H").Value = "¼Ð°O"
    
53. End Sub
    
54. Sub ²M°£³ø»ù()
    
55.     Dim qyt As QueryTable '§R°£¥~³¡³s½u
    
56.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
57.     With Sht2
    
58.         .Columns("A:G").Clear '§R°£«á±N¼ÐÃD¦WºÙ¶ñ¤J
    
59.         .Columns("A:G").Rows(1) = Array("¥N¸¹", "®É¶¡", "¦¨¥æ»ù", "³æ¶q", "Á`¶q", "³Ì°ª»ù", "³Ì°ª»ù", "³Ì§C»ù")
    
60.         For Each qyt In .QueryTables
    
61.             qyt.Delete
    
62.         Next
    
63.     End With
    
64. End Sub

½Æ»s¥N½X

dreamsway

¦^´_ 2# GBKEE
ÁÂÁ¶Wª©G¤jªº¨ó§U
¸Õ¹B¦æ¤Fµ{¦¡¡Aµo²{·|³s³ø»ù¹Bºâªº³¡¤À³£Åܦ¨1¬í­pºâ¤@¦¸¡A
¦Ó¦hªÅ¬ö¿ý¦ü¥G¬O¥H¹q¸£®É¶¡¨Ó°µ¬ö¿ý¡A

¦]¬°¤p§Ìªì¾ÇVBA¡A¤£½T©w¬O§_¦³¥i¯à¹ê²{¡A
ÅýSub ³ø»ù¹Bºâ()ªº³¡¤À¥H¥¿±`³t«×¹B¦æ¡A¦ýSub ¦hªÅ¬ö¿ý()¥Hµ{¦¡ªº®É¶¡¶i¦æ1¬í1¦¸(©Î¬O¥[±ø¥ó·s®É¶¡>®ɶ¡)¦A§PÂ_®É¶¡±ø¥ó«á°O¿ý©O

GBKEE

¦^´_ 3# dreamsway
½Ð¦Û¦æ¸Õ§ï¬Ý¬Ý
Sub ¦hªÅ¬ö¿ý() ¤¤ Time §ï¦¨ Sht1.Range("C1")

1. Sub ³ø»ù¹Bºâ()
   
2.     Dim xTime As Date
   
3.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
   
4.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
   
5.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
   
6.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
   
7.     End If
   
8.     i = 1
   
9.     xTime = Time
   
10.     Do
    
11.         i = i + 1
    
12.         If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
    
13.             Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
    
14.         End If
    
15.         If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
    
16.             Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
    
17.         End If
    
18.         If Sht2.Cells(i, "H") <> 1 Then '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
    
19.             Sht1.Cells(1, "C") = Sht2.Range("B" & i)
    
20.         End If
    
21.         If Time > xTime Then  '** ¤@¬í¹B¦æ¤@¦¸    **
    
22.             xTime = Time
    
23.             Call ¦hªÅ¬ö¿ý
    
24.         End If
    
25.         Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
    
26.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
    
27. End Sub

½Æ»s¥N½X

dreamsway

¦^´_ 4# GBKEE
ÁÂÁÂG¤j ­ì¥»¹J¨ì¤@¨Ç°ÝÃD¡A
¬ã¨s¤F¤@¤U¡A¬ö¿ýªº³¡¤À³£¸Ñ¨M¤F¡I
ÁÂÁ¡I

dreamsway

¦^´_ 4# GBKEE
¤£¦n·N«ä¡A¦A¥t°µ¤@­Ó¬ö¿ý15¤ÀK¶}°ª§C¦¬ªº¥¨¶°¤W¹J¨ì¤F§xÃø(¥Ñ©ó³o¸ò­ì¼ÐÃD¤£§¹¥þ²Å¦X¡A­Y»Ý¥t¥DÃD¦A³Â·Ð§iª¾)
§Æ±æ¦b­ì¥»³vµ§­pºâªº¹Lµ{¤¤¡A±N¨C15¤ÀÄÁªº¶}°ª§C¦¬»ù®æ¬ö¿ý°_¨Ó¡A¹B¥Îo,h,l,c ¥|­ÓÅܼÆ
½Ð°Ý­n¦p¦ó¦b¨C15¤ÀÄÁ¬ö¿ý¨ºÃä¡A­n´«¦æ¬ö¿ý®É¤~±No,h,lÅܼÆÂk0¨Ã­«·s­pºâ©O

1. Option Explicit
   
2. Public uMode&, StartTime, EndTime
   
3. Public MyBook As Workbook, Sht1 As Worksheet, Sht2 As Worksheet, Sht3 As Worksheet, xRow&
   
4. Public o As Single, h As Single, l As Single c As Single
   
5. 
   
6. Sub ¦@¥Î°Ñ·Ó()
   
7.     Set MyBook = ThisWorkbook
   
8.     Set Sht1 = MyBook.Sheets("¦hªÅÂŹÏ")
   
9.     Set Sht2 = MyBook.Sheets("³ø»ù¼Æ¾Ú")
   
10.     Set Sht3 = MyBook.Sheets("¦hªÅ¼Æ¾Ú")
    
11.     StartTime = "08:44:50"  '¶}½L®É¶¡¡]´£¦­¤Q¬í¶}©l¡A¤~¥i°O¿ý¶}½L¶q»ù¡^
    
12. End Sub
    
13. Sub ³ø»ù¹Bºâ()
    
14.     Dim xTime As Date
    
15.     Dim i As Long
    
16.    
    
17.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
18.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
    
19.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
    
20.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
    
21.     End If
    
22.     i = 1
    
23.    
    
24.     Do
    
25.         i = i + 1
    
26.         
    
27.         If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
    
28.             Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
    
29.         End If
    
30.         If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
    
31.             Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
    
32.         End If
    
33.         If Sht2.Cells(i, "H") <> 1 Then
    
34.             Sht1.Cells(1, "C") = Sht2.Range("B" & i) '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
    
35.         End If
    
36. 
    
37. '***¥H¤U·s¼W15¤ÀK¬ö¿ý
    
38. c = Sht2.Range("C" & i)
    
39.    If c > h Then h = c '§ó·s³Ì°ª»ù
    
40.    If c < l Then l = c '§ó·s³Ì§C»ù
    
41.    If o = 0 Then o = Sht2.Range("C" & i) Else o = o
    
42.    If l = 0 Then l = Sht2.Range("C" & i) Else l = l
    
43.    If h = 0 Then h = Sht2.Range("C" & i) Else h = h
    
44. Sht2.Range("O2").Value = o '¶ñ¶}½L»ù
    
45. Sht2.Range("P2").Value = h '¶ñ³Ì°ª»ù
    
46. Sht2.Range("Q2").Value = l '¶ñ³Ì§C»ù
    
47. Sht2.Range("R2").Value = c '¶ñ¦¬½L»ù
    
48.         
    
49.         If Sht1.Range("C1") > xTime Then  '** ¤@¬í¹B¦æ¤@¦¸    **
    
50.             
    
51.             Call ¦hªÅ¬ö¿ý
    
52.             Call ¤ÀK¶}°ª§C¦¬
    
53.         End If
    
54.         
    
55. 
    
56.         Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
    
57.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
    
58. End Sub
    
59. 
    
60. Sub ¤ÀK¶}°ª§C¦¬()
    
61. Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
    
62. 
    
63.   Dim xMinute As Integer
    
64.    
    
65.         
    
66.         xMinute = Int(Application.Text(Sht1.Range("C1") - #8:45:00 AM#, "[M]") / 15)
    
67.         '*** xMinute ¥H Time ¶Z 8:45 ªº¤ÀÄÁ¼Æ / 15 ¶Ç¦^ªº¾ã¼Æ
    
68.         '*** Time ¤p©ó 8:45 ±o¨ì­t¼Æ
    
69.         If xMinute > -1 And Sht1.Range("C1") <= #1:45:00 PM# Then
    
70.             xMinute = xMinute + 3   '*** ±q²Ä2¦C¶}©l
    
71.             Sht2.Cells(xMinute, "O") = Sht2.Cells(2, "O")
    
72.             Sht2.Cells(xMinute, "P") = Sht2.Cells(2, "P")
    
73.             Sht2.Cells(xMinute, "Q") = Sht2.Cells(2, "Q")
    
74.             Sht2.Cells(xMinute, "R") = Sht2.Cells(2, "R")
    
75.         End If
    
76.       
    
77. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 6# dreamsway

©ú¤Ñ¶}½L ¥i´ú¬Ý¬Ý

    ¨Ì®É¶¡¬ö¿ý.zip (29.26 KB)

¤U¸ü¦¸¼Æ: 9

2018-10-15 15:33

dreamsway

¦^´_ 7# GBKEE
ÁÂÁ¶Wª©G¤j¡A©ú¤Ñ½L¤¤´ú¸Õ¬Ý¬Ý¡A
ÁöµM»¡³o¦¸ªºµ{¦¡¹ï§Ú¹ê¦b¤Ó²`¶ø¤F¡A
ÁÙ¦³¦nªøªº¤@±ø¸ô¡A§Ú·|§V¤O¾Ç²ßªº¡I

dreamsway

¦^´_ 7# GBKEE
¦­¦wG¤j¡A¦]¬°³o¦¸ªºµ{¦¡¡A¤p§ÌºCºC¬dMSDN¤@¨Çµ{¦¡ªº§t·N¤´µM¤£¬Æ¤F¸Ñ¡A§Ú¤j·§´y­z¤@¤U¨Ï¥Î¤Wªº°ÝÃD¡A¦A½Ð±z¦h¦h«ü¾É¡A
¤@¶}©l·ÓµÛ»¡©ú¦sÀÉ«á­«¶}(±z´£¨Ñªº­ìÀÉ)¡A¨S¦³¥ô¦ó¤ÏÀ³¡A¤]ı±o«Ü©_©Ç³o¼Ë¨S³ø»ùªº¼Æ¾Ú«ç»ò¹Bºâ©O¡A
±µµÛÆ[¹î¨ì¥H¤U¥N½X¡A

1. Set Rng = Sheets("¦hªÅÂŹÏ").Range("A2") '** «ü¼Æ ¥N¸¹

½Æ»s¥N½X

¨Ã±NSheets("¦hªÅÂŹÏ").Range("A2")¶ñ¤W¥N½X«á¦sÀÉ­«¶}¡A§Y·s³Ð¤F¤@­Ó¸Õºâªí¡A¸Õºâªí¦WºÙ¬°«ü¼Æªº¥N¸¹¡A
¨ä¸Õºâªí¤]¶]¥X

1. Names.Add "Ar", Array("1¤À", "5¤À", "10¤À", "15¤À", "20¤À", "30¤À", "60¤À")

½Æ»s¥N½X

µ¥¦U¶µÄæ¦ì¦WºÙ¡A¦ý¦]¬°¨S¦³³ø»ù¼Æ¾Ú¡A©Ò¥H¨Ã¨S¦³¦b¹Bºâ¡A
±µ¤U¨Ó§Ú§âµ{¦¡¶K¨ì§Ú­ì¥»ªºÀɮסA¨Ã±N

1. Set Rng = Sheets("¦hªÅÂŹÏ").Range("A2") '** «ü¼Æ ¥N¸¹

½Æ»s¥N½X

§ï¦¨§Ú­ì¥»ªº³ø»ù°Ï°ì

1. Set Rng = Sheets("³ø»ù¼Æ¾Ú").Range("A2") '** «ü¼Æ ¥N¸¹

½Æ»s¥N½X

­«±Ò«áEXCEL§e²{­t¸ü¨S¦^À³ªºª¬ºA¡A¹Á¸Õµ¥¤F5¤ÀÄÁ¥H¤W¡A¨ä·sªº¸Õºâªí(1-60¤ÀK¬ö¿ý)¤´µM¬°ªÅ¥Õ¡A±µµÛ´NEXCELµL¦^À³±Y¼ì¤F¡A
¤§«á¦bÀÉ®×­«±Ò«áÃö³¬¥¨¶°¹B¦æ¡A¶i¦æ¼Æ¦¸°»¿ù
³£Åã¥Ü¦b¥H¤Uµ{¦¡½X

1. .Offset(1).Resize(, 3) = Array("®É¶¡", "¦h¤è¥[Á`", "ªÅ¤è¥[Á`")

½Æ»s¥N½X

¤£ª¾¬O¤p§Ì­þÃä¾Þ§@¤W¦³»~¶Ü!?

GBKEE

¦^´_ 9# dreamsway

¥Ñ©ó²{¦b¬O¨C¤@µ§¦¨¥æ´N¶i¦æ¤@¦¸¹Bºâ¡A1¬í¤º¥i¯à¦¨¥æ«Ü¦hµ§¥æ©ö

³o¸ê®Æ¬O­þ¸Ì¨Óªº



¦hªÅÂÅ¹Ï ¤W¬O»Ý¶ñ¤WDee¤½¦¡ªº¦p§A¨S¦³
½Ðªþ¤W§Aªº­ì©lÀɮ׬ݬÝ