-
Tough VBA data pulling from IE to Excel coding question
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?
-
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.
[VBA]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[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules