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


Comments

Post a Comment

Popular posts from this blog

How to access named pipes using JavaScript in Firefox add-on? -

-
i'm trying access named pipe in firefox add-on. code, based on solution 2 this codeproject question, is: var file = components.classes["@mozilla.org/file/local;1"].createinstance(components.interfaces.nsilocalfile); file.initwithpath("\\\\.\\pipe\\test"); var text = "some text written"; var writer = components.classes["@mozilla.org/network/file-output-stream;1"].createinstance(components.interfaces.nsifileoutputstream); // open file read/write access, , create if not exist. writer.init (file, 0x04 | 0x08, -1, 0); writer.write (text, text.length); writer.flush (); writer.close (); when run firefox scratchpad, get: /* exception: component returned failure code: 0x80520012 (ns_error_file_not_found) [nsifileoutputstream.init] @6 */ line 6 line call writer.init. i've played passing different flags writer.init, no luck. i'm able write normal file path code, not named pipe path. i've been searching more information of da...
Read more

multithreading - OPAL (Open Phone Abstraction Library) Transport not terminated when reattaching thread? -

-
i in process of writing application using opal makes h323 calls. have looked online number of working examples , have managed put resembles should like, @ present able call external ip via application when accept call craps out , dies. leaving me with: 0:12.949 setupcall:8752 sert.cxx(259) assertion fail: transport not terminated when reattaching thread , file d:\voip\software\opal\src\opal\transports.cxx, line 1021 passertfunc(0xde3f88, 0xffffffffd228226f, 0, 0) + 0x82 passertfunc(0x10d6e5f8, 0x3fd, 0, 0x10d6eb98) + 0x15b opaltransport::attachthread(0xde5870, 0xffffffffd22fc76b, 0, 0) + 0x96 h323connection::setupconnection(0xffffffffd22fc713, 0, 0, 0xde6ea8) + 0x196 asynchcallsetup(0x10d3572c, 0, 0, 0xdd0108) + 0x7c pthread1arg<psafeptr<opalconnection,psafeptrbase> >::main(0xffffffffd2282faf from have deduced, perhaps incorrectly if there thread lock issue caused (possibly arising fact application sending expected h323 call, throwing in c...
Read more

node.js - req param returns an empty array -

-
i using node.js , mongodb geolocation app , req param returns empty array exports.findpressure = function(req, res) { var queryobject = req.param('q'); console.log(queryobject); db.collection('pressure', function(err, collection) { collection.find( { loc: { $near :[ req.param('q') ] , $maxdistance : 5 }},{"value" : 1, _id : 0}) .sort({_id : -1}).limit(1).toarray(function(err, items) { res.send(items); }); }); }; the longitude , latitude values displayed in console listening on port 3000... connected 'weather' database 8.9068256,52.019347499999995 /pressure?q=8.9068256,52.019347499999995 200 27ms - 2b the url follows http://localhost:3000/pressure?q=8.9068256,52.019347499999995 if use values 8.9068256,52.019347499999995 getting value database if use req.param('q') returning empty array you seem passing values part of query string. req.query.q should contain comma-s...
Read more