WebQuery to import data table

In “Sheet I” cell F18, F19, F20, I have three URL (derived through formula). This refers to webpage which source code reveals that it uses XHTML format and has some java script.

Using URL in cell F18, I want to import data table in sheet F, from Row B2
Using URL in cell F19, I want to import data table in sheet F, from Row B102
Using URL in cell F20, I want to import data table in sheet F, from Row B202

Once data is imported in sheet F, all formatting and link to webpage should be removed.
In any case such cleaning should be done within range A:Z.

From Column AA onwards, I want to perform some calculation, which would be repetitive each time when I bring new data, it should not be disturbed.

Likewise, there are formula based URL in Sheet I - Cell Z18, Z19, Z20 which is required to be placed in above manner but in Sheet O.

Kindly help. Sample File attached.

I am not very clear about your problems. See if help you:

Please Login or Register  to view this content.

Thanks, but if u run the code, you would observe that Data that should appear in table, comes in the first line (say B2, and B100) .

While the table structure is there, corresponding value is in first row only.

For instance. Cell B1 value is like

{"valid":"true","isinCode":null,"lastUpdateTime":"03-APR-2014 10:44:05","ocLink":"\/marketinfo\/sym_map\/symbolMapping.jsp?symbol=ACC&instrument=-&date=-&segmentLink=17&symbolCount=2","tradedDate":"03APR2014","data":[{"change":"-0.80","sellPrice1":"1,366.80","buyQuantity3":"250","sellPrice2":"1,366.90","buyQuantity4":"500","buyQuantity1":"250","ltp":"10.59","buyQuantity2":"250","sellPrice5":"1,367.80","sellPrice3":"1,367.00","buyQuantity5":"250","sellPrice4":"1,367.40","underlying":"ACC","bestSell":"10.46","annualisedVolatility":"38.21","optionType":"-","prevClose":"1,367.70","pChange":"-0.06","lastPrice":"1,366.90","lowPrice":"1,355.00","strikePrice":"-","numberOfContractsTraded":"978","underlyingValue":"1,358.60","openInterest":"10,68,500","impliedVolatility":"-","vwap":"1,363.37","totalBuyQuantity":"68,000","openPrice":"1,367.70","closePrice":"-","bestBuy":"8.81","changeinOpenInterest":"37,750","clientWisePositionLimits":"933162","totalSellQuantity":"58,750","dailyVolatility":"2.00","sellQuantity5":"500","marketLot":"250","expiryDate":"24APR2014","marketWidePositionLimits":"18663247","sellQuantity2":"500","sellQuantity1":"250","buyPrice1":"1,365.50","sellQuantity4":"500","buyPrice2":"1,365.15","sellQuantity3":"250","buyPrice4":"1,364.40","buyPrice3":"1,365.10","buyPrice5":"1,364.30","turnoverinRsLakhs":"3,333.44","pchangeinOpenInterest":"3.66","settlementPrice":"1367.70","instrumentType":"FUTSTK","highPrice":"1,373.10"}],"companyName":"ACC Limited","eqLink":"\/live_market\/dynaContent\/live_watch\/get_quote\/GetQuote.jsp?symbol=ACC"}

Open the url in browser, and have a look at the webpage, how data is organised.

Normally, if HTML page is being in place, above code would work with no problem, but because the URL (Link) is in xhtml, or having some Java variables, this problem is coming.

Kindly help.

maybe you can adjust this to suit
The span tags in the table is allow the java script to run .. it references a array

Please Login or Register  to view this content.

Hi
Forgot to say that you have to add a MSXML2.XMLHTTP library
so in the VBE > Tools> References > "Microsoft HTML object Library"

values will be printed to the intermediate window

In a hurry. Quick reply.

1. You are missing a reference library. Google HTMLDocument to find out what reference library you need to add to your project. (At a guess I'd say 'Microsoft HTML Objects Library' )

2. Yes it can. Give your desired input cell a name (so it's a named range) and then read the named range as a string. Replace the URL with the string variable.

cross-post without supplying a link to the duplicate question on the other forum.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).