Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query and Add Table Window | Excel Discussion (Misc queries) | |||
Top 10 Publications and Matching Text Values | Excel Worksheet Functions | |||
Pivot table and MS Query | Excel Discussion (Misc queries) | |||
a query for pivot table | Excel Discussion (Misc queries) | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |