LoginSignup
@yuka-mori

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

MacでExcelVBAのHTTP通信を有効化する方法が知りたい

Q&A

Closed

MacExcelHTTPExcelVBA

解決したいこと

MacでExcelVBAのHTTP通信を有効化する方法が知りたい。
HTTP通信の有効化を試みたものの、有効化になったのかの検証の仕方がわからず困っております。
今読んでいる書籍(ChatGPT API× Excel VBA Automation work technique)にはWindows用の方法しか記載なく、Mac用のHTTP通信の有効化とその検証方法が知りたいです。

試したこと

次のページを参考に以下のコードを入力し、HTTP通信の有効化を試みました。
https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac

Option Explicit

' execShell() function courtesy of Robert Knight via StackOverflow
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac

Private Declare PtrSafe Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function pclose Lib "libc.dylib" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function feof Lib "libc.dylib" (ByVal file As LongPtr) As LongPtr

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As LongPtr
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Function HTTPGet(sUrl As String, sQuery As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
    sResult = execShell(sCmd, lExitCode)

    ' ToDo check lExitCode

    HTTPGet = sResult

End Function

書籍に載っているwindows用の検証方法

Private Sub HttpTest()
    Dim client As New MSXML2.ServerXMLHTTP60
    client.setTimeouts 30000, 30000, 30000, 60000
    client.Open "GET", "https://www.impress.co.jp", True
    client.send
    client.waitForResponse 60
    
    Debug.Print client.responseText
End Sub
0 likes

1Answer

nak435
@nak435

下記のコードで試しましたが、ちゃんと機能しています。
（気象庁のAPIで東京都の天気を取得。ただし、結果はUTF-8のためDebug.Printは文字化けします）

Sub test()
    Dim result As String
    result = HTTPGet("https://www.jma.go.jp/bosai/forecast/data/forecast/130000.json", "")
    Debug.Print result
End Sub
結果
[{"publishingOffice":"気象庁","reportDatetime":"2023-09-22T05:00:00+09:00","timeSeries":[{"timeDefines":["2023-09-22T05:00:00+09:00","2023-09-23T00:00:00+09:00"],"areas":[{"area":{"name":"東京地方","code":"130010"},"weatherCodes":["203","203"],"weathers":["くもり　時々　雨　所により　昼前　まで　雷　を伴う","くもり　時々　雨"],"winds":["南西の風　後　東の風　２３区西部　では　はじめ　南西の風　やや強く","北の風　後　北東の風"],"waves":["１メートル　後　０．５メートル","０．５メートル"]},{"area":{"name":"伊豆諸島北部","code":"130020"},"weatherCodes":["203","203"],"weathers":["くもり　時々　雨　所により　朝　まで　雷を伴い　激しく　降る","くもり　時々　雨"],"winds":["南西の風　やや強く　後　北東の風　やや強く","北東の風　後　やや強く"],"waves":["２．５メートル　後　１．５メートル","１．５メートル"]},{"area":{"name":"伊豆諸島南部","code":"130030"},"weatherCodes":["203","302"],"weathers":["くもり　時々　雨　所により　昼前　まで　雷を伴い　激しく　降る","雨　時々　くもり　所により　雷　を伴う"],"winds":["南西の風　はじめ　やや強く","南西の風　後　北東の風　三宅島　では　後　北東の風　やや強く"],"waves":["２メートル　後　１．５メートル　ただし　三宅島　では　２．５メートル　後　１．５メートル","１．５メートル"]},{"area":{"name":"小笠原諸島","code":"130040"},"weatherCodes":["101","201"],"weathers":["晴れ　時々　くもり　所により　夜遅く　雨　で　雷を伴う","くもり　時々　晴れ　所により　雨　で　雷を伴う"],"winds":["東の風","東の風"],"waves":["１．５メートル","１．５メートル"]}]},{"timeDefines":["2023-09-22T06:00:00+09:00","2023-09-22T12:00:00+09:00","2023-09-22T18:00:00+09:00","2023-09-23T00:00:00+09:00","2023-09-23T06:00:00+09:00","2023-09-23T12:00:00+09:00","2023-09-23T18:00:00+09:00"],"areas":[{"area":{"name":"東京地方","code":"130010"},"pops":["50","50","60","50","30","50","50"]},{"area":{"name":"伊豆諸島北部","code":"130020"},"pops":["60","50","50","50","30","50","50"]},{"area":{"name":"伊豆諸島南部","code":"130030"},"pops":["50","50","50","50","50","50","50"]},{"area":{"name":"小笠原諸島","code":"130040"},"pops":["10","10","20","30","30","30","30"]}]},{"timeDefines":["2023-09-22T09:00:00+09:00","2023-09-22T00:00:00+09:00","2023-09-23T00:00:00+09:00","2023-09-23T09:00:00+09:00"],"areas":[{"area":{"name":"東京","code":"44132"},"temps":["27","27","22","27"]},{"area":{"name":"大島","code":"44172"},"temps":["29","29","23","27"]},{"area":{"name":"八丈島","code":"44263"},"temps":["29","29","24","28"]},{"area":{"name":"父島","code":"44301"},"temps":["32","32","26","31"]}]}]},{"publishingOffice":"気象庁","reportDatetime":"2023-09-21T17:00:00+09:00","timeSeries":[{"timeDefines":["2023-09-22T00:00:00+09:00","2023-09-23T00:00:00+09:00","2023-09-24T00:00:00+09:00","2023-09-25T00:00:00+09:00","2023-09-26T00:00:00+09:00","2023-09-27T00:00:00+09:00","2023-09-28T00:00:00+09:00"],"areas":[{"area":{"name":"東京地方","code":"130010"},"weatherCodes":["203","202","201","101","201","201","101"],"pops":["","50","30","20","30","30","20"],"reliabilities":["","","B","A","A","A","B"]},{"area":{"name":"伊豆諸島","code":"130100"},"weatherCodes":["203","203","202","101","201","201","101"],"pops":["","70","50","20","30","30","20"],"reliabilities":["","","C","A","B","A","A"]},{"area":{"name":"小笠原諸島","code":"130040"},"weatherCodes":["101","201","201","201","201","201","201"],"pops":["","40","30","30","30","30","30"],"reliabilities":["","","A","B","A","A","A"]}]},{"timeDefines":["2023-09-22T00:00:00+09:00","2023-09-23T00:00:00+09:00","2023-09-24T00:00:00+09:00","2023-09-25T00:00:00+09:00","2023-09-26T00:00:00+09:00","2023-09-27T00:00:00+09:00","2023-09-28T00:00:00+09:00"],"areas":[{"area":{"name":"東京","code":"44132"},"tempsMin":["","22","21","19","19","21","23"],"tempsMinUpper":["","23","22","21","21","23","24"],"tempsMinLower":["","20","19","18","18","20","21"],"tempsMax":["","28","29","29","30","31","32"],"tempsMaxUpper":["","31","31","31","31","33","34"],"tempsMaxLower":["","26","26","27","28","29","29"]},{"area":{"name":"八丈島","code":"44263"},"tempsMin":["","24","23","23","23","23","25"],"tempsMinUpper":["","25","24","24","24","25","26"],"tempsMinLower":["","22","21","22","21","22","23"],"tempsMax":["","29","27","29","30","30","30"],"tempsMaxUpper":["","30","29","30","31","31","31"],"tempsMaxLower":["","28","26","28","28","29","28"]},{"area":{"name":"父島","code":"44301"},"tempsMin":["","26","26","26","26","26","26"],"tempsMinUpper":["","27","27","27","27","27","28"],"tempsMinLower":["","25","25","25","25","25","25"],"tempsMax":["","31","32","31","31","32","32"],"tempsMaxUpper":["","32","33","32","32","33","33"],"tempsMaxLower":["","30","31","30","30","31","31"]}]}],"tempAverage":{"areas":[{"area":{"name":"東京","code":"44132"},"min":"18.3","max":"25.4"},{"area":{"name":"八丈島","code":"44263"},"min":"21.2","max":"26.3"},{"area":{"name":"父島","code":"44301"},"min":"25.5","max":"29.6"}]},"precipAverage":{"areas":[{"area":{"name":"東京","code":"44132"},"min":"21.7","max":"58.6"},{"area":{"name":"八丈島","code":"44263"},"min":"48.3","max":"107.6"},{"area":{"name":"父島","code":"44301"},"min":"9.1","max":"35.2"}]}}]

今回は省略したsQuery引数ですが、これは、getのURLに付加するパラメタです。
イメージは下記の通り（認証エラーで実際には取得できませんが）

HTTPGet("https://www.google.com/search", "q=get+parameter")
'==> https://www.google.com/search?q=get+parameter
0Like

Comments

  1. @yuka-mori

    Questioner

    スクリーンショット 2023-09-22 11.31.06.png
    ありがとうございます！
    確かに文字化けで内容はよくわかりませんが、HTTP通信が有効化していることはわかりました！
    これで次のステップに進めそうです！昨日に引き続きありがとうございます！

Your answer might help someone💌

LoginSign Up