Open Hypelinks from column and Copy all text on the next column

GeoKoro13

New Member
Joined
Nov 24, 2016
Messages
27
Hi there,

First of all, I have no experience in coding so whatever I have done is by searching online and asking. I have a file with multipe hypelinks. Each hypelink opens a database page on which I would like to check whether or not some keywords are present and report that back to excel. See below.

1.PNG


So, for instance when I open K2 link I want to check whether the keywords on the headings M1:T1 are present or not.
So, the way I was thinking to do (although it doesn't sound elegant) is to copy all the text from each page (since I'm not sure how to copy specific text) and paste it into to L2:L (Description) and then the formulae below the keywords will search.

So far I managed to find a code (see below) that open all the hyperlinks into Chrome.

VBA Code:
Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("K2:K" & .Cells(.Rows.Count, "K").End(xlUp).Row)
    End With
    For Each Cell In Rng
        ThisWorkbook.FollowHyperlink Cell.Value
    Next Cell
End Sub


It works fine but I'm getting the following error:

error.PNG


I have activated the references the Microsoft HTML Library and Internet Controls references.
Despite that issue, what I was thinking is to somehow between the operation of opening each hyperlink to copy all the texts from each page to the next column.

Do you have any idea/suggestion?

Thanks in advance,
George.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

GeoKoro13

New Member
Joined
Nov 24, 2016
Messages
27
So, I've done the following so far.
VBA Code:
Sub Test1()

Dim x As Integer

For x = 2 To 3


Shell "explorer.exe " & Cells(x, 12).Text
Application.Wait (Now + TimeValue("0:00:04"))

            Application.SendKeys ("^u")
Application.Wait (Now + TimeValue("0:00:01"))
            Application.SendKeys ("^a")
Application.Wait (Now + TimeValue("0:00:01"))
            Application.SendKeys ("^c")
Application.Wait (Now + TimeValue("0:00:01"))
            Application.SendKeys ("%{TAB}")
Application.Wait (Now + TimeValue("0:00:01"))
           Cells(x, 13).Select
Application.Wait (Now + TimeValue("0:00:05"))
            Application.SendKeys ("{F2}")
Application.Wait (Now + TimeValue("0:00:05"))
            Application.SendKeys ("^v")
Application.Wait (Now + TimeValue("0:00:05"))
            Application.SendKeys ("^{ENTER}")
Application.Wait (Now + TimeValue("0:00:05"))

Next x


End Sub

Since I'm getting no responses, I tried with a very messy way by using the SendKeys. All working ok when I use on Cell but when I try to loop, for some reason it only pastes the HTML to the very last cell of the loop (no matter if I increase the waiting time).

Can you please explain me why or help with any suggestion?

George.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,345
Messages
5,624,127
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top