Help with VBA copying html from website

[rowbigred26]

Situation: I need 2010 Excel to take a list of stock symbols (changes daily) given to me daily, login to an internal company website and query our accounts to see who holds the stocks in question. I need the returned query to be copied back into excel for manipulation. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm fairly green but adept at googling. So far I've been able to write code to login, input the symbol, and run the query. I'm unable to find the write code to copy the results and paste them back into excel. The results are always contained within the same html table, a sample of which is below. What can I write to tell vba to locate this table? I've tried getelementbyname and getelementbyID but keep coming up with blanks. I had some limited success with "by ID" and having vba show me the results in a msgBOX as a test but could not find this particular table. My concern is that there is a line break immediately following the td tag that precedes my results. Any suggestions?

Results
                          
            johndoe started running Open Positions Tool (version 2.04 built April 6, 2011) at Thu Mar 29 09:49:26 CDT 2012
       
            looking for positions in LIVETRADING_RETAIL REAL ABCClearing accounts with symbol=MGT
       
            Found 1 accounts with this position
       
            123456789ABCD,S,15.0,MGT,0.0,2464716,OPEN
       
            Processing finished at Thu Mar 29 09:49:33 CDT 2012

I've tried (2 refers third to occurrence of ).:<o></o>
<o></o>

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]Dim std as Str<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]std = Trim(doc.getElementsbyTagName("td")(2).innertext)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]msgBox std<o:p></o:p>[/FONT][/COLOR]
[/COLOR][/FONT]

<o></o>

 

[Norie]

You can't post HTML code here unless you put it in the boards HTML tags.

What's the problem with the code you posted?

If it's not returning the expected value it's probably because it's returning the 3rd td element in the entire document, and there's probably quite a few tds there.

 

[rowbigred26]

Thanks Norie. That was my guess too but the message box comes up blank for any number I choose. Do you know if /td counts as an occurrence of td?

 

[rowbigred26]

I think I figured it out. In previous code I had Dim'd a different IE window as my doc and had closed it with IE.Quit. My current code above was trying to find elements on the closed site. I Dim'd my new window as Doc2 and am getting very close. Thanks for the help Norie, I've enjoyed reading your responses on this site and others.

 

[Norie]

</td> is the closing tag for <td> ,so I suppose <td></td> is the element.

 

[rowbigred26]

I've located the right td but the data is all showing in a single excel cell. I'm only interested in the the line 123456789ABCD,S,15.0,MGT,0.0,2464716,OPEN br/. How can I only get this line? I tried using the getelementsbytagname for the br/ but am not getting results. I'm wondiner if that's because there's no opening br. I've also tried copy/paste special in numerous ways. The common delimiter in each line is the br/ but I would need a "Text to Rows" feature, similar to "Text to Columns". Is there a macro I could add that would parce the cell into rows based on the line breaks?

johndoe started running Open Positions Tool (version 2.04 built April 6, 2011) at Thu Mar 29 09:49:26 CDT 2012 

looking for positions in LIVETRADING_RETAIL REAL ABCClearing accounts with symbol=MGT 

Found 1 accounts with this position 

123456789ABCD,S,15.0,MGT,0.0,2464716,OPEN 

Processing finished at Thu Mar 29 09:49:33 CDT 2012

 

[Norie]

It's hard to tell without seeing the actual page, or HTML.