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

basically, have following program, allow me obtain stock prices of particular stock, "700" in below example.the stock prices appear in particular table on webpage. in pc computer, able use

.webselectiontype = xlspecifiedtables

.webtables = "4"

to pick out specific tables want webpage. on mac, not , ran run-time error 438 : object doesn't support property or method. annoying. removed 2 lines code. problem that: not extract particular stock prices table web now. can show me how can overcome ?

sub getstockdatatest()     getgooglestockhistory 700  end sub   sub getgooglestockhistory(gint long)       'load google stock hisotry      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 end sub 

office 2011 mac

looking about, seems .webtables property either vba 6 implementation or only works on ie

possible workaround

import on hidden sheet , copy , paste across relevant section since layout should consistent different stock types

---

general guidance

creating web query

for mac, need create web query file in order retrieve web contents. microsoft have produced how-to should you: http://support.microsoft.com/kb/274787

referencing specific tables import

you can specify table select using selection parameter. here example of 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 .iqy file

with .iqy file can load using "finder;c:\folderstructure\queryname.iqy" instead of using url:

sub example()     workbooks("book6").connections.addfromfile _         "c:\users\slocke\desktop\hkquery.iqy"     activesheet.querytables.add(connection:= _         "finder;c:\users\slocke\desktop\hkquery.iqy", destination:=range("$a$1"))         .commandtype = 0         .name = "hkquery"         .webtables = "4"     end end sub 

using .iqy file changing value

i have added iqy , workbook basic vba in importing , updating table based on text box location parameter changing.

you can take further amend loop statement insert value or update in vba directly wanted provide simpler way of changing table make more visual. https://dl.dropboxusercontent.com/u/40423572/so%20items/hkexample.xlsm https://dl.dropboxusercontent.com/u/40423572/so%20items/hkquery.iqy