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???
when it's in debugging mode it highlights this line:
NAME=searchSelectionForm METHOD=POST
and i just want to say THANK YOU SOOO MUCH, brad for your
help.. i would never have come up with anything like
that..
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
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???
when it's in debugging mode it highlights this line:
NAME=searchSelectionForm METHOD=POST
and i just want to say THANK YOU SOOO MUCH, brad for your
help.. i would never have come up with anything like
that..
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
..