PDA

View Full Version : VBA Unable to Run Excel 2011



ajn946946
01-24-2011, 01:13 AM
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

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

Would anyone have any ideas as to why?

tpoynton
01-24-2011, 04:12 PM
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
01-24-2011, 08:34 PM
Plus the file path has Windows / rather than Mac's :

Use Application.PathSeparator for cross platform file paths.

ajn946946
01-25-2011, 10:45 PM
Mikerickson, forgive my ignorance but how would I do that?

mikerickson
01-26-2011, 09:21 PM
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.