I have recently created a live market feed to my excel spreadsheet on my PC usin
ID: 3561770 • Letter: I
Question
I have recently created a live market feed to my excel spreadsheet on my PC using VB coding. It downloads from the Yahoo Finance website. However, when I open my spreadsheet on a Mac with microsoft!#2011 I get a bunch of error messages throughout the coding script. Can anyone take a look and see if it is possible to write a script for the same functions that is compatible on both PC and Mac? I believe part of the problem is because the Mac does have the available reference Microsoft WinHTTP Services, Version 5.1 and so the GET URL function of the code does not work... Below is the coding script:
Private Sub btnRefresh_Click()
Dim W As Worksheet: Set W = ActiveSheet
Dim Last As Integer: Last = W.Range("A1000").End(xlUp).Row
If Last = 1 Then Exit Sub
Dim Symbols As String
Dim i As Integer
For i = 2 To Last
Symbols = Symbols & W.Range("A" & i).Value & "+"
Next i
Symbols = Left(Symbols, Len(Symbols) - 1)
Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hgc6m3m4m5yr"
Dim http As New WinHttpRequest
http.Open "GET", URL, False
http.Send
Dim Resp As String: Resp = http.ResponseText
Dim Lines As Variant: Lines = Split(Resp, vbNewLine)
Dim sLine As String
Dim Values As Variant
For i = 0 To UBound(Lines)
sLine = Lines(i)
If InStr(sLine, ",") > 0 Then
Values = Split(sLine, ",")
W.Cells(i + 2, 2).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0)
W.Cells(i + 2, 3).Value = Values(UBound(Values) - 8)
W.Cells(i + 2, 4).Value = Values(UBound(Values) - 7)
W.Cells(i + 2, 5).Value = Values(UBound(Values) - 6)
W.Cells(i + 2, 6).Value = Replace(Values(UBound(Values) - 5), Chr(34), "")
W.Cells(i + 2, 7).Value = Values(UBound(Values) - 4)
W.Cells(i + 2, 8).Value = Values(UBound(Values) - 3)
W.Cells(i + 2, 9).Value = Values(UBound(Values) - 2)
W.Cells(i + 2, 10).Value = Values(UBound(Values) - 1)
W.Cells(i + 2, 11).Value = Values(UBound(Values))
End If
Next i
W.Cells.Columns.AutoFit
End Sub
Any help would be very much appreciated!!!!!!!
Explanation / Answer
Yes, you are correct, the Mac does not support the WinHTTP services. You will have to do this with a web query on the Mac. You ccan search this group for Web query to get several suggestions. You can make the code work on both platforms by using conditional assembly or run time platform recognition. Soumething like:
if left(application.operatingsystem),3)="Mac" then
your mac code goes here
else
windows code here
end iff