Get table text from inside IE Browser Frame

[gypsyjoe11]

Hello Everyone,

My customer has to log-in to a website and enter a page with 2 frames in it. Inside of one of the frames is a table where they cut & paste the info to get it out. I'm trying to find a way to get that info for them once they see what they want on the screen. The problem is that the users are missing stuff when they cut & paste.

The table is inside of a frame and it doesn't list the table name. All the stuff I've found doesn't deal with info inside of frames. I found one entry here about it, but it talked about coding from inside IE which I can't do. They talked about referencing "Me.WebBrowser0.Document.documentElement.all" which doesn't work from inside of Access. I'm using access 2003 and IE6 by the way. Don't have a choice about it.

Below is the HTML source. I've xxxed out the paths for company privacy reasons.

<LINK REL="SHORTCUT ICON" HREF="/xxx/xxx.ico">
<frameset rows="82,*" border="0" frameborder="0">
<frame name="header" title="Portal Header" SRC="/xxx/xxx/xxx/?ICType=Script&ICScriptProgramName=WEBLIB_MENU.ISCRIPT3.FieldFormula.IScript_GetMenuHeader" FRAMEBORDER="0" marginheight="0" scrolling="no">
<frame name="main" title="Main Content" SRC="/xxx/xxx/xxx/?ICType=Script&ICScriptProgramName=WEBLIB_MENU.ISCRIPT3.FieldFormula.IScript_RedirectToDrillDown" FRAMEBORDER="0" marginheight="0">
</frameset>
</html>

Below is the test sub. All I've been able to do is find the correct webpage and then print it to the printer. I can't figure out how to do more than that.

Private Sub Test_Click()
    Debug.Print
    Debug.Print "---------------"
    Dim SWs As New SHDocVw.ShellWindows
    Dim vIE As SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    'Dim objExplorer As New SHDocVw.InternetExplorer
    'Dim objDoc As IHTMLDocument2
    'Dim IEframes As FramesCollection
 
    Set SWs = New SHDocVw.ShellWindows
    For Each vIE In SWs
        If Left(vIE.LocationURL, 27) = "[URL="https://hr.direct-access.us/"]https://hr.direct-access.us[/URL]" Then
            vIE.Visible = True
            Set HTMLDoc = vIE.Document
 
            Debug.Print vIE.LocationURL
        End If
    Next
 
    Debug.Print HTMLDoc.Frames(1).Print
End Sub

Any help would be greatly appreciated,

Joe

 

[darbid]

I am not an expert on VBA, IE or HTML but I have managed to work with a multiframed internet site with VBA and the Webbrowser Control.

IMHO frames are old as far as HTML is concerned but that does not help us. Think of a frame as its own HTML document and thus you must wait for the frame to be available.

Dim mywb As Object
Dim myHTMLDoc As HTMLDocument
Dim myHTMLFrame4 As HTMLDocument



'get the webbrowser - here mine is name wb_Name
Set mywb = Me.wb_Name

'get the main HTML Document
Set myHTMLDoc = mywb.Document

'get the Frame4 - here I am getting the 5th frame for example (i think they start a 0)
Set myHTMLFrame4 = myHTMLDoc.frames(4).Document

'maybe this waits for the document to be ready - good luck I never did get frames working properly for the websites I was working with.
'there are other methods you will find other than this one.
Do Until myHTMLFrame4.readyState = "complete"
DoEvents
Loop

'this will show you the outerHTML and I imagine your table.
Debug.Print myHTMLFrame4.documentElement.outerHTML

Once you have your frame then you just treat it like any other HTMLDocument and get the table out of it.

 

[gypsyjoe11]

Thanks Darbid. Your suggestion steered me in the right direction.

In my case I don't have to wait for elements to be ready because the user has to login to the site first. Also since I'm making a IE object from an already open page I can't use "me."

I kept modifying the code below to print out the objects until I found what I needed:

Dim ieObject As Object
For Each ieObject In myHTMLDoc.all
    Debug.Print TypeName(ieObject)
Next ieObject

The problem with mine was that the table was 2 frames deep inside the main HTML document.

The below code takes the table from the HTML Document and then opens up a new Excel sheet and copies it there.

Private Sub Magic_Man_Click()
  'find the right internet explorer webpage
    Dim allExplorerWindows As New SHDocVw.ShellWindows
    Set allExplorerWindows = New SHDocVw.ShellWindows
 
    Dim IEwindow As SHDocVw.InternetExplorer
 
    For Each IEwindow In allExplorerWindows
        If Left(IEwindow.LocationURL, 27) = "[URL="https://hr.direct-access.us/"]https://hr.direct-access.us[/URL]" Then
            Exit For    'found the right IE window URL
        End If
    Next
 
    IEwindow.Visible = True      'set IE window to active
  'end find
 
    'set myHTMLDoc to the main pages IE document
    Dim myHTMLDoc As HTMLDocument
    Set myHTMLDoc = IEwindow.Document
 
    'set myHTMLFrame2 as the 2nd frame of the main page (index starts at 0)
    Dim myHTMLFrame2 As HTMLDocument
    Set myHTMLFrame2 = myHTMLDoc.Frames(1).Document
 
    'set myHTMLFrame2_3 as the 3rd frame of myHTMLFrame2
    Dim myHTMLFrame2_3 As HTMLDocument
    Set myHTMLFrame2_3 = myHTMLFrame2.Frames(2).Document
 
    'get all the tables in myHTMLFrame2_3
    Dim allTables As Object
    Set allTables = myHTMLFrame2_3.getElementsByTagName("Table")
 
    'set myTable as the 3rd table in allTables
    Dim myTable As HTMLTable
    Set myTable = allTables(2)
 
 'Create new Excel object
    Dim xlObj As Excel.Application
    Dim xlWkbk As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim xlRow As Integer
    Dim xlCol As Integer
    Set xlObj = GetObject("", "Excel.Application")
    Set xlWkbk = xlObj.Workbooks.Add
    Set xlSht = xlWkbk.ActiveSheet
    xlObj.Visible = True
  'end create new Excel Object
 
  'copy the cells over to the Excel sheet
    xlSht.Cells(1, 1).Activate
 
    For xlRow = 1 To myTable.Rows.Length
        For xlCol = 1 To myTable.Rows(xlRow - 1).Cells.Length
            xlSht.Cells(xlRow, xlCol) = myTable.Rows(xlRow - 1) _
                .Cells(xlCol - 1).innerText
        Next xlCol
    Next xlRow
  'end copy
 
  'Excel sheet appearance formatting
    xlSht.Cells.NumberFormat = "@"
    xlSht.Cells.HorizontalAlignment = xlLeft
    xlSht.Cells.VerticalAlignment = xlCenter
    xlSht.Cells.Orientation = 0
    xlSht.Cells.AddIndent = False
    xlSht.Cells.ShrinkToFit = False
    xlSht.Cells.ReadingOrder = xlContext
    xlSht.Cells.MergeCells = False
 
    xlSht.Cells.Font.Name = "Arial"
    xlSht.Cells.Font.ColorIndex = 0
    xlSht.Cells.Font.Bold = False
    xlSht.Cells.Font.Italic = False
    xlSht.Cells.Font.Size = 10
    xlSht.Cells.RowHeight = 16
    xlSht.Cells.Borders.LineStyle = xlContinuous
    xlSht.Cells.Borders.Color = vbBlue
 
    xlSht.Cells.Columns.AutoFit
 
  'end Excel sheet appearance formatting
 
    xlObj.Cells(2, 2).Select
 
''''''BELOW WAS USED FOR FINDING THE CORRECT HTML ELEMENTS'''''''''
    'print the all the types for all the objects in the document
    'Dim ieObject As Object
    'For Each ieObject In myHTMLDoc.Frames(1).Document.all
    '    Debug.Print TypeName(ieObject)
    'Next ieObject
End Sub

Thanks again for pointing me in the right direction,

Joe

 

[darbid]

Hey that looks great. I am glad to help as I was just picking up on the threads with 0 responses.

Me. is what you can use if you use the Webbrowser Control which allows you to have IE on a form in access. I use this. IE is no different you just need to get the IE object.

If you wanted to be fancy you could use this control and log your users in automatically and get this for you.

There is also another way to get tables from HTML into excel it is done from excel with "QueryTables.Add".

But you method i think is safer as long as you are prepared to update it when they change the webpage.