VB script, Macro Help!!!. Build a Hyperlink Generator.

[manoj18071965]

Hello,

I have a list of Patent numbers in an excel sheet. I just wanted to know if following is feasible in excel:

1. I need a script so that each of the respective patent number (there 'web home page address' e.g. Patent number: 4456785 http://www.google.com/patents?id=jzs...ed=0CC8Q6AEwAA) can be automatically pasted on next column of each patent number.

2. Please note Patent Numbers available on excel sheet does not contain hyperlinks.

3. I am using MS Office 2007

Hoping for a positive response from you all.

Thanks,
Manoj Kumar

 

[jkpieterse]

The URL google gives me is:

http://www.google.com/search?q=4456785&tbm=pts&tbo=1&hl=en

So you should be able to generate links like so (assume patent # is in cell A1):

=HYPERLINK("http://www.google.com/search?q=" & A1 & "&tbm=pts&tbo=1&hl=en")

 

[manoj18071965]

Hi Jan,

Thank you very much!!!

It really helped me a lot.

Can you please help me on below case also:

On the basis of Patent Number I want to download its PDF file from website.http://worldwide.espacenet.com

Example: If I search Patent number US7157984 on http://gb.espacenet.com/search97cgi/s97_cgi.exe?Action=FormGen&Template=gb/en/number.hts
I reach to following address http://worldwide.espacenet.com/sear...&F=0&CY=gb&LG=en&&PN=US7157984+&Submit=SEARCH
Now if I open the provided link on this page (1. MEMS-based, computer systems, clock generation and oscillator circuits and LC-tank apparatus for use therein)
And then press 'Original document' link (Provided on the middle left side)
I reach to Original document page
Here If I press Download link, I reach to Espacenet Verification window.


I only want to reach till here only and then after I can further manually download the pdf file.

Thanks,
Manoj Kumar

 

[jkpieterse]

If you right-click the "Original document" link and select copy shortcut, you can then paste that shortcut into Excel.
Maybe you can find out how to build the HYPERLINK formula using that shortcut and the example I provided earlier?

 

[manoj18071965]

Jay,

By right clicking on Original Document no option is appearing like copy short cut.

 

[jkpieterse]

The shortcut I could copy using my Internet Explorer right-click menu is:

http://worldwide.espacenet.com/publicationDetails/originalDocument?CC=US&NR=2006049885A1&KC=A1&FT=D&date=20060309&DB=EPODOC&locale=en_gb

 

[manoj18071965]

Thanks for the link. But I am not able to get what I required in my request.

 

[jkpieterse]

I expect you would need this:

Suppose cell A1 contains:

US2006049885

Then if I understand the URL correctly, this should give you a direct link to the download:

=HYPERLINK("http://worldwide.espacenet.com/publicationDetails/originalDocument?CC="&LEFT(A1,2)&"&NR="&MID(A1,3,LEN(A1))&"A1&KC=A1&FT=D&date=20060309&DB=EPODOC&locale=en_gb")

 

[manoj18071965]

Yes your understanding is right. But I need to apply this on a list

example:
US7358826
US7365614
US7132874
US7786812
US7248124
US7456699

 

[jkpieterse]

You can simply enter the list in cells A1, A2, A3, ... and then copy that formula down next to those items and click the links.