Mac OSX Excel 2011 VBA 上的 WinHttpRequest
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18282443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WinHttpRequest on Mac OSX Excel 2011 VBA
提问by brno792
There is no WinHttopRequest in "References" on Mac version of Excel 2011. I have tried following approaches which I have seen in other posts:
在 Excel 2011 的 Mac 版本的“参考”中没有 WinHttopRequest。我尝试了以下在其他帖子中看到的方法：
Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")
which gives me a '429' runtime error: ActiveX controller can't create object.
这给了我一个“429”运行时错误：ActiveX 控制器无法创建对象。
Is there a way to use WinHttpRequest or something similar on Mac Excel? I have had no luck with query tables either, and want to avoid that solution. There should be a simple http GET solution to this problem I would think. Just cant find it out for Mac Excel.
有没有办法在 Mac Excel 上使用 WinHttpRequest 或类似的东西？我也没有查询表的运气，并希望避免该解决方案。我认为应该有一个简单的 http GET 解决方案来解决这个问题。只是无法为 Mac Excel 找到它。
I am trying to get data from Yahoo Finance api url:
我正在尝试从 Yahoo Finance api url 获取数据：
Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"
Dim HTTP As New WinHttpRequest
HTTP.Open "GET", URL, False
HTTP.Send
I know this works on windows, but I am using a Mac. Please advise. Thanks!
我知道这适用于 Windows，但我使用的是 Mac。请指教。谢谢！
回答by jidulberger
You can use QueryTables in place of the HTTP Get call (WinHttopRequest), which evidently is not supported by Mac Excel 2011. The code below worked for me - enter the tickers in column, starting with A2, enter yahoo finance tags (i.e. a,b, r, n) in row starting with B1.
您可以使用 QueryTables 代替 HTTP Get 调用 (WinHttopRequest)，这显然不受 Mac Excel 2011 支持。下面的代码对我有用 - 在列中输入股票代码，从 A2 开始，输入雅虎金融标签（即 a, b, r, n) 在以 B1 开头的行中。
You can assemble the URL to call YF for the csv, then use a QueryTable to make the call and paste the results in your worksheet.
您可以组合 URL 以调用 csv 的 YF，然后使用 QueryTable 进行调用并将结果粘贴到您的工作表中。
Code working on Mac Excel 2011:
适用于 Mac Excel 2011 的代码：
Sub Yahoo_Finance_API_Call_MacExcel2011()
Dim head As Range
Set head = Range("A1")
Dim wb As Workbook 'In the event that you'll use different workbooks
Dim src As Worksheet 'In the event that you'll use different a source worksheet
Dim tgt As Worksheet 'In the event that you'll use different a target worksheet
Set wb = ThisWorkbook
Set src = wb.Sheets("Sheet1")
Set tgt = wb.Sheets("Sheet1")
'Assemble Symbols for API Call
Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
Symbols = Symbols & cell.Value & "+"
Next cell
Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'
'Assemble Tags or API Call
Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
tags = tags & cell.Value
Next cell
'Build URL
URL = "TEXT;http://finance.yahoo.com/d/quotes.csv?s=" 'Use TEXT to collect API data below
URL = URL & Symbols & "&f=" & tags
'Range("A1").Value = URL 'This will output the assembled URL in a1 for QA if need be
'Call API
With tgt.QueryTables.Add(Connection:= _
URL, _
Destination:=Range(head.Offset(1, 1), head.Offset(1, 1).End(xlDown)))
.RefreshStyle = xlOverwriteCells
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.BackgroundQuery = True
.TextFileCommaDelimiter = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.SaveData = False
End With
End Sub
回答by jeefreak
I do not believe Macs have anything equivalent to MSXML.ServerXMLHTTP. A suggestion from another stackoverflow threadis to use QueryTables. In summary the thread suggests:
我不相信 Mac 有任何等同于 MSXML.ServerXMLHTTP 的东西。一从另一个线程计算器建议是使用QueryTables。总之，线程建议：
With ActiveSheet.QueryTables.Add(Connection:="URL;http://carbon.brighterplanet.com/flights.txt", Destination:=Range("A2"))
.PostText = "origin_airport=MSN&destination_airport=ORD"
.RefreshStyle = xlOverwriteCells
.SaveData = True
.Refresh
End With
