Created
June 3, 2018 02:07
-
-
Save muramoto1041/2e18c8009a2c8748ee839cfbb2134bf4 to your computer and use it in GitHub Desktop.
Excel javascript json
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'(1)JSONデータを解析し、オブジェクト変数に格納 | |
Dim objJson As Object | |
Set objJson = js.CodeObject.eval("(" + resText + ")") | |
'(2)解析済みのJSONデータの件数を取得 | |
Dim objRowCount As Integer | |
objRowCount = CallByName(objJson, "length", VbGet) | |
'(3)結果をワークシートへ転記 | |
For idx = 0 To objRowCount - 1 'データの件数だけ繰り返す | |
'(4)JSONデータ1件分を取り出し、オブジェクト変数に格納 | |
Dim objRow As Object | |
Set objRow = CallByName(objJson, idx, VbGet) | |
'(5)JSONデータ1件分の該当項目を取り出し、セルに転記 | |
'リンク付きのタイトルを設定する | |
ActiveSheet.Hyperlinks.Add _ | |
anchor:=Range("word_title").Offset(idx + 1), _ | |
Address:=CallByName(objRow, "url", VbGet), _ | |
TextToDisplay:=CallByName(objRow, "title", VbGet) | |
'HTMLの改行記号を、VBAの改行記号に置換して設定する | |
Range("meaning_title").Offset(idx + 1) _ | |
= Replace(CallByName(objRow, "body", VbGet), "<br/>", vbCrLf) | |
'更新日時をExcelで認識できる形式に変換して設定する | |
Range("date_title").Offset(idx + 1) _ | |
= timeConv(CallByName(objRow, "datetime", VbGet)) | |
Set objRow = Nothing | |
Next | |
Set objJson = Nothing | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment