object doesn't support this property or method (runtime error 438)

**studentblob** · 01-07-2015, 09:03 PM

Hi Everyone,

After looking for solutions I still can't fix this error. All the references are loaded like 'microsoft internet explorer controls' and 'html object library'.

The error is in this code:

PHP Code:


quote = Trim(doc.getElementsByTagName("h2").innerText)

The whole code is here:

PHP Code:


Private Sub Worksheet_Change(ByVal Target As Range) 
 
Dim i As InternetExplorer, doc As HTMLDocument, rng As Range, ticker As String, quote As String 
 
Set rng = Sheets("Sheet1").Range("A1") 
ticker = rng.Value 
 
 
 
 
Set ie = CreateObject("InternetExplorer.Application") 
 
 
If Target.Row = rng.Row And Target.Column = rng.Column Then 
 
       ie.navigate "http://www.soccerpunter.com/soccer-statistics/Netherlands/Eredivisie-2014-2015/head_to_head_statistics/all/1515_AFC_Ajax/1516_Alkmaar_Zaanstreek" 
       Do 
       DoEvents 
        
               Loop Until ie.readyState = READYSTATE_COMPLETE 
               Set doc = ie.document 
               On Error GoTo handler 
               quote = Trim(doc.getElementsByTagName("h2").innerText) 
                On Error GoTo 0 
                
              Cells(1, 2).Value = quote 
               
              End If 
               
 
 
 
Exit Sub 
 
handler: 
MsgBox "fout melding" 
ie.Application.Quit 
 
End Sub

Thanks for any help

**studentblob** · 01-08-2015, 08:26 AM

anyone ? because I really have no clue how I can solve this...

**AB33** · 01-08-2015, 08:55 AM

This will give you a good start.
Change
doc As HTMLDocument
INTO

doc As Object

Please Login or Register  to view this content.

You either need to loop through the H2 tag name or find out the node number. You have many H2s in the page.
It would be easier to get the data from a table

**studentblob** · 01-08-2015, 12:39 PM

That worked ! Thanks for your help. One thing I dont understand is why the data is displayed in A2 t/m A5 instead of B1 (cells (1,2)) and underlying cells.

Sorry wasn't looking to good you changed that part, just found out

**AB33** · 01-08-2015, 12:52 PM

Use these lines

Please Login or Register  to view this content.

**studentblob** · 01-09-2015, 10:32 AM

Hi,

After changing the link it gives another error. So after pasting various codes and changing, finding solutions and stuff I am not able to tackle this error message. It's unfortunate because figured out how to get the elements but for some reason I still get an error. Error is ; 'Object variable with block variable not set.' Is it because I removed the loop ? If so then I would like to have a code were I can loop if I need to but also that i can remove the loop if I don't need it.

error line

PHP Code:


Set doc = ie.document.getElementById("h2hFactSheet").getElementsByTagName("tbody")(1).getElementsByTagName("tr")(20).getElementsByTagName("td")(0).innerText

full code;

PHP Code:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As InternetExplorer, doc As Object, rng As Range, ticker As String, quote As String

Set rng = Sheets("Sheet1").Range("A1")
ticker = rng.Value




Set ie = CreateObject("InternetExplorer.Application")


If Target.Row = rng.Row And Target.Column = rng.Column Then

       ie.navigate "http://www.soccerpunter.com/soccer-statistics/Netherlands/Eredivisie-2014-2015/head_to_head_statistics/all/1515_AFC_Ajax/1516_Alkmaar_Zaanstreek"
       Do
       DoEvents
       
             Loop Until ie.readyState = READYSTATE_COMPLETE
             
                Set doc = ie.document.getElementById("h2hFactSheet").getElementsByTagName("tbody")(1).getElementsByTagName("tr")(20).getElementsByTagName("td")(0).innerText
               
   
                 Range("B" & Rows.Count).End(xlUp).Offset(1) = elem.innerText
                
              
              End If
            
        

Exit Sub

handler:
MsgBox "fout melding"
ie.Application.Quit

End Sub

**AB33** · 01-09-2015, 10:43 AM

It looks like, you are trying to get data from a table, but I do not know which lines. It would be easier to loop through a table. You need to tell us what information are you trying to get from the site?
You can actually import the data using the browse link in to excel and then work adjust it in excel.

Please Login or Register  to view this content.

**studentblob** · 01-09-2015, 01:19 PM

True but I just need specific information and I already have a table query from which I am adding data. let's say I need 2 values from this website;

http://www.soccerpunter.com/soccer-s...aar_Zaanstreek

The first value is in the second last table in the left section, second last value you see the name "Christian Eriksen" Thats the value I tried to retrieve with the code beneath.

PHP Code:


  Set doc = ie.document.getElementById("h2hFactSheet").getElementsByTagName("tbody")(1).getElementsByTagName("tr")(20).getElementsByTagName("td")(0).innerText

The other value is from the last table, the standings table where you can see each team. Depending on what team I have in my webquery I search for that one in the list of teams. So I was thinking of looping trough the team elements and retrieving the value when it is equal to the team name in my webquery. So let's say I want to have the goals for and against ratio of Ajax (at top of table) which is +52 and that value I want to load into my excel sheet.

If I can retrieve the first value I should be able to get the other value from the last table as well but for some reason the code pasted above gives error 'Object variable with block variable not set.'

**AB33** · 01-09-2015, 01:46 PM

Instead of doing all in one line, separate this line first in to many lines

Please Login or Register  to view this content.

Please Login or Register  to view this content.

gives you an error as there is ID tag name called h2hFactSheet.

It is not clear to me what text you want to extract.

**studentblob** · 01-09-2015, 03:38 PM

I'm sorry but I don't know how to separate this code as I got it from an example and just adjusted the () values. The code from the html page is posted below with the value C.Eriksen that I want to load in my sheet all the way at the bottom. In my code I refer to the closest ID with tag name which is 'h2hfactsheet' from there on I used get element by tag name to search within that table. However as you said I need to break my code line into smaller lines but I don't know how to make that work.

PHP Code:


<script type="text/javascript"></script> 
 
<div id="wrapper"> 
 
    <div id="wp_inn"> 
        <a id="soccer-picks" name="soccer-picks"></a> 
        <div id="header"></div> 
        <div id="content_box"> 
            <table id="content" cellspacing="0" cellpadding="0" border="0"> 
                <!-- 
 
                DWLayoutTable 
 
                --> 
                <tbody> 
                    <tr> 
                        <td id="center_column" valign="top"> 
                            <table width="100%" cellspacing="0" cellpadding="0" border="0"> 
                                <tbody> 
                                    <tr> 
                                        <td> 
                                            <table width="640" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF"> 
                                                <tbody> 
                                                    <tr></tr> 
                                                    <tr></tr> 
                                                    <tr> 
                                                        <td> 
                                                            <p></p> 
                                                            <p></p> 
                                                            <div id="h2hSelect"></div> 
                                                            <script></script> 
                                                            <p></p> 
                                                            <p></p> 
                                                            <p></p> 
                                                            <div class="oldSeasonH2hWarning"></div> 
                                                            <div class="oldSeasonH2hWarningPopup"></div> 
                                                            <script src="/script/jquery.bpopup.min.js?ver=1392848372"></script> 
                                                            <script src="/script/oldSeasonH2hWarning.js?ver=1392929478"></script> 
                                                            <table id="h2hHead" width="100%"></table> 
                                                            <p></p> 
                                                            <!-- 
 
                                                             STATS TABLE  
 
                                                            --> 
                                                            <table id="h2hSimpleStats"></table> 
                                                            <!-- 
 
                                                             END STATS TABLE  
 
                                                            --> 
                                                            <p></p> 
                                                            <h2></h2> 
                                                            <p></p> 
                                                            <p></p> 
                                                            <div id="h2hPie" style="position: relative;"></div> 
                                                            <script></script> 
                                                            <script type="text/javascript" src="https://www.google.com/uds/?file=visualization&v=1&packages=corechart"></script> 
                                                            <link rel="stylesheet" type="text/css" href="https://www.google.com/uds/api/visualization/1.0/ff67c3772da186a412ae3231c32409e9/ui+nl.css"></link> 
                                                            <script type="text/javascript" src="https://www.google.com/uds/api/visualization/1.0/ff67c3772da…2ae3231c32409e9/format+nl,default+nl,ui+nl,corechart+nl.I.js"></script> 
                                                            <table id="h2hSum" class="competitionRanking tablesorter"></table> 
                                                            <h2></h2> 
                                                            <p></p> 
                                                            <p></p> 
                                                            <table id="h2hMatches" class="competitionRanking tablesorter"></table> 
                                                            <script></script> 
                                                            <h2></h2> 
                                                            <p></p> 
                                                            <div id="perfChart" style="position: relative;"></div> 
                                                            <script type="text/javascript"></script> 
                                                            <p></p> 
                                                            <h2></h2> 
                                                            <table id="h2hFactSheet" class="competitionRanking tablesorter"> 
                                                                <thead></thead> 
                                                                <thead></thead> 
                                                                <tbody> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"></tr> 
                                                                    <tr class="even"></tr> 
                                                                    <tr class="odd"> 
                                                                        <td> 
                                                                            <a href="/players/95871-Christian--Dannemann-Eriksen"> 
 
                                                                                C. Eriksen

**studentblob** · 01-10-2015, 09:18 AM

Anyone that can help ?

object doesn't support this property or method (runtime error 438)

LinkBack

Thread Tools

Rate This Thread

Display

object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Re: object doesn't support this property or method (runtime error 438)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Runtime Error 438 - Object doesn't support the property or method

Runtime Error 438 - Object doesnt support this property or method

Object Doesn't support this property or method Error

error '438', Object doesn't support this property or method.

Object doesn't support this property or method (Error 438)

Bookmarks

Bookmarks

Posting Permissions