VBA: Copy data/text from an existing open Internet Explorer tab

Question

I'm trying to write a VBA code which allows me to copy data from an existing open IE tab maybe by sending a CTRL+A & CTRL+C with SendKey method and then pasting it to a sheet on Excel. I have tried "Get Data from Web" but that doesn't work since I need to enter username/password and can't setup a getElementbyId lines. So I found some code, that scans the open tabs of an already active Internet explorer session, but I want to make VBA Switch to the tab that matched and Copy the text from this website.

Sub AAA()

    marker = 0
    Set objShell = CreateObject("Shell.Application")
    IE_count = objShell.Windows.Count
    For x = 0 To (IE_count - 1)
        On Error Resume Next    ' sometimes more web pages are counted than are open
        my_url = objShell.Windows(x).Document.Location
        my_title = objShell.Windows(x).Document.Title
        If my_url Like "http://www.cnn.com" & "*" Then 'compare to find if the desired web page is already open
            Set ie = objShell.Windows(x)
            marker = 1
            Exit For
        Else
        End If
    Next
    If marker = 0 Then
        MsgBox ("A matching webpage was NOT found")
    Else
        'Switch to the tab that matched and Copy the text from this website..
    End If

End Sub

Maybe you would be happier skipping the user interface copy-paste method and instead go to the temporary internet files and find the .html document instead. Tell me if you have already ruled this method out! — elliot svensson, Aug 01 '18 at 15:16
Could you please provide actual URL and expected output? That might be [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). — omegastripes, Aug 01 '18 at 15:19
@omegastripes, unfortunately I can't, it's an intranet address. elliotsvensson, I have not, I'm not sure how to do that. — sagittarius_88, Aug 01 '18 at 15:24
Anyway, you could share HTML content (the relevent part with cleaned up sensitive data), and expected output. — omegastripes, Aug 01 '18 at 15:26
How do I get the HTML content? I navigated to the temp internet folder. I don't see HTML type in the files — sagittarius_88, Aug 01 '18 at 15:32
why are you not already in the right window/tab? Have you clicked on something that has led you to a new window/tab? — QHarr, Aug 01 '18 at 15:40
Also check [this answer](https://stackoverflow.com/a/21490198/2165759). — omegastripes, Aug 01 '18 at 15:42
@Qharr, no I haven't. This is an already open webpage. I just can't switch to it and sendkey to "select all" on this particular tab — sagittarius_88, Aug 01 '18 at 15:43
And you can't navigate to this tab/webpage using IE because of login issues? — QHarr, Aug 01 '18 at 15:46
IMO better to refuse from `SendKeys` as most unreliable. Get a reference to the IE with opened workpage `Application` object, and work directly with DOM. — omegastripes, Aug 01 '18 at 15:48
@sagittarius_88 You can get the HTML content of the webpage by the following steps: in IE navigate to the target webpage, press F12, go to DOM Explorer tab, find root `` tag, right click on it, choose copy in context menu. Take a look at [this screenshot](https://i.stack.imgur.com/AeLgT.png). Then you may paste the copied HTML content to a notepad, make some cleanups if necessary, and share. — omegastripes, Aug 01 '18 at 17:57

score 1 · Accepted Answer · answered Aug 01 '18 at 15:05

He is one way to get the handle of the IE

Option Explicit

Sub Sample()
    Dim ShellApp As Object, ShellWindows As Object
    Dim objIE As Object, objWin As Object
    Dim ieCaption As String

    Set ShellApp = CreateObject("Shell.Application")
    Set ShellWindows = ShellApp.Windows()

    '~~> Loop through the windows and check if it is IE
    For Each objWin In ShellWindows
        If (Not objWin Is Nothing) Then
            ieCaption = objWin.Name
            If ieCaption = "Internet Explorer" Then
                Set objIE = objWin
                Exit For
            End If
        End If
    Next

    Set ShellApp = Nothing

    '~~> If IE is found then wirk with that object
    If Not objIE Is Nothing Then
        With objIE
            Debug.Print .Document.Body.Innerhtml
        End With
    End If
End Sub

VBA: Copy data/text from an existing open Internet Explorer tab

1 Answers1