HTML scraping

[Neekos]

Im using VBA (in Access) to scrape an internal webpage at work to gather email addresses. The emails are encoded and i need to use this page to decode them. I can get it to return the entire HTML of a page and within this i need to narrow down and extract the email address.

right now i have this as my code:

Private Sub Command0_Click()
Dim webBrowser As webBrowser
Dim recordset As ADODB.recordset
Dim ScrapeHTML As String
Dim test As String
 
Set webBrowser = CreateObject("InternetExplorer.Application")
 
Set recordset = New ADODB.recordset
    recordset.Open "scrambled", CurrentProject.Connection
 
With webBrowser
    .Navigate "URL removed for security purposes"
    .Visible = True
        Do While webBrowser.Busy
           DoEvents
        Loop
     End With
Do While webBrowser.Busy
    DoEvents
Loop
 
Do While Not recordset.EOF
Me.[pnr] = recordset![pnr]
Do While webBrowser.Busy
    DoEvents
Loop
Debug.Print test
With webBrowser
    .Document.Forms("EncodeDecodeEmailForm").all("decode").focus
    .Document.Forms("EncodeDecodeEmailForm").all("decode").Value = recordset![Scrambled email]
    SendKeys "{TAB}"
    Pause 1
    SendKeys "{ENTER}"
Pause 2
 
Do While webBrowser.Busy
    DoEvents
Loop
 
ScrapeHTML = webBrowser.Document.documentElement.innerHTML
ScrapeHTML = Mid(ScrapeHTML, InStr(1, ScrapeHTML, "this.form.encode") + 112, 60)
Debug.Print ScrapeHTML
ScrapeHTML = Left(ScrapeHTML, InStr(1, ScrapeHTML, "<") - 1)
Debug.Print InStr(1, ScrapeHTML, ">")
Me.[decoded email] = Right(ScrapeHTML, InStr(1, ScrapeHTML, ">") + 30)
Debug.Print Me.[decoded email]
 
recordset.MoveNext
DoCmd.RunCommand acCmdRecordsGoToNew
 
End With
Do While webBrowser.Busy
    DoEvents
Loop
With webBrowser
    .Document.Forms("EncodeDecodeEmailForm").all("decode").focus
    .Document.Forms("EncodeDecodeEmailForm").all("decode").Value = ""
 
End With
Do While webBrowser.Busy
    DoEvents
Loop
Pause 1
 
Do While webBrowser.Busy
    DoEvents
Loop
Loop
End Sub

and my output looks like this:

iddle>[e-mail]

obviously im not doing something right with my Left(), Right() functions, but i cant seem to figure it out. In fact i think that location of the email address is moving around in the HTML by a few characters every time.

Does anyone have any ideas on how to accomplish this?

[Dököll]

Im using VBA (in Access) to scrape an internal webpage at work to gather email addresses...

Sorry for your troubles, Neekos!

I am sending over to the Access forum for further support, surely could be handled here but our Access forum is extremely busy, just in case someone sees yours.

Not sure how to handle this either, Neekos, hope you find what you're looking for:-)

[ADezii]

The following code will work for the constant pattern of text>E-Mail Address. Should this pattern vary, I need to know some of the parameters, namely:

1. Does the E-Mail Address always follow the '>'?
2. Can the E-Mail Address appear anywhere within the string?
3. Is the '.' delimiter the only period that will appear in the String?
4. Anything else you can think of...

Dim strCompleteString As String
Dim strEMailAddr As String
 
strCompleteString = "iddle>[e-mail]"
strEMailAddr = Right$(strCompleteString, Len(strCompleteString) - InStr(strCompleteString, ">"))
 
Debug.Print strEMailAddr

OUTPUT:

[e-mail]

[Neekos]

The email address always follows the '>' (its part of an html tag)

Here is the actual HTML that its scraping:

<tr><td align=center>Enter Encoded E-Mail Address to DECODE</td></tr>
<tr><td align=center><input type="text" name="decode" maxlength="60" value="$16$79/IRSWBERQB.DUJCYSXQUB01" onclick="clearField(this.form.encode)"></td></tr>
<tr><td align=center id=hilite>[e-mail]</td></tr>
</table>
 
<table border="0" width="400">
<tr><td align=center><input type="button" name="send" value="Submit" onclick="javascript:return validateForm();">
    &nbsp;&nbsp;<input type="reset" value="Reset"></td>

for the most part, the email address is in the same character position for every one, but occassionally ill get some that are off by about 4-6 characters (so the output is getting trimmed)

The following code will work for the constant pattern of text>E-Mail Address. Should this pattern vary, I need to know some of the parameters, namely:

1. Does the E-Mail Address always follow the '>'?
2. Can the E-Mail Address appear anywhere within the string?
3. Is the '.' delimiter the only period that will appear in the String?
4. Anything else you can think of...

Dim strCompleteString As String
Dim strEMailAddr As String
 
strCompleteString = "iddle>[e-mail]"
strEMailAddr = Right$(strCompleteString, Len(strCompleteString) - InStr(strCompleteString, ">"))
 
Debug.Print strEMailAddr

OUTPUT:

[e-mail]

[mshmyob]

May I jump in here? I have noticed in your HTML scrape that the unique identifier is the '@' symbol. I would therefore use that to determine the email address.

Logic would be like so:

1. Find the @ symbol
2. Look backwards to find the '>' symbol
3. Look forward to find the '<' symbol
4. What is in between is the email address (this eliminates variable lengths)

Here is the code

Dim vSearchStr As String
 Dim vAmpPos As Long
    Dim vStartPos As Long
    Dim vEndPos As Long
    Dim vEmail As String
 
 
    vSearchStr = [Your scraped HTML]
    ' get the position of the @ symbol
    vAmpPos = InStr(1, vSearchStr, "@", 1)
    ' get the end position
    vEndPos = InStr(vAmpPos, vSearchStr, "<", 1)
    ' get the start position
    vStartPos = InStrRev(vSearchStr, ">", vAmpPos, 1)
    ' this is your email address
    vEmail = Mid(vSearchStr, vStartPos + 1, (vEndPos - vStartPos) - 1)
 

Hope this is of some help

cheers,

The email address always follows the '>' (its part of an html tag)

Here is the actual HTML that its scraping:

<tr><td align=center>Enter Encoded E-Mail Address to DECODE</td></tr>
<tr><td align=center><input type="text" name="decode" maxlength="60" value="$16$79/IRSWBERQB.DUJCYSXQUB01" onclick="clearField(this.form.encode)"></td></tr>
<tr><td align=center id=hilite>[e-mail]</td></tr>
</table>
 
<table border="0" width="400">
<tr><td align=center><input type="button" name="send" value="Submit" onclick="javascript:return validateForm();">
    &nbsp;&nbsp;<input type="reset" value="Reset"></td>

for the most part, the email address is in the same character position for every one, but occassionally ill get some that are off by about 4-6 characters (so the output is getting trimmed)

[Neekos]

HA! how the heck did i not think of using that in the first place?? Here i was counting hundreds of characters away to find something unique in the HTML. Thank you!

May I jump in here? I have noticed in your HTML scrape that the unique identifier is the '@' symbol. I would therefore use that to determine the email address.

Logic would be like so:

1. Find the @ symbol
2. Look backwards to find the '>' symbol
3. Look forward to find the '<' symbol
4. What is in between is the email address (this eliminates variable lengths)

Here is the code

Dim vSearchStr As String
 Dim vAmpPos As Long
    Dim vStartPos As Long
    Dim vEndPos As Long
    Dim vEmail As String
 
 
    vSearchStr = [Your scraped HTML]
    ' get the position of the @ symbol
    vAmpPos = InStr(1, vSearchStr, "@", 1)
    ' get the end position
    vEndPos = InStr(vAmpPos, vSearchStr, "<", 1)
    ' get the start position
    vStartPos = InStrRev(vSearchStr, ">", vAmpPos, 1)
    ' this is your email address
    vEmail = Mid(vSearchStr, vStartPos + 1, (vEndPos - vStartPos) - 1)
 

Hope this is of some help

cheers,

[mshmyob]

You're welcome. We all have been in the same boat. The more eyes the better.

cheers,

HA! how the heck did i not think of using that in the first place?? Here i was counting hundreds of characters away to find something unique in the HTML. Thank you!