asked on

Accessing myheritage.com from Excel VBA

I am trying to access data from myheritage.com on Internet Explorer using Excel VBA

So far I have been successful in getting the family data from an individual's profile page.

Now I am trying to be able to click on the family member to go to his/her page but cannot find the anchor to do so.

Can anyone help me out with this?

Using a browser other than Internet Explorer is also welcome.

Sub getImmediateFamilyFromMyheritage(IE As Object)
    Dim w As Object
    Dim html As String
    Dim htmlarray
    Dim pcel As Range
    Dim tcel As Range
    Dim i As Long
    Dim DesSzCol As Range
    Dim MembrCol As Range
    Set MembrCol = Cells.Find("Member Column", , , xlWhole).EntireColumn
    Set DesSzCol = Cells.Find("Descendent Size", , , xlWhole).EntireColumn
    Application.Calculation = xlCalculationManual
  For Each w In CreateObject("Shell.Application").Windows
    If w.Name = "Internet Explorer" Then
    
        Set HTMLDoc = w.document
        html = HTMLDoc.body.innerText
        htmlarray = Split(Mid(html, InStr(html, "Immediate family"), _
                            InStr(InStr(html, "Immediate family"), html, "showCAPTCHA") - _
                            InStr(html, "Immediate family")) & vbLf & vbCr, vbLf)
        Set pcel = ActiveCell
        Set tcel = pcel.Offset(1, 1)
        For i = 1 To UBound(htmlarray)
        htmlarray(i) = Replace(htmlarray(i), vbCr, "")
        Select Case htmlarray(i) 'InStr("His son His daughter Her son Her Daughter His wife Her husband", cel.Value)
            Case ""
                Select Case htmlarray(i - 1)
                    Case vbCr, "Immediate family" & vbCr
                    Case "His wife", "Her husband"
                        If IsEmpty(pcel.Offset(, 6)) Then
                            pcel.Offset(, 6) = htmlarray(i - 2)
                            pcel.Offset(, 6).Font.Bold = True
                        Else
                            If IsEmpty(pcel.Offset(, 7)) Then
                                pcel.Offset(, 7) = htmlarray(i - 2)
                                pcel.Offset(, 7).Font.Bold = True
                            Else
                                pcel.Offset(, 6).End(xlToRight).Offset(, 1) = htmlarray(i - 2)
                                pcel.Offset(, 6).End(xlToRight).Font.Bold = True
                            
                            End If
                        End If
                    Case "Her father", "Her mother", "Parent", "His parent", ""
                    Case "His father", "His mother", "His father  "
                    Case "His father    ", "His mother    ", "His mother  "
                    Case "His sister", "His brother", "His brother  "
                    Case "His brother    ", "Sibling", "His sister  ", "His sister    "
                    Case "Her brother", "Her brother  ", "Her brother    "
                    Case "Her sister", "Her sister    ", "Her sister  "
                    Case "Half sister", "Half brother", "Half brother  ", "Half sister    ", "Half sister  "
                    Case "Her daughter", "Her son"
                    Case "His daughter", "His son", "His son    ", "Daughter", "Son", "His son  "
                        tcel.EntireRow.Insert
                        tcel.Offset(-1).Value = htmlarray(i - 2)
                        tcel.Offset(-1).Font.Bold = True
                    Case "Contact information"
                        Exit For
                    Case Else
                        Debug.Print htmlarray(i - 1)
                        Stop
                End Select
        End Select
        Next i
        pcel.Font.Bold = False
    End If

  Next w
Range(Cells(pcel.Row, DesSzCol.Column), Cells(pcel.Row, MembrCol.Column)).Copy Range(Cells(pcel.Row, MembrCol.Column), Cells(pcel.Row, MembrCol.Column).End(xlDown))
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Open in new window

Bill Prew

Without an account to sign into on that site it will be hard for any experts to help you (in my opinion), since we cant know/see the underlying page content and HTML to know what elements are on it and how to navigate to the information you want.

You might try viewing the source on the page involved and posting that here, unless you know of some way to allow access to the actual data and page you are viewing?

»bp

Saqib Husain

ASKER

You can try this username/password
ssaqibhusain@yahoo.com
expertsexchange

Bill Prew

Why not just export the tree in GEDCOM format, and then parse that "text" file?

»bp

ste5an

Is it legal do to this in the first place? And does it comply with their service terms? I guess not.

You should look into their Family Graph API.

Saqib Husain

ASKER

Bill,

The tree I am trying to access is from someone else and does not give me GEDCOM access. I can use GEDCOM export on my tree only. This probably ends up at

Ste5an's point that I am not supposed to go there.

Since I could access all the data manually, I thought automating it would be legal. The data I am accessing is of the order of 7000 entries and it is easy to miss-out if done manually.

Bill Prew

What page(s) are you looking at trying to gather the data from?

»bp

Saqib Husain

ASKER

When you are in the tree, select a person and visit the Profile of that person. The profile is accessible from the window opened at the left of the tree when you select a person in the tree.

Saqib Husain

ASKER

BTW, as per Ste5an I have sent request for the Family Graph API.

Bill Prew

Good on API, if you can get at the data you desire that way it feels like the better, more structured approach, and should be more reliable than site scraping.

»bp

Saqib Husain

ASKER

I have also sent two subsequent reminders to the Family Graph API host but no response. So I guess my question remains where it was.

Bill Prew

@Saqib Husain, Syed,

Are you all set with this now, or do you need more help? If all set, could you please close it out now. If you need help with the question close process take a look at:

How do I select a solution for my question?

»bp

Saqib Husain

ASKER

As I said, got no response from Family Graph API so I am still where I was...against a blank wall.

Bill Prew

So far I have been successful in getting the family data from an individual's profile page.
Now I am trying to be able to click on the family member to go to his/her page but cannot find the anchor to do so.

Can you show a screen capture of the "profile page" you are extracting data from currently, and the "link" on that page that you want to click/activate? It wasn't clear to me as I took a look at their site...

»bp

Saqib Husain

ASKER

I have brought this down from above for your convenience

You can try this username/password
ssaqibhusain@yahoo.com
expertsexchange

When you are in the tree, select a person and visit the Profile of that person. The profile is accessible from the window opened at the left of the tree when you select a person in the tree.

Now I am trying to be able to click on a family member to go to his/her page but cannot find the anchor to do so.

Bill Prew

Okay, let me try and ask it with an image. I have done what you described and I see the page shown below. Where on that page do you want to click to go to another family member?

»bp

Saqib Husain

ASKER

If you click on

Profile

under the photo near the top left you would go to the profile page.

On that page I would like to be able to click on the names of the other two family members highlighted below.

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

ASKER

Looks like this is just what I am looking for. Since this found its way to the bottom of my to-do list, I don't remember much how this these things work. Now I am going to sit down and search how to invoke the FL_LinkBold. If I am able to get this to work then this is the end of this question. Otherwise I may come back for more guidance on how to invoke an FL_LinkBold.

Thanks for all the efforts.

Saqib Husain

ASKER

I have not been able to find the FL_LinkBold class.

I have, however, ventured into the .all object of the HTMLDOC and managed to find HTMLAnchorElement. The following routine works.

Set HTMLDoc = w.document

For Each elmnt In HTMLDoc.all
Select Case TypeName(elmnt)
Case "HTMLAnchorElement"
Dim ancr As HTMLAnchorElement
Set ancr = elmnt
******routine to select desired ancr******
ancr.Click
End Select
Next elmnt

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

Glad you made some headway!

»bp