Screen Scraping

[ckrows]

I have been trying to find a sample of this on the internet and i cannot.

Scenario: I have an Oracle program, it has a logon screen with username and password, 2nd screen i need to enter a "1" to get to the screen i am looking for.

From there, i just want to scrape all or some of the information from that screen into my Access. I won't get access to the tables directly otherwise that is the route i would have taken.
Can anyone help or point me to a tutorial on this type of stuff?

Thanks, Much appreciated.

Ashe

[NeoPa]

It appears to me that screen scraping is a facility not native either to Access or VBA. As such I don't feel this is the right place for this question.

[georges50]

Here is some VBA code that will grab an open IE Browser window on your desktop and return the document so that it can be scraped. Use the Tagnames and Innertext fields. You will need to handle frames on the page specially.
This should get you going

Sub TstdcIEURL()
Dim myObj As Object, ObjDoc As Object
Dim myTagName As String, myInnerText As String
    'Set myObj = dcIEURL("replace with desired IE domain name", "optional parameter, replace with Location name")
    Set ObjDoc = myObj.Document
    For i = 1 To ObjDoc.all.length
        myTagName = ObjDoc.all.Item(i).tagName
        myInnerText = ObjDoc.all.Item(i).innerText
        Debug.Print myTagName, myInnerText
        Stop
    Next i
    Stop
End Sub
Public Function dcIEURL(zDomainName, Optional zLocName As String) As Object
'zDomainName is the IE window with data you want to screen scrape, LocName is optional and a further qualification of the window data
On Error GoTo ErrHandler
Dim objShellWins As SHDocVw.ShellWindows
Dim ObjIE As SHDocVw.InternetExplorer
Dim ObjDoc As Object
Dim myInnerText As String
Dim myNode As String, AccountID As String
Dim i As Integer, myLeft As String, myRight As String
Dim strOut As String, mySearch As String
Dim intFree As Integer, myURL As String
Dim myURLDomainInspecting As String, myURLDomainSeeking As String
Dim myLocNameInspecting As String, myLocNameSeeking As String
Dim myIE As Variant

    
    ' Instantiate
    Set objShellWins = New SHDocVw.ShellWindows
    ' There might be multiple IE windows open
    If objShellWins.Count > 0 Then
        myURLDomainSeeking = zDomainName
        If IsMissing(zLocName) Then
            myLocNameSeeking = ""
        Else
            myLocNameSeeking = zLocName
        End If
        For Each ObjIE In objShellWins
            With ObjIE
                ' Try to locate the browser with a specific address in it's AddressBar.
                myURLDomainInspecting = GetDomain(.LocationURL)
                myLocNameInspecting = ObjIE.LocationName
                Select Case myURLDomainInspecting
                    Case Is = myURLDomainSeeking    'Domains are equal here
                        ' URL points to one passed to this function
                        If myLocNameSeeking <> "" Then   'Bypass if no loc name to find
                            Select Case myLocNameInspecting
                                Case Is = ""            'LocName not present, use URL
                                    Set myIE = ObjIE
                                    Exit For
                                Case Is = myLocNameSeeking
                                    Set myIE = ObjIE
                                    Exit For
                            End Select
                        Else
                            Set myIE = ObjIE
                            Exit For
                        End If
                    Case Else
                        If Left(myURLDomainSeeking, 6) = "rodweb" Then
                            If Left(myURLDomainInspecting, 6) = "rodweb" Then
                                Set myIE = ObjIE
                                Exit For
                            End If
                        End If
                End Select
            End With
        Next
    End If
If IsObject(myIE) = False Then
    Set myIE = CreateObject("InternetExplorer.Application")
    myIE.Width = 1200
    myIE.Height = WinHeight
    myIE.Left = 1
    myIE.Top = 1
    myIE.Visible = True
End If
Set dcIEURL = myIE
ExitHere:
    On Error Resume Next
    Close #intFree
    Set ObjDoc = Nothing
    Set ObjIE = Nothing
    Set objShellWins = Nothing
    Exit Function
ErrHandler:
    With Err
        MsgBox "Error: " & .Number & vbCrLf & .Description, _
            vbCritical Or vbOKOnly, .Source
    End With
    Resume ExitHere

End Function
Sub TstdcIEURL()
Dim myObj As Object, ObjDoc As Object
Dim myTagName As String, myInnerText As String
    'Set myObj = dcIEURL("replace with desired IE domain name", "optional parameter, replace with Location name")
    Set myObj = dcIEURL("ecf.nceb.uscourts.gov")
    Set ObjDoc = myObj.Document
    For i = 1 To ObjDoc.all.length
        myTagName = ObjDoc.all.Item(i).tagName
        myInnerText = ObjDoc.all.Item(i).innerText
        Debug.Print myTagName, myInnerText
        Stop
    Next i
    Stop
End Sub
Public Function dcIEURL(zDomainName, Optional zLocName As String) As Object
'zDomainName is the IE window with data you want to screen scrape, LocName is optional and a further qualification of the window data
On Error GoTo ErrHandler
Dim objShellWins As SHDocVw.ShellWindows
Dim ObjIE As SHDocVw.InternetExplorer
Dim ObjDoc As Object
Dim myInnerText As String
Dim myNode As String, AccountID As String
Dim i As Integer, myLeft As String, myRight As String
Dim strOut As String, mySearch As String
Dim intFree As Integer, myURL As String
Dim myURLDomainInspecting As String, myURLDomainSeeking As String
Dim myLocNameInspecting As String, myLocNameSeeking As String
Dim myIE As Variant

    
    ' Instantiate
    Set objShellWins = New SHDocVw.ShellWindows
    ' There might be multiple IE windows open
    If objShellWins.Count > 0 Then
        myURLDomainSeeking = zDomainName
        If IsMissing(zLocName) Then
            myLocNameSeeking = ""
        Else
            myLocNameSeeking = zLocName
        End If
        For Each ObjIE In objShellWins
            With ObjIE
                ' Try to locate the browser with a specific address in it's AddressBar.
                myURLDomainInspecting = GetDomain(.LocationURL)
                myLocNameInspecting = ObjIE.LocationName
                Select Case myURLDomainInspecting
                    Case Is = myURLDomainSeeking    'Domains are equal here
                        ' URL points to one passed to this function
                        If myLocNameSeeking <> "" Then   'Bypass if no loc name to find
                            Select Case myLocNameInspecting
                                Case Is = ""            'LocName not present, use URL
                                    Set myIE = ObjIE
                                    Exit For
                                Case Is = myLocNameSeeking
                                    Set myIE = ObjIE
                                    Exit For
                            End Select
                        Else
                            Set myIE = ObjIE
                            Exit For
                        End If
                    Case Else
                        If Left(myURLDomainSeeking, 6) = "rodweb" Then
                            If Left(myURLDomainInspecting, 6) = "rodweb" Then
                                Set myIE = ObjIE
                                Exit For
                            End If
                        End If
                End Select
            End With
        Next
    End If
If IsObject(myIE) = False Then
    Set myIE = CreateObject("InternetExplorer.Application")
    myIE.Width = 1200
    myIE.Height = WinHeight
    myIE.Left = 1
    myIE.Top = 1
    myIE.Visible = True
End If
Set dcIEURL = myIE
ExitHere:
    On Error Resume Next
    Close #intFree
    Set ObjDoc = Nothing
    Set ObjIE = Nothing
    Set objShellWins = Nothing
    Exit Function
ErrHandler:
    With Err
        MsgBox "Error: " & .Number & vbCrLf & .Description, _
            vbCritical Or vbOKOnly, .Source
    End With
    Resume ExitHere

End Function

[RedSon]

You can just as easily get this information from copying and pasting the open browser window.

Screen scraping is generally frowned upon since you are using another companies data for your own purposes. As long as you are using it for academic purposes then it shouldn't be a problem.