PDA

View Full Version : VBA Problem taking cell data from IE



SBrooky
06-15-2012, 05:27 AM
Im trying to pull all the text from a cell on a webpage. Here's my code so far:

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
The first problems occurs on
ie.Document.getElementById("reg_no").Value = TextBox1.Value 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
result = ie.Document.getElementsByTagName("car_info") 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
06-15-2012, 06:54 AM
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
06-15-2012, 06:58 AM
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
06-15-2012, 07:41 AM
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.


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").Click

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

SBrooky
06-15-2012, 08:14 AM
Sorry I meant Microsoft internet controls.
How would you go about using sql queries in vba to get the data out?

CodeNinja
06-15-2012, 12:11 PM
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
06-16-2012, 06:16 AM
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:
ie1.document.all("buttonnavigationrightcol").Click

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