VBA Unable to Run Excel 2011

[ajn946946]

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?

[tpoynton]

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

[mikerickson]

Plus the file path has Windows / rather than Mac's :

Use Application.PathSeparator for cross platform file paths.

[ajn946946]

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

[mikerickson]

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.