+ Reply to Thread
Results 1 to 13 of 13

Web Query - Search for Specific Text and paste values

  1. #1
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    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 bob.joe@company1.com

    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.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Web Query - Search for Specific Text and paste values

    Can I see the link?
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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.

  4. #4
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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.

  5. #5
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Web Query - Search for Specific Text and paste values

    I am sorry, I just overlooked the thread by error.. And it seems multiple times.. I'll take a look, just give me some time..

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Web Query - Search for Specific Text and paste values

    Can you paste the working link which will open at my end (Not with cell references/function).. I'd like to take a look at what the link returns

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

  8. #8
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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: 
    <div class="courseComplete">
    	<label>
    		<span>Your course is  
    			<span class="scalerClass">100</span>
    		% complete</span>
    	</label>
    		<div class="barComplete">
    			<div class="barCompleteScaler" id="scalerBar"></div>
    		</div>
    	</div>   
    </div>

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Web Query - Search for Specific Text and paste values

    What does this part of the Formula evaluate to?

    "&J3&"%5C"&LEFT(L3,(FIND("@",L3,1)-1)

    The accountname I am talking about.

    Also, paste the whole code that has the accountname visible in it

  10. #10
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    "&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 Bob.Joe@company1.com 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):

    Please Login or Register  to view this content.
    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

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,870

    Re: Web Query - Search for Specific Text and paste values

    You will need a For Loop that will loop through rows.

    Something like this (Code is untested)

    Assuming Cells(Counter, "J") generates the link

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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.

  13. #13
    Registered User
    Join Date
    09-08-2015
    Location
    450
    MS-Off Ver
    2010
    Posts
    19

    Re: Web Query - Search for Specific Text and paste values

    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.

+ 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. To search a specific value and paste its corresponding data
    By archies.gall in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2014, 09:40 AM
  2. [SOLVED] Search columns in specific sheets and paste values on sheet1
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 11:19 AM
  3. Search Query Filtering VBA Needed on Specific Excel
    By cnyoon2 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-02-2013, 01:01 PM
  4. Search Query Filtering VBA Needed on Specific Excel
    By cnyoon2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-29-2013, 09:24 PM
  5. query SQL: search in database reccord that contains specific letter
    By sebastienkanj in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 06-11-2013, 08:15 AM
  6. [SOLVED] search for text in colB, starting with next row search for 1st occurrence of specific #
    By roothog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2012, 10:39 PM
  7. unique values & specific text query
    By verbatim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2010, 06:13 AM

Tags for this Thread

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