Consulting

Results 1 to 5 of 5

Thread: VBA Unable to Run Excel 2011

  1. #1

    VBA Unable to Run Excel 2011

    Hey Guys,

    I am trying to use code posted here on Excel 2011 but it does not return a value when the Windows version works perfectly.

    http://www.vbaexpress.com/forum/showthread.php?t=32704

    [VBA]Public Function getGoogPrice(symbol As String) As Variant
    Dim xmlhttp As Object
    Dim strURL As String
    Dim CompanyID As String
    Dim x As String
    Dim sSearch As String

    strURL = "http://www.google.com/finance?q=" & symbol
    Set xmlhttp = CreateObject("msxml2.xmlhttp")
    With xmlhttp
    .Open "get", strURL, False
    .send
    x = .responsetext
    End With
    Set xmlhttp = Nothing
    'find goolge's "Company ID" they assign to a symbol
    sSearch = "_companyId ="
    CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
    CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ";") - 1))
    'Use the company ID to retrieve data needed
    'Here is an example of the last price:
    'example: <span id="ref_14135_l">15.79</span>
    sSearch = "ref_" & CompanyID & "_l"">"
    getGoogPrice = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
    getGoogPrice = Left(getGoogPrice, InStr(1, getGoogPrice, "<") - 1)
    'Examine the Page Source to find the Span ID's for the other bits you want
    'They all seem to use the company id

    End Function [/VBA]

    Would anyone have any ideas as to why?

  2. #2
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    I dont think the mac os has msxml2.xmlhttp . Dont have 2011 to give it a whirl, but I'd guess that's the culprit

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Plus the file path has Windows / rather than Mac's :

    Use Application.PathSeparator for cross platform file paths.

  4. #4
    Mikerickson, forgive my ignorance but how would I do that?

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I'm sorry, I missed that that is not a file path, but an internet path.

    I'm guessing that you'll have to write an apple script to get the info from the web.

    The VBA command MacScript will let you call an applescript.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •