Importing online data into an Excel spreadsheet

[jayrdi]

Hi all, I have been tasked with streamlining a process to collect data from a specific online website and import it into an Excel 2010 spreadsheet. Website is 'Web of Science', Web of Science [v.5.13.3] - All Databases Home which is a site that details academic publications and journals.

Currently they are going to the website, entering a short number of search parameters and then manually recording the pertinent data from the webpage. They would like to be able to enter a keyword in Excel (which acts as the search item) which then automatically does the rest of the process and provides them with a spreadsheet of the required data.

The data they require however comes in the form of a list of potential authors for the entered publication name which currently comes up on the website as a selection of check-boxes under 'Refine Results': Web of Science [v.5.13.3] - All Databases

So basically I need the user to be able to enter the search item in Excel that they would enter into the search field on the website, and then import a list of authors relevant to that subject into a spreadsheet.

Is this possible? Perhaps by using VBA code within Excel?

What would people recommend? I am really only a novice Excel user but do have programming experience.

Thank you very much for your help,

John

 

[jayrdi]

FYI this is what I have so far, only been working on it for a few hours!

Sub WoS()

  [COLOR=#ff0000]  ' allow user to enter keyword for search
    ' myInput = InputBox("Please enter the subject for Publication Name: ")[/COLOR]
    
    Dim IE As Object
    Dim Doc As Object
    Set IE = CreateObject("internetexplorer.application")
    IE.Visible = True
        
navigate:
    
   [COLOR=#ff0000] ' navigate to the relevant website[/COLOR]
    IE.navigate "http://apps.webofknowledge.com/UA_GeneralSearch_input.do?product=UA&search_mode=GeneralSearch&SID=W1QoAFWjMXZh2esItCv&preferencesSaved="
    
[COLOR=#ff0000]    ' wait until page has finished loading
[/COLOR]    Do While IE.readystate <> 4: DoEvents: Loop
    
[COLOR=#ff0000]    ' create object html class
[/COLOR]    Set Doc = CreateObject("htmlfile")
    Set Doc = IE.document
    
    If Doc Is Nothing Then GoTo navigate
    
[COLOR=#ff0000]    ' value(input1) is id of field where user enters search words
[/COLOR]    Set myInput = Doc.getelementbyid("value(input1)")
    myInput.Value = InputBox("Please enter the subject for Publication Name: ")

[COLOR=#ff0000]    ' click 'submit' button after entered search parameter
[/COLOR]    Set btnSubmit = Doc.getelementbyid("UA_GeneralSearch_input_form_sb")
    btnSubmit.Click
    
End Sub

Have put comments in red so that they are obvious. Wasn't sure where to put the 'myInput' value (entered by user) which is why it's in there a few times and commented out. Where it is left is where it seemed to work.

Thanks!