43414

VBA HTML Data Scrape Guidance

Question:

I'm attempting to extract data from the following site using VBA, by inputting a city, and having selected results outputted into excel cells. I'm very new to this, and this my third attempt, but now I'm getting a "Object Required" error when I try to run it. I've stepped through it, and it throws the error at, of course, the IE object I tried to create. Any suggestions on what I can do to tweak my code? Any help would be much appreciated! Thank you.

<h2>Code</h2> Private Sub CreditUnion() If Target.Row = Range("City").Row And Target.Column = Range("City").Column Then Dim IE As Object Set IE = CreateObject("internetexplorer.application") IE.Navigate "http://mapping.ncua.gov/SingleResult.aspx" IE.Visible = False Do While IE.Busy DoEvents Loop Set TableResults = IE.document.getElementsByID("MainContent_newDetails") Dim City As String: City = TableResults.Cells(17).innerHTML Dim CreditUnion As String: CreditUnion = TableResults.Cells(0).innerHTML Dim Region As String: Region = TableResults.Cells(9).innerHTML Dim Status As String: Status = TableResults.Cells(3).innerHTML Dim Assets As String: Assets = TableResults.Cells(13).innerHTML Dim Members As String: Members = TableResults.Cells(15).innerHTML Range("B1").Value = City Range("C4").Value = CreditUnion Range("D4").Value = Region Range("E4").Value = Status Range("F4").Value = Assets Range("G4").Value = Members IE.Quit Set IE = Nothing End If End Sub <hr />

Code can't get past this point [Code stuck here][1]

We're getting close! made it past the first screen. It's just not pulling in the data now in the case statements [enter image description here][2]

Answer1:

I take New York for example, the code as below.

I rewrite on 2016/6/7

Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long) Sub CreditUnion() Dim IE As Object, TableResults As Object, webRow As Object, charterInfo As Variant, page As Long, pageTotal As Long, r As Long Dim beginTime As Date, i As Long Set IE = CreateObject("internetexplorer.application") IE.navigate "http://mapping.ncua.gov/ResearchCreditUnion.aspx" IE.Visible = True Do While IE.Busy Or IE.readystate <> 4 '4 = READYSTATE_COMPLETE DoEvents Loop 'input city name into form IE.document.getelementbyid("MainContent_txtCity").Value = "new york" 'click find button IE.document.getelementbyid("MainContent_btnFind").Click sleep 5 * 1000 'total pages pageTotal = IE.document.getelementbyid("MainContent_pager_total").innertext page = 0 Do Until page = pageTotal DoEvents page = IE.document.getelementbyid("MainContent_pager_to").innertext With IE.document.getelementbyid("MainContent_grid") For r = 1 To .Rows.Length - 1 If Not IsArray(charterInfo) Then ReDim charterInfo(7, 0) As Variant Else ReDim Preserve charterInfo(7, UBound(charterInfo, 2) + 1) As Variant End If charterInfo(0, UBound(charterInfo, 2)) = .Rows(r).Cells(0).innertext Next r End With If page < pageTotal Then IE.document.getelementbyid("MainContent_pageNext").Click beginTime = Now Application.Wait (Now + TimeValue("00:00:05")) End If Loop For r = 0 To UBound(charterInfo, 2) IE.navigate "http://mapping.ncua.gov/SingleResult.aspx?ID=" & charterInfo(0, r) Do While IE.Busy Or IE.readystate <> 4 '4 = READYSTATE_COMPLETE DoEvents Loop 'wait 5 sec. for screen refresh sleep 5 * 1000 With IE.document.getelementbyid("MainContent_newDetails") For i = 0 To .Rows.Length - 1 DoEvents Select Case .Rows(i).Cells(0).innertext Case "Credit Union Name:" charterInfo(1, r) = .Rows(i).Cells(1).innertext Case "Region:" charterInfo(2, r) = .Rows(i).Cells(1).innertext Case "Credit Union Status:" charterInfo(3, r) = .Rows(i).Cells(1).innertext Case "Assets:" charterInfo(4, r) = Replace(Replace(.Rows(i).Cells(1).innertext, ",", ""), "$", "") Case "Number of Members:" charterInfo(5, r) = Replace(.Rows(i).Cells(1).innertext, ",", "") Case "Address:" charterInfo(6, r) = .Rows(i).Cells(1).innertext Case "Phone:" charterInfo(7, r) = "'" & .Rows(i).Cells(1).innertext End Select Next i End With Next r IE.Quit Set IE = Nothing 'post result on Excel cell Worksheets(1).Range("A1").Resize(UBound(charterInfo, 2) + 1, UBound(charterInfo, 1) + 1).Value = Application.Transpose(charterInfo) End Sub

Recommend

  • javascript find node without id
  • OpenMP and sections
  • Hbase CopyTable inside Java
  • How to enumerate running ec2 instances and load them into a database using ruby? [closed]
  • Vim syntax: Spell checking between certain regions
  • Combining multiple ggplot2 polygons / paths
  • Face aligment check with DLIB
  • How do I recusively unzip nested ZIP files?
  • Efficiently remove lines from fileA that contains string from fileB
  • Using raw SQL in django-rest framework?
  • Why admob ads in ANY apps not displaying
  • Segmentation fault with array of strings C
  • Memory allocation of base class and derived class constructor
  • Android: Skobbler, how to limit map view in an area?
  • Trouble opening utf-8 URI's with Ruby's 'open-uri'
  • How to supply RequestContext data to the constructor of a domain object?
  • Set which rows to delete on mysql after applying combined unique index w/ignore
  • Trigger for custom dependency properties in Style
  • Autoscroll on parent panel, when there is overflow on child panels.Extjs
  • locationManager:didRangeBeacons method not detecting BLE Device
  • How to reload Oracle APEX 5.0 Tree dynamically
  • Check for validation errors in part of a form in JSF
  • Random access image (picture) file
  • Is creating a new version of an object in AWS S3 eventually consistent or read-after-write consisten
  • How can I get the maximum number of OpenMP threads that may be created during the whole execution of
  • How to merge two Request in Laravel
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • Button click event not firing in jQuery
  • Chart.js Multiple dataset
  • How do I get HTML corresponding to current DOM tree?
  • JQuery Internet Explorer and ajaxstop
  • JSON response opens as a file, but I can't access it with JavaScript
  • sending/ receiving email in Java
  • How to set my toolbar fixed while scrolling android
  • Change an a tag attribute in JavaScript based on screen width
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++