query table with patent publications
 

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=VBScriptdocument.searchSelectionForm.sub mit()"
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