H
hk123j
someone had helped me [thanks brad!] with a problem
getting excel to open up a query from the web but now i'm
trying to figure out how to import the data that i get at
the website into excel and to automatically update every
time i run the macro.
the website is with the US patent office
[http://pair.uspto.gov/cgi-bin/final/home.pl] and it's
for looking up publications and its office actions.
For example I have this publication[queried exactly as
typed here]: 2001-0000616 A1,
and i want to put into excel the last five office actions
listed in the File History. so for this publication it
would be Number 18-22, the dates that it took place, and
the description. I want to have the data automatically
put into excel every time i run a macro. is this
possible???
and if a patent is issued how would i get a cell to show
the patent number and the issue date of the patent???
here's the macro for opening up IE window for web query
below but i guess if you have excel automatically update
everytime i run the macro there's no need for the window
to stay open.
-------------------------------------------------------
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 = fs
penTextFile("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
getting excel to open up a query from the web but now i'm
trying to figure out how to import the data that i get at
the website into excel and to automatically update every
time i run the macro.
the website is with the US patent office
[http://pair.uspto.gov/cgi-bin/final/home.pl] and it's
for looking up publications and its office actions.
For example I have this publication[queried exactly as
typed here]: 2001-0000616 A1,
and i want to put into excel the last five office actions
listed in the File History. so for this publication it
would be Number 18-22, the dates that it took place, and
the description. I want to have the data automatically
put into excel every time i run a macro. is this
possible???
and if a patent is issued how would i get a cell to show
the patent number and the issue date of the patent???
here's the macro for opening up IE window for web query
below but i guess if you have excel automatically update
everytime i run the macro there's no need for the window
to stay open.
-------------------------------------------------------
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 = fs
penTextFile("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