VBA Macro For Already Open IE Window

[RL101]

Hi all,

I'm looking to create a VBA macro which will be ran from Excel (2003 or 2010) & which works with Internet Explorer. I have added 'Microsoft Internet Controls' to VBA References, and have looked at plenty of VBA code on the forum which opens & then navigates to specific websites.

However, what I need is for this macro to affect an already open IE window.

Does anyone have an idea of the syntax / command to have excel "select" a specific IE window already open?

Many thanks!

 

[sevape]

Hi,

Just wanted to exress my gratitud towards this post, been searchingh and testin various ways but nothing worked until I stumbled across this... Thank You!

 

[ZVI]

Hi,

Just wanted to exress my gratitud towards this post, been searchingh and testin various ways but nothing worked until I stumbled across this... Thank You!

My pleasure, sevape!
Welcome to the MrExcel Board!

 

[hselvik]

Hi. I need some help writing a VBA-code for a command-button in an Excel-sheet I am working on, and figured I could try this forum. I am not sure if this action is possible, but I am hoping for a code that opens a web-page, adds information from the Excel-sheet into a search-field on this web-page, and performs this search. Is this possible?

 

[Mepwnz2010]

Clicking button on web page is a bit out of the scope of this thread - better to create the new thread for this.
Commonly code of each web page is almost unique - there are no strict rules for the parsing.
Sometimes code for clicking button on web-page can look like these (depends on HTML code of web site):
IE.Document.GetElementsById("bottomButtonRow").Item(0).InvokeMember("submit")
or
IE.Document.Forms.GetElementsByName("save").Item(0).InvokeMember("submit")
or
IE.Document.GetElementsByName("save").Item(0).Click
or
IE.Document.GetElementsById("bottomButtonRow").Item(0).Click
...

Hey ZVI this reply was awesome, and this thread in general took me down the path I needed to learn a lot. So thank you... You the man! Got another question for you now. Do you think the get IE by title code could be made to work with google chrome?

 

[ZVI]

Open in Excel all linked workbooks and save each workbook via 'File - Save As' onto required folder.
After that delete old copies of workbooks from their previous folders.

 

[ZVI]

Open in Excel all linked workbooks and save each workbook via 'File - Save As' onto required folder.
After that delete old copies of workbooks from their previous folders.

Wrong posting - I'm apologising, it was for another thread.

Do you think the get IE by title code could be made to work with google chrome?

I am not using Google Chrome.
Surely the handling of window by its title in VBA is possible with involving of API-code.
But in VBA you can't get Google Chrome object similar to IE with its methods & properties.

 

[Pegaso]

ZVI,


Thanks for your help in answering all of those questions regarding the subject of controlling already existing IE windows.


I'm building a more complex code, but in a nutshell, what I need is to get the HTML code of the page referred in the code, copy and save it as a .txt file, this is what I have:

'Creates the links to access the files in each vault
'Opens each external link, creates a list with all the files and local links for each one


Sub Main()


Dim i As Integer
Dim s As Integer
Dim t As Integer
Dim VaultMin As Integer
Dim VaultMax As Integer
Dim NumRec As Integer
Dim NumTot As Integer
Dim Path As String
Dim PathName As String
Dim fs As Object
Dim IE As Object
Dim objDoc As Object
Dim NPOFile$, myFile$
Dim f As Object
Dim WB1 As Workbook
Dim WB2 As Workbook


Sheets("Summary").Activate
Set WB1 = ThisWorkbook
VaultMin = Cells(2, 2)
VaultMax = Cells(2, 4)


For t = VaultMin To VaultMax
    
    'Section 1 of 3: Create vault URL, open, grab HTML code
    
    Cells(2, 5) = t
    VaultURL = Cells(2, 6)
    VaultPath = Cells(3, 6)
    VaultName = "HTML-" & t & ".txt"
    
    Set fs = CreateObject("Scripting.FileSystemObject") 'This creates the .txt to store the webpage code of the page to open
    Set a = fs.CreateTextFile(VaultPath & VaultName, True)
    a.Close
    
    'ZVI's code starts here!
  
    With CreateObject("Shell.Application").Windows
    
        If .Count > 0 Then
          ' Get IE
          Set IE = .Item(0) ' or .Item(.Count - 1)
        Else
          ' Create IE
          Set IE = CreateObject("InternetExplorer.Application")
          IE.Visible = True
        End If
  
    End With
    
    Range(Cells(2, 6), Cells(2, 6)).Select 'This is where the hyperlink resides in the excel sheet
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    
    'Wait for page to load!
    
    Do
    If IE.ReadyState = 4 Then
    IE.Visible = True
    Exit Do
    Else


    DoEvents
    End If
    Loop


    Set objDoc = IE.Document
    strMyPage = IE.body.innerHTML 'This line gives error code 438, "Object doesn't support this property or method
    strMyPage = IE.innerHTML
    
    NPOFile = "NotePad.exe " & myFile


    'Work with file [Append Text to File].
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(VaultPath & VaultName, 8, TristateUseDefault)


    f.Write Chr(9) & strMyPage
    f.Close
    
Next t


End Sub

I'm getting an error here:

strMyPage = IE.body.innerHTML 'This line gives error code 438, "Object doesn't support this property or method

Thanks in advance for your help.

Rgds!

Pegaso

 

[ZVI]

Hi Pegaso,

The problem is at least here: strMyPage = IE.body.innerHTML
becuase Body is a child of Document but not of IE object directly.
Use this: strMyPage = IE.Document.Body.innerHTML

To simplify your task try using the shown below function GetBodyInnerHTML.
It returns innerHTML of the web page referenced in hyperlink of the passed cell.
Put its code to the module where your Main subroutine resides and then just call it from your code like this:

Sub Main()
 
  ' ... Your code
 
  ' ZVI's code starts here! (the single line now)
   strMyPage = GetBodyInnerHTML(Range("F2"))
 
  ' Work with file [Append Text to File]
  ' ... Your code
 
End Sub

Sub Test()
  Dim strMyPage As String
  ' Put Body's nnerHTML to the strMyPage variable
  strMyPage = GetBodyInnerHTML(Range("F3"))
  ' Print strMyPage into Immediate window of VBE
  Debug.Print strMyPage
End Sub
 
Function GetBodyInnerHTML(Cell As Range, Optional WaitSeconds As Long = 10) As String
' ZVI:2015-04-20 Returns InnerHTML of the web page referenced in the hyperlink of the Cell
' (already open IE window is used if exists)
' Arguments:
'   Cell        - the cell with hyperlink to the web page
'   WaitSeconds - (optional) timeout in seconds for IE navigation
  Dim IE As Object
  Dim t As Single
  Dim Url As String
  With CreateObject("Shell.Application").Windows
    If .Count > 0 Then
      ' Get IE
      Set IE = .Item(0)  ' or .Item(.Count - 1)
    Else
      ' Create IE
      Set IE = CreateObject("InternetExplorer.Application")
      IE.Visible = True
    End If
  End With
  ' Get url from the cell
  If Cell.Hyperlinks.Count > 0 Then
    ' Hyperlink object is present in the cell
    Url = Cell.Hyperlinks(1).Address
  Else
    ' May be hyperlink is in the cell's value (text)
    Url = Cell.Value
  End If
  ' Do navigation
  IE.Navigate Url
  ' Charge the timeout
  t = Timer + WaitSeconds
  ' Wait for "IE is ready" state
  While IE.ReadyState <> 4 And Timer < t: DoEvents: Wend
  ' Wait for "IE.Document is completely downloaded" state
  If Timer < t Then
    While IE.Document Is Nothing And Timer < t: DoEvents: Wend
  Else
    Err.Raise vbObjectError + 513, , "Timeout happens: " & WaitSeconds & " seconds"
  End If
  ' Return Body's innerHTML
  GetBodyInnerHTML = IE.Document.Body.innerHTML
  ' Clear the memory of object variable
  Set IE = Nothing
End Function

Regards

 

[Pegaso]

ZVI,


Thanks for your quick answer.

Hi Pegaso,


The problem is at least here: strMyPage = IE.body.innerHTML
becuase Body is a child of Document but not of IE object directly.
Use this: strMyPage = IE.Document.Body.innerHTML

I replaced strMyPage = IE.body.innerHTML with strMyPage = IE.Document.Body.innerHTML, but it didn't work, "Error 483, Object doesn't support property or method". That didn't surprise me since you mentioned that there could be other errors around,


Then I used your suggestion using GetBodyInnerHTML, but unfortunately the error persists, the line:

GetBodyInnerHTML = IE.Document.Body.innerHTML

Gives me the same error


Any ideas?


Again, thanks for your help!


Pegaso

 

[ZVI]

Well, then please copy all the below updated code into VBA module and run Test1 subroutine.
If it works then try new GetBodyInnerHTML with your workbook.
If it does not work then what will happen if you click hyperlink of F2 cell in testing sheet which is created by Test1 subroutine?

Sub Test1()
  Dim strMyPage As String
  ' Create new worbook for testing
  With Workbooks.Add
    With Sheets(1)
      ' Prepare testing sheet
      .Activate
      .Name = "Summary"
      ' Add hyperlink for the testing
      .Hyperlinks.Add Anchor:=.Range("F2"), _
                      Address:="http://www.mrexcel.com/forum/faq.php", _
                      ScreenTip:="MrExcel Message Board FAQ", _
                      TextToDisplay:="MrExcel FAQ"
    End With
  End With
  ' Put Body's innerHTML to the strMyPage variable
  strMyPage = GetBodyInnerHTML(Range("F2"))
  ' Print strMyPage into Immediate window of VBE
  Debug.Print strMyPage
End Sub
 
 
Function GetBodyInnerHTML(Cell As Range, Optional WaitSeconds As Long = 10) As String
  ' ZVI:2015-04-20 http://www.mrexcel.com/forum/excel-questions/553580-visual-basic-applications-macro-already-open-ie-window-2.html
  ' Arguments:
  '   Cell        - the cell with hyperlink to the web page
  '   WaitSeconds - (optional) timeout in seconds for IE navigation
  ' Returns InnerHTML of the web page referenced in the hyperlink of the Cell
  ' (already open IE window is used if exists)
  Dim IE As Object, w As Object
  Dim t As Single
  Dim Url As String
  For Each w In CreateObject("Shell.Application").Windows
    If w.Name = "Windows Internet Explorer" Then
      ' Get IE
      'Debug.Print "IE is already open" ' <-- for testing
      Set IE = w
      Exit For
    End If
  Next
  If IE Is Nothing Then
    ' Create IE
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Silent = True
    IE.Visible = True
    'Debug.Print "New IE" ' <-- for testing
  End If
  ' Get url from the cell
  If Cell.Hyperlinks.Count > 0 Then
    ' Hyperlink object is present in the cell
    Url = Cell.Hyperlinks(1).Address
  Else
    ' May be hyperlink is in the cell's value (text)
    Url = Cell.Value
  End If
  ' Do navigation
  IE.Navigate Url
  ' Charge the timeout
  t = Timer + WaitSeconds
  ' Wait for "IE is ready" state
  While IE.ReadyState <> 4 And Timer < t: DoEvents: Wend
  ' Wait for "IE.Document is completely downloaded" state
  If Timer < t Then
    While IE.Document Is Nothing And Timer < t: DoEvents: Wend
  Else
    Err.Raise vbObjectError + 513, , "Timeout happens: " & WaitSeconds & " seconds"
  End If
  ' Return Body's innerHTML
  GetBodyInnerHTML = IE.Document.Body.innerHTML
  ' Clean the memory of object variables
  Set w = Nothing
  Set IE = Nothing
End Function