幾個月前遇到需要透過 VBA 送出多個 HTTP Request 的需求,過了這段時間,User 想要加強安全性,因此改成用 HTTPS,但
但是,User 使用的憑證是自己發行的,如果今天用 Browser 去執行,會出現警告訊息,而透過之前 VBA 的寫法,則會出現錯誤訊息。
要死的是,錯誤訊息是啥我也忘了,改天再補 ....
重點在於 Option 的設定,設定為忽略錯誤,這樣才不會卡住
先把我的範例程式記錄起來,以後才能參考
Dim result As String
Dim myURL As String
Dim winHttpReq As Object
Dim i As Double
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
winHttpReq.Option(4) = 13056 '13056: ignore all err, 0: accept no err
For i = 3 To 100000
If ThisWorkbook.Sheets("Trial Activate").Cells(i, 9).Value = "" Then
myURL = ""
result = ""
myURL = ThisWorkbook.Sheets("Trial Activate").Cells(i, 8)
If myURL <> "" Then
winHttpReq.Open "GET", myURL, False
winHttpReq.Send
result = winHttpReq.responseText
ThisWorkbook.Sheets("Trial Activate").Cells(i, 9).Value = result
ThisWorkbook.Sheets("Trial Activate").Cells(i, 10).Value = Now()
Else
Exit For
End If
DoEvents
End If
Next i
MsgBox "Done"
沒有留言:
張貼留言