Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Screen Grab (Windows)

    This may not be the right forum, but here goes.
    I wonder if anybody can help me here. I need a way to grab the contents of a betting odds screen and save this in a file for further processing. I need to make this automatic and not a manual function.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Screen Grab (Windows)

    Assuming that this data is on a web page then you could try using a Web Query from within Excel (Data > Get External Data > New Web Query), this could be automated using VBA.

    StuartR

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Thanks for your reply Stuart, but I was wondering whether it could be done from VBA in Access?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    What is the form of the data? Plain text in an HTML page? Java applet or ActiveX control? Source is empty and contents are rendered at runtime with JavaScript? Only the first of these lends itself easily to "scraping."

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Plain text in an html page.
    Have a look at http://www.tabracing.com.au/realodds.../28/index.html
    I need to get the table into a text file, this repaints itself every few minutes.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    Okay, they let you open the page directly (without reloading frames). You can use the MSHTML object library to read through the tags collection, or you can "brute force" the data using VBA string methods or automating Word.

    Here's a snippet of code that reads a web page with MSHTML. There's more documentation at Microsoft.com, but I found it tough going getting into the object model. As I recall, this worked flawlessly in IE 5.5 but had terrible problems once IE 6 was loaded. Not sure if you can work around that or I coded something incorrectly.

    <pre>' Tools>References must include (1) Microsoft Internet Controls;
    ' (2) Microsoft HTML Object Library; this at top of module:
    Private Type tableData
    strDesc As String
    dblData As Double
    End Type
    'Declare Sleep API
    Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

    ' This inside a procedure:
    'Create browser object references
    Dim ieSrc As New InternetExplorer

    'Load web page
    With ieSrc
    .Visible = True 'show window and load page
    .navigate "http://myurl.com/"
    While Not .readyState = READYSTATE_COMPLETE
    Sleep 500 'wait 1/2 sec before trying again
    Wend
    End With

    'Create document object model references
    Dim ieDocSrc As MSHTML.HTMLDocument
    Set ieDocSrc = ieSrc.Document

    'Set up and parse the TABLES collections
    Dim colTables As Variant, aTable As HTMLTable
    Set colTables = ieDocSrc.all.tags("TABLE")
    If colTables.Length = 0 Then
    MsgBox "Catastrophic problem: no tables found!"
    GoTo shutDown
    End If

    ' Parse last table on the page
    Dim intRow As Integer, intColumn As Integer
    Dim intRows As Integer, intColumns As Integer
    Dim colCells As Variant, aCell As HTMLTableCell
    Dim trustArray() As tableData
    Set aTable = colTables.Item(colTables.Length - 1)
    intRows = aTable.all.tags("TR").Length
    intColumns = aTable.all.tags("TD").Length / intRows
    ReDim trustArray(intRows - 1)
    Set colCells = aTable.Cells
    For intRow = 0 To (intRows - 1)
    Set aCell = colCells((intRow * intColumns)) ' assumes no merged cells
    trustArray(intRow).strDesc = aCell.innerText
    Set aCell = colCells((intRow * intColumns) + 1)
    trustArray(intRow).dblData = _
    CDbl("0" & Replace(aCell.innerText, "$", vbNullString))
    Next 'intRow

    ' Now the trustArray contains all the useful data
    shutDown:
    'Clean up some of the bigger objects
    Set ieDocSrc = Nothing
    ieSrc.Quit
    Set ieSrc = Nothing</pre>

    If you're not familiar with accessing arrays of user-defined types, it's great:

    For intCounter = 0 To UBound(trustArray)
    If trustArray(intCounter).dblData <> 0 Then
    msgbox trustArray(intCounter).strDesc & " = " & _
    Format(trustArray(intCounter).dblData, "Currency")
    End If
    Next

    Of course, you don't need to do this in your case because you can stuff it right into your recordset. Hope this helps.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Thanks for posting that code. But there are two points I wish to make (one's a point the other a question):
    1. I meant if you go to that site and choose a venue then choose a race you will see it's a refresh type screen.
    2. Is the code you posted for Excel or Access?

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    > 1. I meant if you go to that site and choose a venue then choose a race you will see it's a refresh type screen.

    Ummm... you mean this page refreshes itself every so often? If so, I think you probably need to write code to check every so often. I don't think the browser can trigger the process from its end (unless you use one of the open source ones and arrange for that in the code).

    > 2. Is the code you posted for Excel or Access?

    Actually, it was originally embedded in a Word template, but it can be adapted to any VB(A) environment, as it uses only two "outside" (non-Office) libraries that should be installed with Internet Explorer.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Thanks Jefferson for all this.

    Where can I learn how to read in a previous table, this code currently reads the very last line as a table. As you can see there are a number of tables on this page.
    I guess I need to know how to use the code to address all the fields on this page.
    The page is http://www.tabracing.com.au/find.asp?URL=/...8/28/P1RR7.html

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    The line of code that points to the last table is this one:

    Set aTable = colTables.Item(colTables.Length - 1)

    You can assign aTable to refer to any table you like.

    To get a grip on which one you want, try placing a Stop statement on the line after you Set colTables. When you return to the de######, open the Locals window and expand colTables and explore the collection until you figure out the pattern and/or markers you'll need to write your code. This might be different for each page format, so take plenty of time to study them. If they change too often, you might have to adopt a different strategy.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Thanks Jefferson

    It is now working a treat for IE6.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    As I said before, thank you for the code. Did you write this code? It works fine on my IE6.
    Can you point me to the reference that describes how to use the object model (if that's what you call it).
    I had to change the code because the table has either 3 column entries or 8 column entries in the one table.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    I did indeed write that code. The documentation was basically Microsoft's web site, if you search for an object and method (or property), you should be able to find the DHTML section that goes over the document object model. It's well linked internally, but the tables expand and compress using JavaScript, so the load times can be a bit slow. Or should I say, after the page loaded, a second later, it may well rearrange itself before your eyes.

    Here are a couple pages from my history bar and a quick search:

    InternetExplorer Object (MSDN) - from "Programming and Reusing the Browser > WebBrowser Control > Reference for Visual Basic Developers"

    HTML and Dynamic HTML (MSDN) - top entry point for object model and reference material

    Also, search in MSDN for the "DTHML Dude" columns. I didn't see these before, but they offer a casual introduction that might be useful.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Screen Grab (Windows)

    Well the code works for me on my IE 6 machine, but it takes forever to load the page on a friend of mine's IE 6 and even then it does not load, it comes up with an error message. His IE6 is slightly different to mine, his is SP2 whereas mine is SP1.
    What problems did you encounter on your IE6?
    I haven't had a chance to look at those links yet, but I will.

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Screen Grab (Windows)

    I got a quick security prompt. I was pulling a document from our intranet, so it was a real head-scratcher. I ended up going straight to SQL Server in a new version. 'fraid I can't shed much more light.

Page 1 of 2 12 LastLast

Posting Permissions

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