VBA Problem taking cell data from IE

[SBrooky]

Im trying to pull all the text from a cell on a webpage. Here's my code so far:

[vba]Private Sub CommandButton1_Click()
Dim ie As Object
Dim result As Long

Set ie = CreateObject("internetexplorer.application")
ie.Visible = True
ie.navigate "http ://ww w.mycarcheck.com/"

While ie.busy
DoEvents
Wend

ie.Document.getElementById("reg_no").Value = TextBox1.Value
ie.Document.all("submit_button").Click

While ie.busy
DoEvents
Wend

result = ie.Document.getElementsByTagName("car_info")
TextBox2.Text = result
ie.Quit
Set ie = Nothing
End Sub[/vba]
The first problems occurs on
[vba]ie.Document.getElementById("reg_no").Value = TextBox1.Value[/vba] where i get the error "Object variable or With variable block not set"
But when i click debug then hit resume it works fine and puts the value from TextBox1 into the relevant field on the webpage...odd

The next problem is on
[vba]result = ie.Document.getElementsByTagName("car_info")[/vba] with the error "Type mismatch"

I really dont understand the first problem but the second one ive tried making resulkt a String and an Object which just throw out different errors.

Im probably doing something obviously wrong. Can anyone point it out please?

[CodeNinja]

sBrooky,
First, what you are trying to do is not an easy task, so good for you! I too have struggled with getting data from the WWW.
Second, what references are you including?
Third, is it even navigating to the site you want (opening browser)? I don't like the line:
ie.navigate "http ://ww w.mycarcheck.com/"

The spaces might be messing with your connection to the site... Shouldn't it be ie.navigate "http://www.mycarcheck.com/" ???

[SBrooky]

Thanks! I realized this too quickly!
References include HTML Objects and internet controls.

Yeah it all works fine apart from the 2 problems i pointed out..I had to put the spaces in the URL to be able to submit this forum post =)

[CodeNinja]

Well, sadly, I am having some issues of my own right now... I cannot find my correct references and darnit, I never put a note of what they should have been and I cannot find Internet Controls so I cannot test your code... I can only help by showing you what I did... Sorry this is abandoned code from a while ago... it worked, but we went another way... I was able to open the site and get past the password etc (I blocked that out as you dont need to know my passwords) and then I believe we used a sql query to get data from the tables specifically...

Hope this helps... and good luck... If I can find the correct references, I will try to solve the problem for you... I just remember it was a total bear.

[VBA]
Sub contactWebsite()
Dim IE As InternetExplorer
Dim RegEx As RegExp, RegMatch As MatchCollection
Dim str As String
Dim obj As Object
Dim objCol As Object
Set IE = New InternetExplorer
Set RegEx = New RegExp
'Do Until IE.ReadyState = READYSTATE_COMPLETE
' DoEvents
'Loop
With IE
.Navigate "https://qa.marriott.com/WebApp/Common/SfLoginMenu.aspx?ReturnUrl=%2fWebApp%2fDefault.aspx"
.Visible = True
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop

.Document.all.Item("ctl00$mMainContentPlaceHolder$UsernameTextBox").Value = "******"
.Document.all.Item("ctl00$mMainContentPlaceHolder$PasswordTextBox").Value = "******"
.Document.all.Item("ctl00$mMainContentPlaceHolder$LoginMenuIconLogin").Clic k

Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop

.Navigate "https://qa.marriott.com/WebApp/MasterPages/IFrameContentPage.aspx?url=..%2fPlugins%2fEdrsPlugin%2fSfEdrsRedirector.asp x%3fevt%3d3010%26src%3dMain.aspx.3010"

End With



End Sub
[/VBA]

[SBrooky]

Sorry I meant Microsoft internet controls.
How would you go about using sql queries in vba to get the data out?

[CodeNinja]

SBrooky,
Sorry, I can't seem to find that portion of the code, and it looks like my current version of excel doesnt have the addin I would need to play around with it... I think I remember it was something like ie.execute(sql query), but I am not 100% sure... I remember the problem was locating the name of the table because I had to read the javascript on the page to figure out all the details...

I wish I could be more help. Hopefully someone smarter than me will come along and give you a hand.

Good luck.

[SBrooky]

Managed to just pull the whole innertext of the site and stick ti in a textbox which gives the same result just doesnt look as fancy.

Im now stuck on the simple task of clicking the Next button! Which is an image:

<ul class="buttonnavigationrightcol">                         <li>                             <div class="masterassoc">                                 <a href="/EvlPortalApp/app/enquiry?execution=e1s1&amp;_eventId=next" title="Next">                                     <img src="/EvlPortalApp/framework/images/en/buttons/next.gif" master="true" alt="Next" hsrc="/EvlPortalApp/framework/images/en/buttons/next_rollover.gif" /></a>                             </div>                         </li>                     </ul>

this doesnt cut the cheese at the moment:
[VBA]ie1.document.all("buttonnavigationrightcol").Click[/VBA]

And there is no ID for this button. Anything I dont know that anyone else might on how to click this?