Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Charlotte, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Capturing Data from Web Page

    Does anyone know of any way to capture data from a web page and put it into an Access database? My customer has a database driven web site that contains data that we would like to put into an Access application but we would like to avoid rekeying the data. If we could visit the page, capture the data and populate a database table (or a spreadsheet) it would be great. (yeah I know, we should just connect to the customer's database, but we can't because of firewall and other issues at the moment)

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Capturing Data from Web Page

    You can retrieve the page using the InternetExplorer object (add a reference to the "Microsoft Internet Controls" and "Microsoft HTML Object Library" libraries). I only just started playing with this, but here's an example:

    <pre>Option Explicit
    Sub ExamineTags()
    'pop open browser window
    Dim myBrowser As New InternetExplorer
    Dim myWebPage As HTMLDocument
    With myBrowser
    .Navigate ""
    .Visible = True 'for testing; need not be visible
    End With
    'kludge alert! - really, there is a way to detect when the event is complete
    MsgBox "Click OK when page is loaded"
    Set myWebPage = myBrowser.Document
    Dim intCounter As Integer, objTableCell As HTMLTableCell
    For intCounter = 0 To myWebPage.all.Length - 1
    If TypeName(myWebPage.all(intCounter)) = "HTMLTableCell" Then
    'set an object reference to this cell and look inside
    Set objTableCell = myWebPage.all(intCounter)
    If Len(Replace(objTableCell.innerText, " ", "")) > 0 Then
    'it's not just white space
    Debug.Print "Cell numbered Tag " & intCounter & " contains: " & _
    End If
    End If
    If Not objTableCell Is Nothing Then Set objTableCell = Nothing
    Set myWebPage = Nothing
    Set myBrowser = Nothing
    End Sub</pre>

    Now, to do this right, you would need to get more sophisticated, since the nesting of tables causes duplicate data to be retrieved. But this is one approach. What I have done in the past, not being familiar with this set of objects, is to open the source of the pages as text in Word, and use Find/Replace to get at the data. Distracting for the user, but effective if the format is fairly standard. Good luck!

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Redcliff, Alberta, Canada
    Thanked 5 Times in 5 Posts

    Re: Capturing Data from Web Page

    You might want to try this suggestion from Geoff Whitfield (Moderator/WMVP).
    "I can point him to Excel- there's an option from the menu (Data, Get external data, Run saved query) to get Web data"
    He'd tell you himself - but I think there is a holiday or something down under (or maybe he's sleeping <img src=/S/doze.gif border=0 alt=doze width=15 height=15>.... naaah Geoff never sleeps!) <img src=/S/wink.gif border=0 alt=wink width=15 height=15>.

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts