{"id":65263,"date":"2020-04-13T20:32:07","date_gmt":"2020-04-13T12:32:07","guid":{"rendered":"http:\/\/4563.org\/?p=65263"},"modified":"2020-04-13T20:32:07","modified_gmt":"2020-04-13T12:32:07","slug":"%e3%80%90%e7%8e%a9%e5%85%b7%e3%80%91%e7%94%a8excle%e5%b7%a5%e4%bd%9c%e8%a1%a8%e8%b0%83%e7%94%a8e5-10%e4%b8%aaapi","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=65263","title":{"rendered":"\u3010\u73a9\u5177\u3011\u7528Excle\u5de5\u4f5c\u8868\u8c03\u7528E5 10\u4e2aAPI"},"content":{"rendered":"\n

\t\t\t\t\txiaoque<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

\u3010\u73a9\u5177\u3011\u7528Excle\u5de5\u4f5c\u8868\u8c03\u7528E5 10\u4e2aAPI<\/h3>\n

\t\t \u672c\u5e16\u6700\u540e\u7531 xiaoque \u4e8e 2020-4-13 20:43 \u7f16\u8f91 <\/p>\n

\u76f8\u4fe1Office\u529e\u516c\u8f6f\u4ef6\u6bcf\u4e2a\u4eba\u90fd\u4f1a\u7528\u5230\uff0c\u5176\u4e2d\u53ef\u80fdExcel\u6709\u90e8\u5206\u670b\u53cb\u7528\u5f97\u6bd4\u8f83\u591a\uff0c\u90a3\u4e48\u5c0f\u5f1f\u7528Excel VBA\u5199\u4e86\u4e2a\u8c03\u7528E5 API\u7684\u4e1c\u4e1c\uff0c\u4f9b\u5404\u4f4d\u5927\u4f6c\u8bd5\u8bd5\uff0c\u5728\u5fae\u8f6f\u81ea\u5df1\u7684\u5f00\u53d1\u73af\u5883\u4e0b\u8c03\u7528API\uff0c\u662f\u5426\u80fd\u4e3aE5\u7eed\u8ba2\u52a9\u529b\u5462\uff1f<\/p>\n

\u6ce8\u610f\uff0c\u4e0d\u4fdd\u8bc1\u4e00\u5b9a\u80fd\u7eed\u8ba2
\u6ce8\u610f\uff0c\u4e0d\u4fdd\u8bc1\u4e00\u5b9a\u80fd\u7eed\u8ba2
\u6ce8\u610f\uff0c\u4e0d\u4fdd\u8bc1\u4e00\u5b9a\u80fd\u7eed\u8ba2<\/p>\n

\u597d\u4e86\uff0c\u5e9f\u8bdd\u4e0d\u591a\u8bf4\uff0c\u5148\u4e0a\u6548\u679c\u56fe\u3010\u7b2c1\u7248\u3011\uff1a<\/p>\n

https:\/\/sm.ms\/image\/jpe4sD9zQxiWMX2<\/p>\n

\u9996\u5148\uff0c\u611f\u8c22\u8fd9\u4e2a\u535a\u5ba2\u4e3b\u7684\u6559\u7a0b\uff1ahttps:\/\/blog.432100.xyz\/index.php\/archives\/50\/<\/p>\n

\u5927\u5bb6\u8bf7\u6309\u8fd9\u4e2a\u535a\u5ba2\u6559\u7a0b\u521b\u5efa\u81ea\u5df1\u7684\u5e94\u7528\u7a0b\u5e8f\uff0c\u7136\u540e\u5f00\u542f\u5982\u4e0b\u6743\u9650\uff1a<\/p>\n

Files.Read.All   Files.ReadWrite.All   Sites.Read.All   Sites.ReadWrite.All<\/p>\n

User.Read.All   User.ReadWrite.All   Directory.Read.Al   Directory.ReadWrite.All<\/p>\n

Mail.Read   Mail.ReadWrite   MailboxSettings.Read   MailboxSettings.ReadWrite<\/p>\n

\u7136\u540e\u6309\u535a\u5ba2\u6559\u7a0b\u91cc\u7684\u64cd\u4f5c\uff0c\u7528 rclone \u83b7\u53d6 access_token \uff0c\u6ce8\u610f\uff0c\u662f\uff1aaccess_token\uff0c\u4e0d\u662f\uff1arefresh_token\uff0c\u53ef\u4ee5\u770b\u4e0b\u56fe\uff1a<\/p>\n

https:\/\/sm.ms\/image\/NIFPiOZA9RDcgqL<\/p>\n

\u7136\u540e\u65b0\u5efa\u4e00\u4e2a Excel \u5de5\u4f5c\u8868\uff0c\u6253\u5f00\u5de5\u4f5c\u8868\uff0c\u4ec0\u4e48\u90fd\u4e0d\u7528\u586b\u5148\uff0c\u5148\u53e6\u5b58\u4e3a .xlsm \u683c\u5f0f\u7684\u6587\u4ef6\uff0c\u540d\u5b57\u968f\u4fbf\uff0c\u56e0\u4e3a\u53ea\u6709 .xlsm \u683c\u5f0f\u7684 Excel \u6587\u4ef6\u624d\u80fd\u4fdd\u5b58 Excel VBA \u4ee3\u7801\uff0c\u6253\u5f00\u4f60\u53e6\u5b58\u7684 .xlsm \u6587\u4ef6\uff0c\u91cc\u9762\u53ea\u4f1a\u6709\u4e00\u4e2a Sheet1 \u5de5\u4f5c\u8868\uff0c\u628a\u4e0a\u4e00\u6b65\u5f97\u5230\u5f97 access_token \u590d\u5236\u5230 A1 \u5355\u5143\u683c\u4e2d\uff0c\u6ce8\u610f\u4e00\u5b9a\u662f\u590d\u5236\u5230 A1 \u5355\u5143\u683c\u4e2d\uff0c\u7136\u540e\u6309 Alt+F11 \u7ec4\u5408\u952e\uff0c\u8fdb\u5165 Excel VBA \u7f16\u8f91\u5668\u7a97\u53e3\uff0c\u7136\u540e\u70b9\uff1a\u63d2\u5165–>\u6a21\u5757\uff0c\u5728\u5de6\u8fb9\u5de5\u7a0b\u8d44\u6e90\u7ba1\u7406\u5668\u7a97\u53e3\u91cc\u5c31\u4f1a\u65b0\u5efa\u4e00\u683c\u201d\u6a21\u57571\u201c\uff0c<\/p>\n

https:\/\/sm.ms\/image\/QyH4DBfGcr5z2JC<\/p>\n

\u7136\u540e\u5c06\u4e0b\u9762\u6240\u6709\u4ee3\u7801\u590d\u5236\u5230\u201d\u6a21\u57571\u201c\u4e2d\uff1a<\/p>\n

Private Flag As Long
Private Count As Long<\/p>\n

Public Sub MainProgram()
    Dim accessToken As String
    accessToken = Worksheets("sheet1").Range("A1").Value
    Dim url(1 To 10) As String
    url(1) = "https:\/\/graph.microsoft.com\/v1.0\/me\/drive\/root"
    url(2) = "https:\/\/graph.microsoft.com\/v1.0\/me\/drive"
    url(3) = "https:\/\/graph.microsoft.com\/v1.0\/users"
    url(4) = "https:\/\/graph.microsoft.com\/v1.0\/me\/messages"
    url(5) = "https:\/\/graph.microsoft.com\/v1.0\/me\/mailFolders\/inbox\/messageRules"
    url(6) = "https:\/\/graph.microsoft.com\/v1.0\/me\/drive\/root\/children"
    url(7) = "https:\/\/graph.microsoft.com\/v1.0\/me\/mailboxSettings"
    url(8) = "https:\/\/graph.microsoft.com\/v1.0\/me\/mailFolders"
    url(9) = "https:\/\/graph.microsoft.com\/v1.0\/me\/outlook\/masterCategories"
    url(10) = "https:\/\/graph.microsoft.com\/v1.0\/sites\/root\/drives"
   
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    Dim i As Long
    For i = 1 To 10 Step 1
    http.Open "GET", url(i), False
    http.setRequestHeader "Content-Type", "application\/json"
    http.setRequestHeader "Authorization", accessToken
    http.Send
   
    Do While http.ReadyState <> 4
      DoEvents
    Loop
      
    If http.Status = 200 Then
    Worksheets("sheet1").Range("A11").Select
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "\u6210\u529f\u8c03\u7528\u7b2c\u3010" & i & "\u3011\u4e2aAPI\uff0c\u7b2c\u3010" & Count & "\u3011\u6b21\uff0c" & "\u8c03\u7528\u65f6\u95f4\uff1a" & Now()
    Worksheets("sheet1").Range("A10").Select
    Else
    Debug.Print "\u8c03\u7528\u5931\u8d25\uff0c\u9519\u8bef\u4ee3\u7801\u4e3a\uff1a" & http.Status
    End If
    Next
    Count = Count + 1
    Call ExecProgram
End Sub<\/p>\n

Public Sub ExecProgram()
    If Flag = 1 Then
      Application.OnTime Now() + TimeValue("00:00:10"), "MainProgram"   ‘\u8fd9\u91cc\u4fee\u6539API\u8c03\u7528\u65f6\u95f4\u95f4\u9694\uff0c\u9ed8\u8ba4\u4e3a\u6bcf\u969410\u79d2\u8c03\u7528\u4e00\u6b21\uff0c\u683c\u5f0f\u4e3a\uff1a\u65f6:\u5206:\u79d2
    Else
      Exit Sub
    End If
End Sub<\/p>\n

Public Sub Run()
    Flag = 1
    Count = 1
    Call ExecProgram
    MsgBox "\u5f00\u59cb\u8c03\u7528API\uff01"
End Sub<\/p>\n

Public Sub Shutdown()
    Flag = 0
    MsgBox "\u5173\u95ed\u8c03\u7528\u7a0b\u5e8f\uff01"
End Sub<\/p>\n

\u7136\u540e\u5207\u6362\u56de\u5230 Sheet1 \u5de5\u4f5c\u8868\u754c\u9762\uff0c\u9009\u62e9\uff1a\u5f00\u53d1\u5de5\u5177–>\u63d2\u5165 \uff0c\u9009\u62e9\u201d\u8868\u5355\u63a7\u4ef6\u201c\u4e2d\u7b2c1\u683c\u201d\u6309\u94ae\u63a7\u4ef6\u201c\uff0c\u5982\u56fe<\/p>\n

https:\/\/sm.ms\/image\/hoUROiL5aqFQVnB[<\/p>\n

\u6309\u7167\u8fd9\u4e2a\u6b65\u9aa4\u5efa2\u683c\u201d\u6309\u94ae\u63a7\u4ef6\u201c\uff0c\u7136\u540e\u53f3\u952e\u70b9\u7b2c1\u4e2a\u201d\u6309\u94ae\u63a7\u4ef6\u201c\uff0c\u9009\u62e9\u201d\u6307\u5b9a\u5b8f\u201c\uff0c\u7136\u540e\u5728\u5f39\u51fa\u5f97\u5bf9\u8bdd\u6846\u4e2d\u9009\u62e9\u201d run "\u8fd9\u4e2a\u5b8f\uff0c\u7136\u540e\u70b9\u786e\u5b9a\uff0c\u7136\u540e\u518d\u6b21\u53f3\u952e\u70b9\u8fd9\u4e2a\u201c\u6309\u94ae\u63a7\u4ef6\u201d\uff0c\u9009\u62e9\u201c\u7f16\u8f91\u6587\u5b57\u201d\uff0c\u4e3a\u8fd9\u4e2a\u63a7\u4ef6\u547d\u540d\u4e3a\u201c\u542f\u52a8\u201d\uff0c\u91cd\u590d\u8fd9\u4e00\u6b65\u9aa4\uff0c\u8bbe\u7f6e\u7b2c2\u4e2a\u201d\u6309\u94ae\u63a7\u4ef6\u201c\uff0c\u9009\u62e9\u201d Shutdown "\u8fd9\u4e2a\u5b8f\uff0c\u7136\u540e\u70b9\u786e\u5b9a\uff0c\u6700\u540e\u4e5f\u662f\u53f3\u952e\u70b9\u51fb\uff0c\u53ef\u4ee5\u66f4\u6539\u63a7\u4ef6\u540d\u79f0\u4e3a\u201c\u5173\u95ed\u201d\uff0c\u7136\u540e\u4f60\u5355\u51fb\u201c\u542f\u52a8\u201d\u6309\u94ae\uff0c\u5c31\u5f00\u59cb\u8c03\u7528E5 API\u4e86\u3002\u6211\u9ed8\u8ba4\u8bbe\u7f6e\u4e3a10\u79d2\u95f4\u9694\uff0c\u4f60\u53ef\u4ee5\u81ea\u5df1\u4fee\u6539\u65f6\u95f4\u95f4\u9694\u3002<\/p>\n

\u53e6\u5916\u8981\u8bf4\u660e\u4e00\u4e0b\uff1a
1\u3001\u5173\u673a\u91cd\u542f\u540e\uff0c\u9700\u8981\u91cd\u65b0\u7528 rclone \u83b7\u53d6 access_token \u66f4\u6362 A1 \u5355\u5143\u683c\u4e4b\u524d\u5f97 access_token \u624d\u80fd\u518d\u6b21\u6b63\u5e38\u8fd0\u884c
2\u3001\u7a0b\u5e8f\u9694\u5f97\u592a\u4e45\u4e0d\u8fd0\u884c\u540e\uff0c\u9700\u8981\u91cd\u65b0\u7528 rclone \u83b7\u53d6 access_token \u66f4\u6362 A1 \u5355\u5143\u683c\u4e4b\u524d\u5f97 access_token \u624d\u80fd\u518d\u6b21\u6b63\u5e38\u8fd0\u884c<\/p>\n

\u5176\u5b9e\u5df2\u7ecf\u5199\u597d\u4e86\u7b2c2\u4e2a\u7248\u672c\uff0c\u7b2c2\u4e2a\u7248\u672c\u662f\u652f\u6301\u8bbe\u7f6e\u6307\u5b9a\u65f6\u95f4\u533a\u95f4\u5185\u968f\u673a\u8c03\u7528API\uff0c\u4f46\u662f\u529e\u6cd5\u6bd4\u8f83\u7b28\u62d9\uff0c\u6240\u6709\u5c31\u5148\u6ca1\u6709\u653e\u51fa\u6765\u7ed9\u5927\u5bb6\uff0c\u6709\u6ca1\u6709\u54ea\u4f4d\u5927\u4f6c\u80fd\u505a\u4e0b\u968f\u673a\u8c03\u7528\u7684\u4fee\u6539\uff0c\u9ebb\u70e6\u5f04\u5f04\uff0c\u9020\u798f\u5404\u4f4dmjj\uff0c\u8c22\u8c22 \t\t\t\t<\/p>\n

\t\t\t\t\tftp1<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

<\/h3>\n

\t\t\u725b\u6279\uff0c\u9876\u4f60\u4e2a\u80ba\t\t\t\t<\/p>\n

\t\t\t\t\t\u7ffb\u8f66\u7684\u8001\u53f8\u673a<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

<\/h3>\n

\t\t\u9a9a\u64cd\u4f5c\u554a\uff0c\u8868\u683c\u6211\u90fd\u6ca1\u5b66\u4f1a\u5462\t\t\t\t<\/p>\n

\t\t\t\t\txiaoque<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

<\/h3>\n

\t\t\u56fe\u7247\u600e\u4e48\u6ca1\u51fa\u6765\uff1f\t\t\t\t<\/p>\n

\t\t\t\t\tyxmss<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

<\/h3>\n

\t\t\u5389\u5bb3\uff0c\u9876\u4e00\u4e2a\t\t\t\t<\/p>\n

\t\t\t\t\t\u685c\u3067\u3059<\/strong> \t\t\t\t\u5927\u4f6c\u6709\u8bdd\u8bf4 : \t<\/p>\n

<\/h3>\n

\t\t\u5927\u4f6c\u5389\u5bb3\uff5e\uff5e\t\t\t <\/p>\n","protected":false},"excerpt":{"rendered":"

xiaoque \u5927\u4f6c\u6709\u8bdd\u8bf4 : \u3010…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/65263"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=65263"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/65263\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=65263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=65263"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=65263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}