Web Query - Search for Specific Text and paste values

Hi there, I am unsure how to do this properly, but I've managed to figure out that a Macro is perhaps the best way to go.

I currently have a single sheet that I need to update one column based on a link that has been pre-generated.

Company	First Name	Last Name	Position	Salary Group	Gender	Course	Completed	Country	Link	Email
Company 1	Bob	Joe	HR Manager	2	Male	Excel 101	10%	USA	Link	[email protected]

The link is already generated and when you click on it, goes to the relevant page.

However, this excel has in excess of 500 people in a list and it would be cumbersome for someone to click on each link, search out the % completion, then paste it into the appropriate column. I was wondering if a Macro could automatically do this as and when required.

The rest of the information is pulled from the local Active Directory using another Macro (works like a charm). Only the % complete isn't as that resource is from a website.

Any help would be very much appreciated.

I've tried to use the DATA>GET EXTERNAL DATA>IMPORT FROM WEB function, but to no avail since there are multiple occurrences on one page.

Hi,

Sure:

The link is formed as thus: =HYPERLINK("https://company1.com/Person.aspx?accountname="&J3&"%5C"&LEFT(L3,(FIND("@",L3,1)-1)),C3)

The link takes in the email, removes everything after the '@' symbol, and returns the email username, which happens to be the link address I need.

Hope that helps.

Also, since the network is internal (Intranet), I don't need username/password.

Hi, sorry, didn't reply/ping you.

Tried again and failed. I actually managed to somehow corrupt the file. Luckily I had a backup, but it is still worrying.

I am not sure what the etiquette is about bringing focus back, but I've tried again to complete this -- so I'm posting in hopes to getting some more attention.

From what I understand, the process involves:

1. Opening a site in Internet Explorer
2. Using a search function to find the text in question
3. Copy said text to the clipboard
4. Pasting the results in the relevant cell in Excel
5. Repeat process until there are no more rows

I have found out how to open IE, but I cannot figure out how to 'search for text'. Any help would be much appreciated, or if there is another more efficient way of doing this, then please help!

Thanks.

Hi, you can't open the site. It's internal and I cannot share it outside (rules is rules). I can provide the code snippet of what I need:

HTML Code: 

"&J3&" - This is the continent wherein the account 'resides'
%5C - This is the URL code for a backslash (\)
&LEFT(L3,(FIND("@",L3,1)-1) - This code looks for the contents of L3 (email address) and takes everything before the '@' symbol (essentially the username) and attaches that to the formed URL

So, if the person's email is [email protected] and they're in North America, the link will be:

https://company1.com/Person.aspx?acc...MERICA\Bob.Joe

The reason why I have to do this is that the account names vary quite a lot between various elements of the company. Some use the simple firstname.lastname approach, while others do lastname.firstname, firstname-middleinitial.lastname, etc. There are far too many variables.

The links themselves are working and when you click on them they open a InternetExplorer window and take you straight to the desired page.

This sort of streamlines the process, wherein the user opens each and every person's link, looks up the relevant information, and then pastes it in.


After hours of looking around, I've pieced this code together (based on this code here: http://stackoverflow.com/questions/2...website-data):

Sub extract()
    Dim IE As InternetExplorer
    Dim html As HTMLDocument

    Set IE = New InternetExplorerMedium
    
    IE.Visible = False
    'IE.Navigate2 "https://company1.com/Person.aspx?accountname=AMERICA\Bob.Joe"
    IE.Navigate (Range("J2"))
    
    ' Wait while IE loading
    Do While IE.Busy
        Application.Wait DateAdd("s", 5, Now)
    Loop

    Set html = IE.Document
    Set holdingsClass = html.getElementsByClassName("scalerClass")

    Range("H2").Value = holdingsClass(0).textContent
        
    'Cleanup
    IE.Quit
    Set IE = Nothing
End Sub

This code works if I use a manual address (currently commented out) and does return the right information in the cell I desire (H2), but I can't see to loop it through the list of links and put the corresponding information into the appropriate row.

Like:
J3 -> H3
J4 -> H4
J5 -> H5

Hi,

Yes, Cells in "J" have the link. I execute it, but I'm getting an error:

Run-time error '91':

Object variable or With block variable not set

I do know that I need to reference two libraries here (as per the link above).

1. Microsoft Internet Controls
2. Microsoft HTML Object Library

I found these under: Tools > References, then scrolled till I found the objects in question and ensured both had a tick.

It's weird because I can use the manual instance for each person and it works, but when I try to create a loop it fails. I even increased the number for the Do While loop to see if increasing the time would change. This was suggested in the linked post.

I'm still very much stuck with this. Granted, I've put it on the backburner, but I'd be very happy for any more ideas.