{"id":6874,"date":"2016-03-31T16:52:53","date_gmt":"2016-03-31T07:52:53","guid":{"rendered":"http:\/\/www.ka-net.org\/blog\/?p=6874"},"modified":"2016-10-25T22:07:59","modified_gmt":"2016-10-25T13:07:59","slug":"excel-rest-api%e3%82%92vba%e3%81%8b%e3%82%89%e5%91%bc%e3%81%b3%e5%87%ba%e3%81%99%e6%96%b9%e6%b3%95","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=6874","title":{"rendered":"Excel REST API\u3092VBA\u304b\u3089\u547c\u3073\u51fa\u3059\u65b9\u6cd5"},"content":{"rendered":"

\u300cMicrosoft Graph\u3092VBA\u304b\u3089\u547c\u3073\u51fa\u3057\u3066OneNote\u306e\u30da\u30fc\u30b8\u5185\u5bb9\u3092\u53d6\u5f97\u3059\u308b<\/a>\u300d\u8a18\u4e8b\u3067\u89e6\u308c\u3066\u3044\u305f\u201cExcel REST API<\/a>\u201d\u3067\u3059\u304c\u3001\u77e5\u3089\u306c\u9593\u306b\u4f7f\u3048\u308b\u3088\u3046\u306b\u306a\u3063\u3066\u3044\u305f\u307f\u305f\u3044\u3067\u3059\u3002<\/p>\n

\u305d\u308c\u306a\u3089\u3070\u3084\u308b\u3053\u3068\u306f\u4e00\u3064\u3002
\nVBA\u304b\u3089<\/span>\u547c\u3073\u51fa\u3057\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u4e0b\u6e96\u5099<\/h4>\n

API\u3092\u5229\u7528\u3059\u308b\u305f\u3081\u306b\u3001Azure AD\u306e\u8a2d\u5b9a\u3084\u3089\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID\u306e\u53d6\u5f97\u3084\u3089\u304c\u5fc5\u8981\u306b\u306a\u308b\u306e\u3067\u3059\u304c\u3001\u4f5c\u696d\u624b\u9806\u306f\u300cOffice 365 Unified API\u3092VBA\u304b\u3089\u547c\u3073\u51fa\u3059<\/a>\u300d\u3067\u66f8\u3044\u3066\u3044\u308b\u3053\u3068\u3068\u307b\u307c\u540c\u3058\u306a\u306e\u3067\u3001\u3053\u3053\u3067\u306f\u5272\u611b\u3057\u307e\u3059\u3002<\/p>\n

    \n
  1. \u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306e\u7a2e\u985e\uff1a\u30cd\u30a4\u30c6\u30a3\u30d6 \u30af\u30e9\u30a4\u30a2\u30f3\u30c8 \u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3<\/li>\n
  2. \u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\uff1ahttp:\/\/localhost\/WBSample<\/li>\n
  3. \u4ed6\u306e\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306b\u5bfe\u3059\u308b\u30a2\u30af\u30bb\u30b9\u8a31\u53ef\uff1aMicrosoft Graph<\/li>\n
  4. \u30c7\u30ea\u30b2\u30fc\u30c8\u3055\u308c\u305f\u30a2\u30af\u30bb\u30b9\u8a31\u53ef\uff1aHave full access to user files<\/li>\n<\/ol>\n

    \u53d6\u5f97\u3059\u308bWorkbook<\/h4>\n

    \u4eca\u56de\u306f\u4e0b\u56f3\u306e\u3088\u3046\u306b\u3001OneDrive\u306b\u7f6e\u3044\u3066\u3042\u308b\u300cSampleBook.xlsx\u300d\u306e\u300cSampleSheet\u300d\u30b7\u30fc\u30c8\u3001\u30bb\u30ebB3\u306e\u5024\u3092\u53d6\u5f97\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

    \"MicrosoftGraph_ExcelRestAPI_VBA_01\"<\/p>\n

    \"MicrosoftGraph_ExcelRestAPI_VBA_02\"<\/p>\n

    VBA\u30b3\u30fc\u30c9<\/h4>\n

    \u3055\u3063\u305d\u304f\u66f8\u3044\u305f\u30b3\u30fc\u30c9\u304c\u4e0b\u8a18\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n

    authorization code\u53d6\u5f97
    \n\u2193
    \naccess token\u53d6\u5f97
    \n\u2193
    \nOneDrive\u306b\u4fdd\u5b58\u3055\u308c\u305f\u30d5\u30a1\u30a4\u30eb\u306e\u4e2d\u304b\u3089SampleBook.xlsx\u306eID\u3092\u53d6\u5f97
    \n\u2193
    \nExcel REST API\u3092\u547c\u3073\u51fa\u3057\u3066\u30bb\u30eb\u306e\u5024\u3092\u53d6\u5f97<\/p>\n

    \u51e6\u7406\u3068\u3057\u3066\u306f\u4e0a\u8a18\u306e\u3088\u3046\u306a\u6d41\u308c\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n

    Option Explicit\r\n\r\nPublic Sub SampleExcelRestAPI()\r\n'\u30b5\u30f3\u30d7\u30eb - Microsoft Graph(Excel REST API)\u547c\u3073\u51fa\u3057\r\n'\u203b ScriptControl\u3092\u4f7f\u3063\u3066\u3044\u308b\u305f\u3081\u300132\u30d3\u30c3\u30c8\u74b0\u5883\u306e\u307f\u5bfe\u5fdc\r\n'\u203b \u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\u3092localhost\u306b\u3057\u3066\u3044\u308b\u5834\u5408\u306f\u3001\u30ed\u30fc\u30ab\u30eb\u30b5\u30fc\u30d0\u30fc(XAMPP\u4ed6)\u306e\u8d77\u52d5\u304c\u5fc5\u8981\u306a\u5834\u5408\u304c\u3042\u308a\u307e\u3059\u3002\r\n  Dim url_auth As String\r\n  Dim url_token As String\r\n  Dim url_api As String\r\n  Dim q As String\r\n  Dim code As String\r\n  Dim js As String\r\n  Dim access_token As String\r\n  Dim workbook_id As String\r\n  Dim dat As Variant\r\n  Dim ary As Variant, ary2 As Variant\r\n  Dim req As Object\r\n  Dim sc As Object\r\n  Dim items As Object\r\n  Dim item As Object\r\n  Dim i As Long\r\n  Dim value, values, id, name 'JSON\u30d1\u30fc\u30b9\u7528\u30c0\u30df\u30fc\r\n  Const READYSTATE_COMPLETE = 4\r\n  \r\n  '***********************************\r\n  '\u203b \u8981\u5909\u66f4\r\n  '***********************************\r\n  Const client_id As String = "(\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID)" '\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID\r\n  Const redirect_uri As String = "http:\/\/localhost\/WBSample" '\u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\r\n  Const workbook_name As String = "SampleBook.xlsx" '\u53d6\u5f97\u3059\u308bWorkbook\u540d\r\n  Const worksheet_name As String = "SampleSheet" '\u53d6\u5f97\u3059\u308bWorksheet\u540d\r\n  '***********************************\r\n  \r\n  'authorization code\u53d6\u5f97\r\n  code = "" '\u521d\u671f\u5316\r\n  url_auth = "https:\/\/login.microsoftonline.com\/common\/oauth2\/authorize?response_type=code" & _\r\n             "&redirect_uri=" & EncodeURL(redirect_uri) & _\r\n             "&client_id=" & client_id & _\r\n             "&resource=" & EncodeURL("https:\/\/graph.microsoft.com\/")\r\n  With CreateObject("InternetExplorer.Application")\r\n    .Visible = True\r\n    .AddressBar = False\r\n    .MenuBar = False\r\n    .StatusBar = False\r\n    .Toolbar = False\r\n    .Width = 600\r\n    .Height = 480\r\n    .Navigate url_auth\r\n    While .ReadyState <> READYSTATE_COMPLETE Or _\r\n          .Busy = True Or _\r\n          (StrComp(Left(.LocationURL, Len(redirect_uri)), redirect_uri) <> 0)\r\n      DoEvents\r\n    Wend\r\n    q = .document.parentWindow.Location.Search\r\n    q = Mid(q, 2) '"?"\u524a\u9664\r\n    ary = Split(q, "&")\r\n    For i = LBound(ary) To UBound(ary)\r\n      ary2 = Split(ary(i), "=")\r\n      If LCase(ary2(0)) = "code" Then\r\n        code = ary2(1)\r\n        Exit For\r\n      End If\r\n    Next\r\n  End With\r\n  If Len(Trim(code)) < 1 Then Exit Sub\r\n  \r\n  'access token\u53d6\u5f97\r\n  js = "": access_token = "" '\u521d\u671f\u5316\r\n  url_token = "https:\/\/login.microsoftonline.com\/common\/oauth2\/token"\r\n  dat = "grant_type=authorization_code" & _\r\n        "&code=" & code & _\r\n        "&client_id=" & client_id & _\r\n        "&redirect_uri=" & EncodeURL(redirect_uri)\r\n  Set req = CreateObject("WinHttp.WinHttpRequest.5.1")\r\n  With req\r\n    .Open "POST", url_token, False\r\n    .setRequestHeader "Content-Type", "application\/x-www-form-urlencoded"\r\n    .send dat\r\n    Select Case .Status\r\n      Case 200: js = .responseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  js = "(" & js & ")"\r\n  Set sc = CreateObject("ScriptControl")\r\n  With sc\r\n    .Language = "JScript"\r\n    access_token = .CodeObject.eval(js).access_token\r\n  End With\r\n  If Len(Trim(access_token)) < 1 Then Exit Sub\r\n  \r\n  'OneDrive\u306e\u30d5\u30a1\u30a4\u30eb\u5217\u6319\r\n  js = "": workbook_id = "" '\u521d\u671f\u5316\r\n  url_api = "https:\/\/graph.microsoft.com\/beta\/me\/drive\/root\/children"\r\n  With req\r\n    .Open "GET", url_api, False\r\n    .setRequestHeader "Authorization", "Bearer " & access_token\r\n    .send\r\n    Select Case .Status\r\n      Case 200: js = .responseText\r\n      Case Else: Debug.Print .responseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  js = "(" & js & ")"\r\n  'Workbook ID\u53d6\u5f97\r\n  With sc\r\n    .Language = "JScript"\r\n    Set items = .CodeObject.eval(js).value\r\n    For Each item In items\r\n      If item.name = workbook_name Then\r\n        workbook_id = item.id\r\n        Exit For\r\n      End If\r\n    Next\r\n  End With\r\n  If Len(Trim(workbook_id)) < 1 Then Exit Sub\r\n  \r\n  '\u6307\u5b9a\u3057\u305fWorksheet\u306e\u30bb\u30ebB3\u306e\u5024\u3092\u53d6\u5f97\r\n  js = "" '\u521d\u671f\u5316\r\n  url_api = "https:\/\/graph.microsoft.com\/beta\/me\/drive\/items\/" & workbook_id & _\r\n            "\/Workbook\/Worksheets\/" & worksheet_name & "\/Range(address='B3')"\r\n  With req\r\n    .Open "GET", url_api, False\r\n    .setRequestHeader "Authorization", "Bearer " & access_token\r\n    .send\r\n    Select Case .Status\r\n      Case 200: js = .responseText\r\n      Case Else: Debug.Print .responseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  Debug.Print js '\u30ec\u30b9\u30dd\u30f3\u30b9(JSON)\u78ba\u8a8d\u7528\r\n  js = "(" & js & ")"\r\n  With sc\r\n    .Language = "JScript"\r\n    MsgBox "Workbook:" & workbook_name & vbNewLine & _\r\n           "Worksheet:" & worksheet_name & vbNewLine & _\r\n           "\u30bb\u30ebB3:" & .CodeObject.eval(js).values, vbInformation + vbSystemModal\r\n  End With\r\n  \r\n  MsgBox "\u51e6\u7406\u304c\u7d42\u4e86\u3057\u307e\u3057\u305f\u3002", vbInformation + vbSystemModal\r\nEnd Sub\r\n\r\nPrivate Function EncodeURL(ByVal Target As String) As String\r\n'URL\u30a8\u30f3\u30b3\u30fc\u30c9\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    EncodeURL = .CodeObject.encodeURIComponent(Target)\r\n  End With\r\nEnd Function<\/pre>\n
    \u554f\u984c\u306a\u304fAPI\u306e\u547c\u3073\u51fa\u3057\u304c\u884c\u308f\u308c\u308b\u3068\u3001\u4e0b\u56f3\u306e\u3088\u3046\u306b\u30bb\u30eb\u306e\u5024\u304c\u30e1\u30c3\u30bb\u30fc\u30b8\u30dc\u30c3\u30af\u30b9\u3067\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n
    \"MicrosoftGraph_ExcelRestAPI_VBA_03\"<\/p>\n
    \u3061\u306a\u307f\u306b\u3001\u30bb\u30eb\u3092\u53d6\u5f97(Get Range<\/a>)\u3057\u305f\u969b\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306a\u30ec\u30b9\u30dd\u30f3\u30b9\u304c\u8fd4\u3063\u3066\u304d\u307e\u3059\u3002<\/p>\n
    {\r\n    "@odata.context": "https:\/\/graph.microsoft.com\/beta\/$metadata#range",\r\n    "@odata.type": "#microsoft.graph.range",\r\n    "@odata.id": "\/users('********-****-****-****-************')\/drive\/items('**********************************')\/workbook\/worksheets('{00000000-0001-0000-0000-000000000000}')\/range(address='B3')",\r\n    "address": "SampleSheet!B3",\r\n    "addressLocal": "SampleSheet!B3",\r\n    "cellCount": 1,\r\n    "columnCount": 1,\r\n    "columnHidden": false,\r\n    "columnIndex": 1,\r\n    "formulas": [\r\n        ["Test"]\r\n    ],\r\n    "formulasLocal": [\r\n        ["Test"]\r\n    ],\r\n    "formulasR1C1": [\r\n        ["Test"]\r\n    ],\r\n    "hidden": false,\r\n    "numberFormat": [\r\n        ["General"]\r\n    ],\r\n    "rowCount": 1,\r\n    "rowHidden": false,\r\n    "rowIndex": 2,\r\n    "text": [\r\n        ["Test"]\r\n    ],\r\n    "values": [\r\n        ["Test"]\r\n    ],\r\n    "valueTypes": [\r\n        ["String"]\r\n    ]\r\n}<\/pre>\n
    \u79c1\u304c\u8a66\u3057\u305f\u9650\u308a\u3067\u306f\u3001\u307e\u3060\u30d7\u30ec\u30d3\u30e5\u30fc\u7248\u3067\u3042\u308b\u305f\u3081\u304b\u4e0a\u624b\u304f\u7d50\u679c\u304c\u8fd4\u3063\u3066\u3053\u306a\u3044\u30e1\u30bd\u30c3\u30c9\u3082\u3042\u308a\u307e\u3057\u305f\u304c\u3001\u51fa\u756a\u304c\u591a\u305d\u3046\u306aAPI\u306a\u306e\u3067\u4eca\u5f8c\u306b\u671f\u5f85\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n
    \u53c2\u8003Web\u30da\u30fc\u30b8<\/h4>\n