Mac Excel2011VBA上的WinHttpRequest

Question

在MacExcel2011版本的“参考”中没有WinHttopRequest。我尝试了以下我在其他帖子中看到的方法：

Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")

这给了我一个'429‘运行时错误: ActiveX控制器无法创建对象。

有没有办法在Mac Excel上使用WinHttpRequest或类似的东西？我在查询表方面也不太走运，所以我想避免这种解决方案。对于这个问题，我想应该有一个简单的http GET解决方案。就是找不到Mac Excel。

我正在尝试从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

我知道这在windows上可以用，但我用的是Mac。请给我建议。谢谢!

Answer 1

您可以使用QueryTables代替HTTP Get调用(WinHttopRequest)，这显然不受Mac Excel2011的支持。下面的代码适用于我-在列中输入股票代码，以A2开头，在以B1开头的行中输入雅虎金融标签(即a，b，r，n)。

您可以将URL汇编为csv调用YF，然后使用QueryTable进行调用并将结果粘贴到工作表中。

在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

Answer 2

我不相信Mac有任何与MSXML.ServerXMLHTTP等同的东西。suggestion from another stackoverflow thread就是使用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