PDA

View Full Version : Tough VBA data pulling from IE to Excel coding question



igotgame
05-04-2009, 07:57 PM
I am attempting to pull some variables from an IE webpage into excel and the variables are contained within an array. The code looks like this:

<SCRIPT LANGUAGE="JavaScript">

var demo_mode = false;
var onFocusExitsEnabled = false;
document.mainForm.wp_submit=propertySubmit;
var ns4 = (document.layers)? true:false;
var ie4 = (document.all)? true:false;
var promptUser = "[Prompt User for Value]";
var errorFieldFocus=null;
var bpoItems = new Array()
bpoItems[0] = new BPO("23","REO Auto Val","null","Good ","N","53","20090312","null","125050.0000","125050.0000","null","1.0","1","O","NPL VGRP ","null","Mods, Repay, CO, TP")


BpoItems is the array I am looking at and I need the 2 bolded variables from that array outputted to an excel spreadsheet.

I have attempted all kinds of "getelementbyID/TagName/Name..etc" to no avail....

Can anyone help me here?

Kenneth Hobs
05-05-2009, 08:30 AM
I gather you are using the IE object model. Iterate through the All collection.

You could use the winhttp object and get the source code and parse it.
Here is an example showing late and early binding methods. Once it found the right text using InsStr() you can use Split() and then get the elements from the array that you need.
Sub tDividendDate()
[a1] = DividendDate
End Sub

'late binding
Public Function DividendDate() As Date
Dim Request As Object, s As String, p As Long, sLen As Integer

On Error Resume Next
Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
If Request Is Nothing Then
Set Request = CreateObject("WinHttp.WinHttpRequest.5")
End If

Request.Open "GET", "http://finance.yahoo.com/q/ks?s=TOT", False
Request.Send

s = Request.ResponseText
p = InStr(s, "Dividend Date")
Debug.Print s, p
p = InStr(p, s, "yfnc_tabledata1") + 17
DividendDate = CDate(Mid(s, p, InStr(p, s, "<") - p))
End Function

'early binding
Public Function DividendDate2() As Date
'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
Dim Request As New WinHttpRequest, s As String, p As Long, sLen As Integer


Request.Open "GET", "http://finance.yahoo.com/q/ks?s=TOT", False
Request.Send

s = Request.ResponseText
p = InStr(s, "Dividend Date")
p = InStr(p, s, "yfnc_tabledata1") + 17
DividendDate2 = CDate(Mid(s, p, InStr(p, s, "<") - p))
End Function