Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Hyperlink and copy data (XP)

    I'm looking for a way to open a hyperlink (IE Explorer) from within Excel and copy text that is in a specific location in the Explorer window, and paste it back into the Excel spreadsheet.

    For example, Cell reference B2 is a hyperlink to a Web page. I need the macro to open the web page and copy the 15th character (the page is all text) and paste it in Cell C2.

    It needs to repeat down Column B until it gets to the last line.

    Is this possible?

    Any help is much appreciated (as usual).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Hyperlink and copy data (XP)

    This could be tricky - it's hard to know that the 15th character you see is the 15th character on the web page (in whatever definition). You could try this, but I have no way of knowing whether it does what you want.

    Sub GetCharacters()
    Dim ie As Object
    Dim r As Long
    Dim n As Long
    On Error GoTo ErrHandler

    Application.ScreenUpdating = False
    n = Range("B65536").End(xlUp).Row
    Set ie = CreateObject("InternetExplorer.Application")
    For r = 2 To n
    ie.Navigate Range("B" & r).Hyperlinks(1).Address
    Do While Not ie.ReadyState = 4
    DoEvents
    Loop
    Range("C" & r) = Mid(ie.Document.Body.InnerText, 15, 1)
    Next r

    ExitHandler:
    On Error Resume Next
    ie.Quit
    Set ie = Nothing
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Hyperlink and copy data (XP)

    That works fantastically!

    I found out that I will sometimes need two characters pulled. (I'm pulling numbers, so anything above 9 only gives me the first number).

    What do I need to change to get it to pull the 76th and 77th character? (I found out it's the 76th character that I need.)

    I also found out that while this runs, you can't do anything else or it will give an error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Hyperlink and copy data (XP)

    In my code

    Mid(ie.Document.Body.InnerText, 15, 1)

    15 = start position of string to extract.
    1 = length of string to extract.

    So to get the 76th and 77th character, you'd use

    Mid(ie.Document.Body.InnerText, 76, 2)

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Hyperlink and copy data (XP)

    That's great. Thanks a bunch.

    An error I receive from time to time is "Object variable or With block variable not set".

    Is that just a network time-out error? It will return a few results and then produce that error. There doesn't seem to be a pattern as it doesn't stop on the same row each time, and other times it completes. I'm using the same data set and just deleting the results and starting over.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Hyperlink and copy data (XP)

    Is suspect it is a time-out problem, but I'm not sure. You could try this version:

    Sub GetCharacters()
    Dim ie As Object
    Dim r As Long
    Dim n As Long
    On Error GoTo ErrHandler

    Application.ScreenUpdating = False
    n = Range("B65536").End(xlUp).Row
    Set ie = CreateObject("InternetExplorer.Application")
    For r = 2 To n
    ie.Navigate Range("B" & r).Hyperlinks(1).Address
    Do While Not ie.ReadyState = 4
    DoEvents
    Loop
    Range("C" & r) = Mid(ie.Document.Body.InnerText, 15, 1)
    NextCell:
    Next r

    ExitHandler:
    On Error Resume Next
    ie.Quit
    Set ie = Nothing
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume NextCell
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Hyperlink and copy data (XP)

    I still receive the error, but it keeps running after pressing the <OK> button.

    I appreciate the information and assistance you provide here.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Hyperlink and copy data (XP)

    I'm afraid I don't know enough about the InternetExplorer object to know what causes the error.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Hyperlink and copy data (XP)

    That is fine. This will save hours of manual lookup even with the error.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Hyperlink and copy data (XP)

    If you just want to ignore the error and continue without the message, then remove the line below from Hans' code:

    <code>
    MsgBox Err.Description, vbExclamation
    </code>

    BTW, the most likely reason for getting that message would be that the cell being processed does not contain a hyperlink.
    Legare Coleman

Posting Permissions

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