LoginSignup
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@yaju(やじゅ)

【Excel】Mac版VBAでHTTP通信を行う

Posted at

はじめに

Excelでwebブラウザの操作を自動化する最小機能のみ実装したフレームワークの「TinySeleniumVBA」があります。
これは今のところActiveXを使用しているため、Windows専用になっています。ActiveXの部分を取り除き代替部分をVBA-toolsライブラリー群を使えば、Mac上のExcelで動作するかもと取り組みました。

これまで、その準備を着々と進めてきました。

TinySeleniumVBAでは、HTTP通信する上でActiveXのMSXML2.ServerXMLHTTPを使用しています。
MacではActiveX使用できないため、HTTP通信する方法を模索しました。
2016年でExcel for Mac 2011を使用した時の記事も参考にしました。

環境

Mac Book Pro(2.3 GHz 8コアIntel Core i9) Monterey 12.2.1
Microsoft Office Home and Student 2019

VBA-Web

VBA-toolsライブラリー群には、HTTP通信用にVBA-Webがあります。

HTTP通信部分の書き換え

TinySeleniumVBAのHTTP通信の部分は下記のようになっています。
今回は、これを採用します。

Windows

WebDriver.cls
' Send HTTP request
Private Function SendRequest(method As String, url As String, Optional data As Dictionary = Nothing) As Dictionary
    Dim client As Object
    Set client = CreateObject("MSXML2.ServerXMLHTTP")
    
    client.Open method, url
    
    If method = "POST" Or method = "PUT" Then
        client.setRequestHeader "Content-Type", "application/json"
        client.send JsonConverter.ConvertToJson(data)
    Else
        client.send
    End If

    Do While client.readyState < 4
        DoEvents
    Loop

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(client.responseText)
    Set SendRequest = Json
End Function

Mac

WebDriver.cls
' Send HTTP request
Private Function SendRequest(method As String, url As String, Optional Data As Dictionary = Nothing) As Dictionary
    Dim client As Object
    Dim Response As WebResponse

    Set client = New WebClient
    If method = "POST" Or method = "PUT" Then
        Set Response = client.PostJson(url, Data)
    Else
        Set Response = client.GetJson(url)
    End If

    Dim json As Object
    Set json = JsonConverter.ParseJson(Response.Content)
    Set SendRequest = json
End Function

正しい値が返らない

TinySeleniumVBAのExapmleを動かしていくと、Googleの検索BOXを取得する処理があるのですが、ここで513エラーになってしまいます。

Exapmle.bas
' Open browser
Driver.OpenBrowser

' Navigate to Google
Driver.Navigate "https://www.google.co.jp/?q=selenium"

' Get search textbox
Dim searchInput
Set searchInput = Driver.FindElement(By.Name, "q")

調査

デバッグで追っていくと、Web_Curlの渡すパラメーターに何か問題がありそうです。

? Web_Curl
curl -i --connect-timeout 5 --max-time 15 --location 
-H 'User-Agent: VBA-Web v4.1.6 (https://github.com/VBA-tools/VBA-Web)' 
-H 'Accept: application/json' 
-H 'Content-Type: application/json' 
-H 'Content-Length: 94' 
-X POST 
-d '{"using":"css selector","value":"[name=\"q\"]","sessionId":"17b3467186550c38a92aeee9f809712e"}'
'http://localhost:9515/session/17b3467186550c38a92aeee9f809712e/element'

Content-Length

最初にContent-Length: 94を外してみました。-dの値の桁数は94文字あるのですが、変換か何かでパラメーターの桁数が一致していないと正しく動作しないし、Content-Lengthがなくても動作するからです。

WebRequest.cls
Public Sub Prepare()
    ' Add/replace general headers for request
    SetHeader "User-Agent", Me.UserAgent
    SetHeader "Accept", Me.Accept
    If Me.method <> WebMethod.HttpGet Then
        SetHeader "Content-Type", Me.ContentType
        'SetHeader "Content-Length", VBA.CStr(Me.ContentLength)
    End If
End Sub

残念ながら、これでも513エラーになってしまいます。

JSON

次はJSONを疑いました。
エラーになるJSONにはダブルクォテーションの囲み内にダブルクォテーションがあった場合に\"としてエスケープしています。これはJSONのエスケープ処理としては正しいです。

// エラーにならないJSON
'{"url":"https://www.google.co.jp/?q=selenium","sessionId":"17b3467186550c38a92aeee9f809712e"}'
// エラーになるJSON
'{"using":"css selector","value":"[name=\"q\"]","sessionId":"17b3467186550c38a92aeee9f809712e"}'

試しにエスケープされないように、中身をシングルクォテーションにしてみました。

WebDriver.cls
Private Function ToSelector(by_ As By, ByVal value As String) As Dictionary
        ElseIf by_ = By.Name Then
            #If Mac Then
                value = "[name='" + value + "']"
            #Else
                value = "[name=""" + value + """]"
            #End If
        End If
    End If

JSONは下記のように[name="q"]→[name='q']になります。

'{"using":"css selector","value":"[name='q']","sessionId":"17b3467186550c38a92aeee9f809712e"}'

こうするとエラーにならずに正常にパラメーターが返ってくるようになりました。
それではとContent-Lengthを復活させてみたところ、タイムアウトエラーになってしまいました。
※その後、WireSharkで確認したところ、シングルクォーテーションが除去されて"value":"[name=q]"となっていたため、Content-Lengthが一致しない状態だったようです。

WireSharkで確認

ターミナル画面で、Content-Lengthと\"としてエスケープした状態で、curlコマンドにセットすると正しく値が返ってきます。そうすると、VBAからの呼び出し時に何かが起きている。

ネットワーク・アナライザ・ソフトウェアである「WireShark」にMac版があることを知り、ダウンロードしました。
https://www.wireshark.org/download.html

Loopback:Io0でフィルターでtcp.port==9515をしてみました。

正常

ターミナル画面上でcurlコマンドで実行しました。バックスラッシュ5cは正しく表示されています。
スクリーンショット 2022-05-02 23.20.50.png

異常

VBA-WebからCurlコマンドで実行しました。バックスラッシュの部分が80に変換されてしまっています。

スクリーンショット 2022-05-02 23.21.09.png

原因

VBA-Webでは、Macだとpopen関数を使用してcurlコマンドを呼び出しHTTP通信しています。
そして、popen関数は文字コードにMacJapanese(AppleがShift_JISを独自に拡張した文字コード)が使用されます。

MacJapaneseのWikipediaには、半角バックスラッシュ「\」は、0x80に変換されると書かれています。
0x80 …… U+5C reverse solidus (= backslash)、半角バックスラッシュ「\」（ASCII の0x5Cと同じ）

もう少し調べると下記の記述が見つかりました。

Mac OS では、バージョン 7.1〜9.x で主流だった日本語文字コード MacJapanese において、0x5C に円記号が、0x80 にバックスラッシュ記号がそれぞれ別々のコードポイントに存在している
バックスラッシュと円記号 (¥) 0x5C番地の問題について

対応方法1

VBA-Webを使用する前提で一部のプログラムを書き換えます。
半角バックスラッシュ「\」と円記号(¥)は違うコードが割り当てられており、円記号(¥)が0x5cとなっている。
ということは、半角バックスラッシュ(\)を円記号(¥)に置換すればいい。

変更前

WebClient.cls
Public Function PrepareCurlRequest(Request As WebRequest) As String
    ' Add method, data, and url
    web_Curl = web_Curl & " -X " & WebHelpers.MethodToName(Request.method)
    web_Curl = web_Curl & " -d '" & Request.body & "'"
    web_Curl = web_Curl & " '" & Me.GetFullUrl(Request) & "'"

変更後

日本語版の場合に半角バックスラッシュ(\)を円記号(¥)に置換する処理に書き換える。

WebClient.cls
Public Function PrepareCurlRequest(Request As WebRequest) As String
    ' Add method, data, and url
    web_Curl = web_Curl & " -X " & WebHelpers.MethodToName(Request.method)
    Dim body As String: body = Request.body
    If Application.LanguageSettings.LanguageID(msoLanguageIDInstall) = 1041 Then
        body = Replace(body, "\", "¥")
    End If
    web_Curl = web_Curl & " -d '" & body & "'"
    web_Curl = web_Curl & " '" & Me.GetFullUrl(Request) & "'"

当初は原因が分からずVBA-Webのissuesに書いたのですが、最終的には自己解決してしまいました。

対応方法2

実は、対応方法1が分かるまで別の方法で動かしていました。
WebDriver(chromedriver or msedgedriver)を起動させるためにAppleScriptでShellを動かすようにしているので、それを再利用します。

AppleScriptのShellでCurlコマンドを動かしたところ、半角バックスラッシュ(\)のままで動作しました。
AppleScriptTask関数を利用する手順は2段階となります。

  1. スクリプトファイルを「~/Library/Application Scripts/com.microsoft.Excel/」に保存する。
  2. VBA内でAppleScriptTaskからスクリプトファイルを呼び出す。
shell.scpt
on synchandler(param)
    try
        set ret to do shell script param
        return ret
    on error number n
        return "error " & n
    end try
end synchandler

on ansynchandler(param)
    try
        set ret to do shell script param & " &"
        return ret
    on error number n
        return "error " & n
    end try
end ansynchandler

web_Curl変数には、Curlコマンドをセットします。

Dim result As String
result = AppleScriptTask("shell.scpt", "synchandler", web_Curl)

resultには下記の結果が返ってきます。
注意としてMacなので改行コードはLF(Chr$(13)の1文字で区切ります。

HTTP/1.1 200 OK
Content-Length:88
Content-Type:application/json; charset=utf-8
cache-control:no-cache

{"value":{"element-6066-11e4-a52e-4f735466cecf":"f78d74e7-ed73-4b67-af7f-e5e77c8b73cc"}}

文字コード

AppleScriptのShellでCurlコマンドを動かしたところ半角バックスラッシュ(\)のままで動作したので、文字コードはMacJapaneseではないはず、では何になっているのでしょうか？

popenで文字コードを調べた方法を真似て確認してみました。

Dim result As String
result = AppleScriptTask("shell.scpt", "synchandler", "echo 'あいうえお'>/tmp/a.txt")

odコマンドで出力されたコードを確認します。

$ od -x /tmp/a.txt
0000000      81e3    e382    8481    81e3    e386    8881    81e3    0a8a
0000020

Unicodeの「あ」はU+3042、UTF-8では e3 81 82 の3byteになります。
https://orange-factory.com/sample/utf8/code3/e3.html#Hiragana

自PCはIntelCPUのためリトルエンディアンとなり、実際のコードはe38182＝UTF-8ということです。

最後に

ようやく技術的な理解と準備が出来ました。
TinySeleniumVBAのMac対応が最終目的です、名前にTinyが付いている通り最小限のコードにしたい。
VBA-Webで仕組みを理解したので、VBA-Webを使用しないで独自クラスを作成します。その際に将来的なことを考えて、UTF-8が使える対応方法2を使います。

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?