I created the Macro below in Excel 2010 for Windows that runs well, but when I run it in Excel for Mac 2011 I get an error on the ".Web" commands. Is there a place where I can see the list of commands for both the windows version and the Mac version?
A bit of background on of what the Macro does.
The Macro downloads financial data from stocks, which symbols listed in column H. The data of interest is in certain tables only, not all of them, so the command ".webtables". This data is copied to another section of the spreadsheet, the data is cleared and it continues until the list of symbols is exhausted.
Errors encountered:
At line below, the Macro stops and Excel closes the workbook.
.Name = "Yahoo analyst estimates"
Changing above line to
.Name = False
allows macro to continue But none of the ".web" commands below are not interpreted.
So my key issue is to find the list of ".web" like commands for Excel for Mac 2011.
Sorry for the long explanation.
' Download_Yahoo_Metrics Macro
' Downloads Tables for Analyst Estimates, Analyst Opinion and Key Statistics
'
Dim ScrURL As String
lr = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row 'delimits the last row
fr = ActiveSheet.Cells(1, "C") 'delimits the first row
For r = fr To lr
Range("B5:F53").ClearContents 'clear previous
ScrURL = "URL;http://finance.yahoo.com/q/ae?s=" & ActiveSheet.Cells(r, "H")
'
With ActiveSheet.QueryTables.Add(Connection:=ScrURL, Destination:=Range("B5"))
.Name = "Yahoo analyst estimates"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,11,14,17,20,23"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet
.Cells(r, "i") = .Range("C47")
.Cells(r, "j") = .Range("D47")
.Cells(r, "k") = .Range("F47")
.Cells(r, "l") = .Range("C48")
.Cells(r, "m") = .Range("C49")
.Cells(r, "n") = .Range("D49")
.Cells(r, "o") = .Range("F49")
End With
Next
End Sub
Bookmarks