Using Excel to Fill Out Form - Getting error on last name

[drmingle]

Thank you for your help.

I need to be able to pull up the below site and fill out the form. Idealy extracting the output generated.

Below is what I have so far:

Sub SSI_MAcRO()
'
' SSI_MAcRO Macro
'
'_PULL UP FORM
    Dim Ie
    Set Ie = CreateObject("InternetExplorer.application")
    Ie.Visible = True
    Ie.navigate ("[URL]http://ssdi.rootsweb.ancestry.com/cgi-bin/ssdi.cgi[/URL]")
    Do
        If Ie.readyState = 4 Then
            Ie.Visible = True
            Exit Do
        Else
            DoEvents
        End If
    Loop
'USE VIEW SOURCE TO GET FORM ELEMENT IDS
    Ie.document.forms(1).all("lastname").Value = Range("I11")
    Ie.document.forms(1).all("firstname").Value = Range("I14")
    Ie.document.forms(1).submit
'
End Sub

I am having a problem with the form elements:

******** type="text/javascript" src="http://images.rootsweb.ancestry.com/js/rwhr.js">*********>
******** type="text/javascript" src="http://images.rootsweb.ancestry.com/js/oas_ssdi.js">*********>
******** type="text/javascript" language="javascript1.1" src="http://images.rootsweb.ancestry.com/js/o2.js">*********>
******** type="text/javascript" src="http://images.rootsweb.ancestry.com/js/o3.js">*********>


******** type="text/javascript">writeHeader('760px','Searches');*********>



******** type="text/javascript">OAS_AD('Top');*********>










Social Security Death Index (SSDI)

86,272,034 








Search the Social Security Death Index by entering one or more fields in the form and clicking on the 
"submit" button. Keep in mind that the more fields you fill in the more restricted your results 
will be (and you may even eliminate the record you are seeking).

 




SSDI 
Tutorial





 • Missing Entries

 • Reporting Inaccuracies

 • Definitions, Search Tips

 • Full Tutorial








 


RootsWeb's 
Guide to Tracing Family Trees







U. S. Social Security Death Index (SSDI) and 
Railroad Retirement Board Records

























Last Name


 ExactSoundexMetaphone





First Name







Middle Name or Initial







Social Security Number







  


  






 


















******** type="text/javascript">writeFooter();*********>
******** type="text/javascript">
var s_pageName="SSDI Main Page - //ssdi/index.html";
*********>
******** type="text/javascript" src="http://img.rootsweb.com/omniture/omniture_tracking.js">*********>
******** type="text/javascript" src="http://id.ancestry.com/html/script/TSpacer.js">*********>

 

[Norie]

What is the problem?

 

[drmingle]

The form is not filling out...I am asked to debug on the "last name" line...

 

[drmingle]

Okay everything seems to be working with the exception of the "submit"...

any guidance?

Sub SSI_MAcRO()
'
' SSI_MAcRO Macro
'
'_PULL UP FORM
    Dim Ie
    Set Ie = CreateObject("InternetExplorer.application")
    Ie.Visible = True
    Ie.navigate ("[URL]http://ssdi.rootsweb.ancestry.com/cgi-bin/ssdi.cgi[/URL]")
    Do
        If Ie.readyState = 4 Then
            Ie.Visible = True
            Exit Do
        Else
            DoEvents
        End If
    Loop
'USE VIEW SOURCE TO GET FORM ELEMENT IDS
    Ie.document.forms(1).all("lastname").Value = Range("I11")
    Ie.document.forms(1).all("firstname").Value = Range("I14")
    Ie.document.forms(0).submit
'
End Sub

 

[Norie]

That's exactly the problem I ran into - the submit.

Everything else seemed to work but then I got an 'Object doesn't support this method...' error.

Is that the error you are getting?

PS Why did you change from forms(1) to forms(0)?

 

[drmingle]

Replace the submit line of code with the following:

Ie.Document.forms(1).submit.Click

It works!

Any idea how to pull the represented output back into an excel spreadsheet. I want the text only.

 

[Norie]

If I had a clue about the data you want to get it might help.

I've tried my own name, made up names but I can't seem to get past the main page.

When I try a generic name like 'Gordon Smith' I get lots of results, at least 21 pages.

 

[drmingle]

Type this in the last name section:JOHNSON
Leave the other fields blank
Hit submit

 

[John_w]

You don't need to populate the form and submit it. You can specify the search parameters directly in the URL (the query string). The following code shows the first 100 results. You then just need some code to extract the search results from each page - search this forum for 'getonetable' for example code to do this.

Sub SSDI_results()
    
    Dim URL As String
    Dim IE As Object
    Dim lastName As String, firstName As String, start As Long
    
    URL = "http://ssdi.rootsweb.ancestry.com/cgi-bin/ssdi.cgi"
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    lastName = "JOHNSON"
    firstName = "EARL"
    
    start = 1
    While start < 101
        With IE
            .Visible = True
            .navigate URL & "?lastname=" & lastName & "&firstname=" & firstName & "&start=" & start
            While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        End With
        start = start + 20  'Next 20 results
    Wend
    
End Sub

 

[Norie]

John

Can you post a link for 'getonetable'?

When I search with that term the only result directs me back to this thread.

I can find threads that have posts with a sub called GetOneTable when I google, apparently written by a puffin.

Here's one thread.