Saqib Husain
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.
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
ASKER
You can try this username/password
ssaqibhusain@yahoo.com
expertsexchange
ssaqibhusain@yahoo.com
expertsexchange
Why not just export the tree in GEDCOM format, and then parse that "text" file?
»bp
»bp
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.
You should look into their Family Graph API.
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.
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.
What page(s) are you looking at trying to gather the data from?
»bp
»bp
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.
ASKER
BTW, as per Ste5an I have sent request for the Family Graph API.
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
»bp
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.
@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:
»bp
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:
»bp
ASKER
As I said, got no response from Family Graph API so I am still where I was...against a blank wall.
So far I have been successful in getting the family data from an individual's profile page.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...
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.
»bp
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.
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.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Thanks for all the efforts.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you made some headway!
»bp
»bp
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