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:

[CODE=vb]Private Sub Command0_Click( )
Dim webBrowser As webBrowser
Dim recordset As ADODB.recordset
Dim ScrapeHTML As String
Dim test As String

Set webBrowser = CreateObject("I nternetExplorer .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 ("EncodeDecodeE mailForm").all( "decode").f ocus
.Document.Forms ("EncodeDecodeE mailForm").all( "decode").V alue = recordset![Scrambled email]
SendKeys "{TAB}"
Pause 1
SendKeys "{ENTER}"
Pause 2

Do While webBrowser.Busy
DoEvents
Loop

ScrapeHTML = webBrowser.Docu ment.documentEl ement.innerHTML
ScrapeHTML = Mid(ScrapeHTML, InStr(1, ScrapeHTML, "this.form.enco de") + 112, 60)
Debug.Print ScrapeHTML
ScrapeHTML = Left(ScrapeHTML , InStr(1, ScrapeHTML, "<") - 1)
Debug.Print InStr(1, ScrapeHTML, ">")
Me.[decoded email] = Right(ScrapeHTM L, InStr(1, ScrapeHTML, ">") + 30)
Debug.Print Me.[decoded email]

recordset.MoveN ext
DoCmd.RunComman d acCmdRecordsGoT oNew

End With
Do While webBrowser.Busy
DoEvents
Loop
With webBrowser
.Document.Forms ("EncodeDecodeE mailForm").all( "decode").f ocus
.Document.Forms ("EncodeDecodeE mailForm").all( "decode").V alue = ""

End With
Do While webBrowser.Busy
DoEvents
Loop
Pause 1

Do While webBrowser.Busy
DoEvents
Loop
Loop
End Sub[/CODE]

and my output looks like this:

iddle>BOB@COMCAST.NET

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...

[CODE=vb]
Dim strCompleteStri ng As String
Dim strEMailAddr As String

strCompleteStri ng = "iddle>BOB@COMC AST.NET"
strEMailAddr = Right$(strCompl eteString, Len(strComplete String) - InStr(strComple teString, ">"))

Debug.Print strEMailAddr[/CODE]
OUTPUT:
[CODE=text]BOB@COMCAST.NET[/CODE]

[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>[B]BOB@COMCAST.NET[/B]</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...

[CODE=vb]
Dim strCompleteStri ng As String
Dim strEMailAddr As String

strCompleteStri ng = "iddle>BOB@COMC AST.NET"
strEMailAddr = Right$(strCompl eteString, Len(strComplete String) - InStr(strComple teString, ">"))

Debug.Print strEMailAddr[/CODE]
OUTPUT:
[CODE=text]BOB@COMCAST.NET[/CODE]

[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

[code=vb]
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(vSearc hStr, ">", vAmpPos, 1)
' this is your email address
vEmail = Mid(vSearchStr, vStartPos + 1, (vEndPos - vStartPos) - 1)
[/code]

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>[B]BOB@COMCAST.NET[/B]</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

[code=vb]
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(vSearc hStr, ">", vAmpPos, 1)
' this is your email address
vEmail = Mid(vSearchStr, vStartPos + 1, (vEndPos - vStartPos) - 1)
[/code]

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!