Extract output back to Excel sheet... 2

[DrMingle]

I am able to fill the form out the form, but am having a difficult time extracting the output back into an Excel file. I am only looking for Text.

http://ssdi.rootsweb.ancestry.com/cgi-bin/ssdi.cgi

Can someone give me a start...my general idea is to identify the tags I need such as Name, Last State, etc and pull that data back the sheet...

Any guidance is appreciated...

You can type in "johnson" as a last name to generate the output...

 

[SkipVought]

Hi,

Perhaps a better explanation of your requirements. What you posted is rather sketchy and obtuse, don't you think?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DrMingle]

Skip, there was no intent to be either sketchy or obtuse...

What I am looking for is a snippet of code to help me bring down the data I find on the internet. I posted the website for those who need to see it and offered a general last name to help bring up the results that I wish to bring down to an excel spreadsheet.

I originally thought I could use such a command as this:

IE.document.body.innerHTML

But at this point I am not having any luck...I wish that I had the code ironed out a bit more, but I am unable to really offer more than the general concept above...

Thanks for your reply.

 

[SkipVought]

Check out Data > Import External Data > New WEB Query...[/b]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DrMingle]

I receieved an error message "The web query returned no data...".

Any ideas?

 

[SkipVought]

When you arrive at the page of interest, within this process, you must then

1. SELECT the area of the page you want to import and
2. IMPORT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DrMingle]

I performed both step 1 and 2...

and then I receieved an error message...stating the web query returned no data.

 

[SkipVought]

My results, using your URL and the FIRST SELECTION ARROW on the FIRST PAGE
[tt]
Social Security Death Index Interactive Search

86,474,563 Records Lock The key to your research
last updated on 4-14-2010 Join Ancestry.com Today!

The most full-featured SSDI search engine on the internet

Last name
First Name
Middle Name (initial)
SSN


CPU seconds used 0

For more info about the SSDI, see

http://www.rootsweb.com/~rwguide/lesson10.htm

For help using the SSDI search, see Problem solving
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wan1ord]

Ok I was trying to do this the other day and where it becomes a problem is that the URL is dynamic on the SSDI page. There are very complex ways to get around like doing a sendkeys etc. What I did to solve was save the webpage to my local machine then run the macro. For just the issue of getting a table into Excel here is the code:

Sub junktableimport()
'
' junktableimport Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;file:///C:/Documents%20and%20Settings/testuser/My%20Documents/junk/ssdi.cgi.htm" _
, Destination:=Range("$A$1"))
.Name = "ssdi.cgi"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

...here is the code:

Which is what you would get by RECORDING Data > Import External Data > New WEB Query...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wan1ord]

They are using embedded tables, so tables within tables on their page so this might be very difficult to parse with any automated method.

 

[SkipVought]

I would advise NOT using the posted code repeatedly, as each time it is excuted, it ADDS a NEW query table object to your sheet.

Sub REFRESHtableimport()

    With ActiveSheet.QueryTables(1)
       .Connection:="URL;file:///C:/Documents%20and%20Settings/testuser/My%20Documents/junk/ssdi.cgi.htm" 
       .Refresh BackgroundQuery:=False
    End With
End Sub

You can change the Connection string on the fly, if need be.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DrMingle]

O.k. guys this is what I got... I am getting the results to show on the web page, but it is still not importing back into a spreadsheet...

Any ideas...?

Sub SSI_MAcRO()
'
' SSI_MAcRO Macro
'
'_PULL UP FORM
    Dim Ie
    Set Ie = CreateObject("InternetExplorer.application")
    Ie.Visible = True
    Ie.navigate ("[URL unfurl="true"]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(0).all("lastname").Value = Range("I11")
    Ie.Document.forms(0).all("firstname").Value = Range("I14")
    Ie.Document.forms(0).submit.Click

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://ssdi.rootsweb.ancestry.com/cgi-bin/ssdi.cgi",[/URL] Destination:=Range( _
        "A1"))
        .Name = "ssdi"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "9"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

You never took seriously my previous comment, apparently.

    ActiveSheet.QueryTables("ssdi").Refresh BackgroundQuery:=False

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]