Mac OSX Excel 2011 VBA上的WinHttpRequest [英] WinHttpRequest on Mac OSX Excel 2011 VBA

问题描述

Mac版本的Excel 2011的参考"中没有WinHttopRequest.我尝试了以下其他文章中介绍的方法:

There is no WinHttopRequest in "References" on Mac version of Excel 2011. I have tried following approaches which I have seen in other posts:

Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")

这给我一个"429"运行时错误:ActiveX控制器无法创建对象.

which gives me a '429' runtime error: ActiveX controller can't create object.

在Mac Excel上是否可以使用WinHttpRequest或类似的方法?我也没有查询表的运气，并希望避免该解决方案. 我认为应该有一个简单的http GET解决方案来解决这个问题.只是无法在Mac Excel中找到它.

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.

我正在尝试从Yahoo Finance API网址获取数据:

I am trying to get data from 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.请指教.谢谢！

I know this works on windows, but I am using a Mac. Please advise. Thanks!

推荐答案

您可以使用QueryTables代替HTTP Get调用(WinHttopRequest)，这显然不受Mac Excel 2011的支持.下面的代码对我有用-输入在以A2开头的列中的代码，在以B1开头的行中输入yahoo财务标签(即a，b，r，n).

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.

您可以组合URL以调用csv的YF，然后使用QueryTable进行调用并将结果粘贴到工作表中.

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.

在Mac Excel 2011上运行的代码:

Code working on 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

这篇关于Mac OSX Excel 2011 VBA上的WinHttpRequest的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！