+ Reply to Thread
Results 1 to 19 of 19

Coding to insert a message in excel as opposed to a error occurring

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Coding to insert a message in excel as opposed to a error occurring

    Hello,

    I have two bits of code which do a simple scrape of internet websites. In both of them, if no match is found then there is an error message:

    Run-time error '91':

    Object variable or With block variable not set


    And when I press 'Debug' these rows are highlighted, as shown below:

    Sub Demo()
    Dim URL As String
    Dim ieDoc As Object
    Dim cel As Range

    For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
    URL = cel.Value
    With CreateObject("InternetExplorer.Application")
    .Visible = False
    .Navigate URL

    Do Until .ReadyState = 4: DoEvents: Loop

    Set ieDoc = .Document
    cel.Offset(, 1).Value = ieDoc.getElementsByClassName("_gll")(0).getElementsByTagName("a")(0).href
    .Quit
    End With
    Next cel

    End Sub


    Sub DemoMutual2()
    Dim URL As String
    Dim ieDoc As Object, dObj As Object
    Dim cel As Range

    URL = Range("G2").Value
    With CreateObject("InternetExplorer.Application")
    .Visible = False
    .Navigate URL

    Do Until .ReadyState = 4: DoEvents: Loop

    Set ieDoc = .Document
    Set dObj = ieDoc.getElementsByClassName("_50f3")
    [G4].Value = Split(ieDoc.getElementsByClassName("_50f3")(0).innerText, " ")(0)
    For i = 0 To dObj.Length - 1
    If InStr(1, dObj(i).getElementsByTagName("a")(0).innerText, "since") Then
    [G5].Value = Trim(Split(dObj(i).getElementsByTagName("a")(0).innerText, "since")(1))
    End If
    Next
    .Quit
    End With
    Set ieDoc = Nothing
    Set dObj = Nothing
    End Sub


    Obviously the reason for the errors is because this part of the webpage is not there to be scraped, so I am wondering if my code could be amended slightly so that instead of there being an error, there is something displayed in Excel - such as 'No Link' for example.

    The relevant cells for these error messages would be C2 for the first code and G4 for the second.


    Any help much appreciated,

    Cheers

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hallo daar

    In future please view the forum rules before posting. Your post does not comply with rule #3.
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

    Can try this
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Coding to insert a message in excel as opposed to a error occurring

    Look up code tags as required within forum rule

    [ code ] without spaces

    "your code"

    [ /code ] without spaces

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Apologies:

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi guys,

    With the new coding posted by AB33 I get the following error:

    Run-time error '424':

    Object Required

    The row beginning "Set Elem =......href" is then highlighted yellow in the below code:

    Please Login or Register  to view this content.
    Any help appreciated.

    Many thanks,

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Do you get an error if you remove the line? If not, then there is no href.
    Is this an internal site? Could you post the URL or the source view?

  8. #8
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi AB33,

    If I remove the line I do not get an error. If the HREF is there with my coding for 'Demo' then obviously it does what I need it to do. If there HREF is not there when I run 'Demo' then I get the Run-time error '91', which I do not want. I want the relevant cells to be populated with 'No Link' when the HREF is not there.

    Then for my 'DemoMutual2' coding, it is the same as above.

    So basically rather than a pop up error, I want a cell to be populated with 'No Link' if the HREF is not there. If the HREF is not there, then there is no link.

    Many thanks,

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    I understand what you are after, but struggling to find out why the code does not jump into the else statement if the HREF is blank.
    It seems the code does not like
    Set Elem = ieDoc.getElementsByClassName("_gll")(0).getElementsByTagName("a")(0).href
    to return an object of nothing.

    I would not suggest you could try "On error resume statement" before I test the code, but the URL might be private. Could you post the URL?

  10. #10
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi AB33,

    The URL for 'Demo' code is the following 2:

    https://www.facebook.com/search/peop...%40hotmail.com - Does not return error
    https://www.facebook.com/search/peop...s250%40gmx.com - Does return error as HREF is blank

    and for 'DemoMutual2' code is the following 2:

    https://www.facebook.com/504770759?a...on.9440&ref=nf - Does not return any error

    https://www.facebook.com/504770759?a...7702014&ref=nf - Does return an error as the HREF is blank


    The profiles and email addresses are mine.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Sorry! I do not have a Facebook account.

    I am also not surprised why you are getting an error.
    As far as I know, you can not supply the username and password in the URL. If a site requires log-in details and you are using IE, you can not append the password and usernames in the URL. You code should look like

    IE.document.getElementsByName("tbEmail")(0).Value = "[email protected]"
    IE.document.getElementsByName("tbPassword")(0).Value = "pass123"
    IE.document.getElementsByName("btnSubmit")(0).Click.
    I would be surprised if Facebook could allow to use a "GET" method to append your credential in the URL. This is not a normal practice as any one can see your log-in details from the URL. You can append your credential in the body of the URL if you use the "Post" method, but you need to use XMLHTTP object, not IE.
    So, the code appears to be doing something, but as you have not opened the page, the code errors.
    I hope I have not confused you with jargons

  12. #12
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi AB33,

    It isnt to log in, it is to obtain data from a Facebook profile (mine) for statistical analysis.

    The email addresses are linked to the relevant profiles, so they are searched and then the HREF is taken from my profiles when it is visible.

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Okay!, I did a quick look at the page. There is no class name called ("_gll").

    If you split the code in to parts, like

    Set Elem = ieDoc.getElementsByClassName("_gll")(0)

    The code does not error and jumps into the else statement as there is no class name called ("_gll

  14. #14
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi AB33,

    Thanks for that. What should my full code look like now then?

    Many thanks.

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    You first test it for object and if it does not exist, it is the else statement, but what I am not sure is if the object exit and returns true, will this statement errors as well?

    getElementsByTagName("a")(0).href

    I do not know, but try it.


    Please Login or Register  to view this content.
    When you search for info, you first try to do it manually; that is, find the item you are searching, then right click to view the "Inspect Element" to find out the tag name. You then use the that tag name in your code.

  16. #16
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Hi AB33,

    I tried the above code and it inserts a message into the cell straight away and doesnt actually run the rest of the code, so the original code doesnt appear to be used.

  17. #17
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Could you please run the code using F8 to step over each line? I suspect the code is jumping in to the else statement as your class or tag names could not be found. As I said, you are doing a fizzy search and the tag names for each search might not be the same.

  18. #18
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Coding to insert a message in excel as opposed to a error occurring

    Ah ok I have got you. Thank you very much, I will try that.

    In the mean time I have added in 'On Error Resume Next' which seems to be doing the trick.

    Many thanks for your help. I will go through your code and see how that fairs.

  19. #19
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Coding to insert a message in excel as opposed to a error occurring

    Okay! On error resume next just hides the error- It skips the line with the error and jumps in to next line.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 08-25-2015, 01:46 PM
  2. [SOLVED] Why do I get syntax error message on this INSERT INTO ?
    By mc84excel in forum Access Tables & Databases
    Replies: 3
    Last Post: 03-23-2015, 01:21 AM
  3. Excel 2010 - Unable to Insert Column - Error message not helping
    By tplenert in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-25-2014, 05:36 AM
  4. VBA Insert Fails with No Error Message
    By yawnzzzz in forum Access Tables & Databases
    Replies: 8
    Last Post: 05-20-2013, 04:25 PM
  5. [SOLVED] VBA Coding upon error message
    By knighty2002uk in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2013, 04:03 AM
  6. [SOLVED] Getting error message when I try to insert rows.
    By jkc1 in forum Excel General
    Replies: 1
    Last Post: 04-13-2006, 02:19 PM
  7. Error message when trying to insert a column
    By Kelly in forum Excel General
    Replies: 8
    Last Post: 09-12-2005, 03:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1