linking to a website with javascript

H

hk123j

hi all.

[disclaimer] i'm relatively new to excel but i'm working
on a project that i need to link from an excel worksheet
to a website that will give me search results for
patents.

from this website:
http://pair.uspto.gov/cgi-bin/final/home.pl
i normally have to change the drop down menu
to "Publication Number" and manually type in a patent
number like "2003-0041856 A1" and press submit. this page
uses javascript to get the search results which does not
give me a direct page to link to from excel.

having to look through hundred of patents like this, i'd
like to create a function/script/link/etc that will give
me the search page when i click on the cell but i can't
figure out a way to directly bring up the results of the
search engine. any help would be much appreciated!!
thanks!
 
B

Brad

I played around with stuff to see if I could get this to
work. Here's my end result, and it works for me.

First you need to create a reference to the scripting
library. In your Development Environment click on
Tools>References>Microsoft Scripting Runtime.

Then create a standard module and put in this code:

Sub OpenForm()
Dim vOutput(1 To 10) As Variant
Dim strPublicationNumber As String
strPublicationNumber = ActiveCell.Value
vOutput(1) = "<html><body><FORM
NAME=searchSelectionForm METHOD=POST
ACTION=http://pair.uspto.gov/cgi-bin/final/pairsearch.pl>"
vOutput(2) = "<INPUT TYPE=hidden
NAME=publicationnumber SIZE=15 value=" & Chr(34) &
strPublicationNumber & Chr(34) & "><INPUT TYPE=hidden
NAME=patentnumber SIZE=15>"
vOutput(3) = "<INPUT TYPE=hidden
NAME=applicationnumber SIZE=15>"
vOutput(4) = "<INPUT TYPE=hidden NAME=username VALUE=>"
vOutput(5) = "<INPUT TYPE=hidden NAME=USERCODE
VALUE=0>"
vOutput(6) = "<INPUT TYPE=hidden NAME=searchtype
VALUE=publication>"
vOutput(7) = "<INPUT TYPE=HIDDEN NAME=submission
VALUE=PublicationSearch>"
vOutput(8) = "<INPUT TYPE=hidden NAME=sortby VALUE=>"
vOutput(9) = "</form><script
language=VBScript>document.searchSelectionForm.submit()"
vOutput(10) = "</script></body></html>"
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

If fso.FileExists("c:\submit.htm") Then
fso.DeleteFile "c:\submit.htm", True
End If

Dim txtFile As Scripting.File
Dim fsoTextStream As Scripting.TextStream

Set fsoTextStream = fso.OpenTextFile("c:\submit.htm",
ForWriting, True)
For i = 1 To 10
fsoTextStream.WriteLine vOutput(i)
Next
fsoTextStream.Close

Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.Delete
Next

Set h = ActiveSheet.Hyperlinks.Add
(ActiveCell, "c:\submit.htm")
h.Follow True
h.Delete
End Sub

You can assign an accelerator key to the macro also, like
ctrl-P to make it open a new window.

Anyway, as you can see from the code, what you do is click
on a publication number, then hit ctrl-P or whatever your
shortcut is. It should open the results of the search
engine in a new internet explorer window. You might be
able to use that new data source for a querytable, also.

Let me know how it goes, I had a lot of fun making this.

-Brad
-----Original Message-----
hi all.

[disclaimer] i'm relatively new to excel but i'm working
on a project that i need to link from an excel worksheet
to a website that will give me search results for
patents.

from this website:
http://pair.uspto.gov/cgi-bin/final/home.pl
i normally have to change the drop down menu
to "Publication Number" and manually type in a patent
number like "2003-0041856 A1" and press submit. this page
uses javascript to get the search results which does not
give me a direct page to link to from excel.

having to look through hundred of patents like this, i'd
like to create a function/script/link/etc that will give
me the search page when i click on the cell but i can't
figure out a way to directly bring up the results of the
search engine. any help would be much appreciated!!
thanks!
.
Click to expand...
 
H

hk123j

i'm really new to all this programming stuff and i tried
plugging in the code as a macro into the excel
spreadsheet i'm working on but when i try running the
macro it says that there's a syntax error to the code
that you wrote for me. do i need to do something else to
make the macro work???

and i just want to say THANK YOU SOOO MUCH, brad for your
help.. i would never have come up with anything like
that..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Brad, linking website with javascript 2
submitting a cell into a search box on a website 3
Activating javascript with excel 6
Excel's Refreshable Web Query & Javascript... 2
Pulling Data from Web Automatically 4
using vb to help with a complex web query 1
import search result from a website 2
Download data from secure website into new Worksheet 12

Top