CODEWITHSUNDEEP
excelvbamacos
mynameisJEFF
mynameisJEFF

Reputation: 4239

Failed to extract specific tables from web onto spreadsheet in Excel VBA (Mac)

Basically, I have the following program, which allow me to obtain stock prices of a particular stock, say "700" in the below example.The stock prices appear in a particular table on the webpage. In a PC computer, I was able to use

.WebSelectionType = xlSpecifiedTables

.WebTables = "4"

to pick out the specific tables that I want from the webpage. However on a mac, I could not do that and I ran into this run-time error 438 : object doesn't support this property or method. This is really annoying. So I removed those 2 lines from the code. But the problem is that: I could not extract the particular stock prices table from the web now. Can anyone show me how I can overcome this ? 

Sub getStockDataTest()
    getGoogleStockHistory 700

End Sub


Sub getGoogleStockHistory(gInt As Long)
      'load google stock hisotry

    With ActiveSheet.QueryTables.Add(Connection:="URL;https://www.google.com.hk/finance/historical?q=HKG%3A" & Format(gInt, "0000") & "&num=200", Destination:=ThisWorkbook.Sheets("Query").[A1])
        .Name = "WebQuery"
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .Refresh
    End With
End Sub

Upvotes: 3

Views: 1495

Answers (1)

Steph Locke
Steph Locke

Reputation: 6166

Office 2011 for Mac

Looking about, is seems that the .WebTables property is either a VBA 6 implementation or something that only works on IE

Possible workaround

Import everything on a hidden sheet and then copy and paste across the relevant section since layout should be fairly consistent for different stock types

General Guidance

Creating a web query

For Mac, you need to create a web query file in order to retrieve the web contents. Microsoft have produced a how-to that should help you: http://support.microsoft.com/kb/274787

Referencing specific tables to import

You can specify which table to select using the Selection parameter. Here is an example of a webquery built using Excel 2013 Windows.

WEB
1
https://www.google.com.hk/finance/historical?q=HKG%3A0005&num=200#
num=200&q=["stock","stock sym"]

Selection=4
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Using an .iqy file

With .iqy file you can then load it using "FINDER;C:\folderstructure\queryname.iqy" instead of using URL:

Sub example()
    Workbooks("Book6").Connections.AddFromFile _
        "C:\Users\slocke\Desktop\hkquery.iqy"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;C:\Users\slocke\Desktop\hkquery.iqy", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "hkquery"
        .WebTables = "4"
    End With
End Sub

Using the .iqy file with a changing value

I have added an iqy and workbook with some basic vba in for importing and updating the table based on a text box location for the parameter you are changing.

You can take this further to amend your loop statement to insert the value or you could probably update it in the VBA directly but I wanted to provide a simpler way of changing the table to make it more visual. https://dl.dropboxusercontent.com/u/40423572/SO%20items/hkexample.xlsm https://dl.dropboxusercontent.com/u/40423572/SO%20items/hkquery.iqy

Upvotes: 7

Related Questions