VBA Macro For Already Open IE Window

RL101

New Member
Joined
May 29, 2011
Messages
1
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Thanks Rory for posting the Solution. It worked and now i am not getting any Compilation error. However, my problem is still not resolved.
When i checked for the shell window, it is not shell window with no different URL attached, it is Just a pop-up page with no URL and i need to access the same.
The pop-up page is getting appear from the below code (DO-Modal DIV).
Code:
<div class="PSMODAL" id="pt_modals" style="display: block; background-color: transparent;">
	<div class="popupDragFrame" id="ptModalShadow" style="cursor: nw-resize;"> 
	</div><div id="ptMod_0" style="z-index: 9999;">
		<div class="PSMODALTABLE" id="ptModTable_0" style="left: 270.5px; top: 25px; width: 859px; height: 120px;">
			<div class="PSMODALHEADER" id="ptModHeader_0" style="display: block;">
			<div class="PSMODALCONTENT" id="ptModContent_0"> 	
			<div class="PSMODALBOTTOM" id="ptModBottom_0" style="height: 14px; display: block;">
		</div>
	</div>	
</div>
I have written the below code but now stuck to access the popup page. Please help

Code:
Sub Collector_Proc()
 
'Variable Declaration
Dim IE As Object
Dim AJ As String
Dim K As String
Dim rl As String
'Set iePopup = CreateObject("InternetExplorer.application")
Dim shell As ShellWindows
'Variable Initialization
Set shell = New ShellWindows
Set IE = CreateObject("InternetExplorer.application")
Set sh = ThisWorkbook.Sheets("Collector")
Set CR = ThisWorkbook.Sheets("Credentials")
'URL capture and Navigation
AJ = CR.Range("B5").Value
IE.Navigate AJ
IE.Visible = True
'IE processing wait logic
Application.Wait (Now + TimeValue("00:00:03"))
While IE.Busy = True
           DoEvents
      Wend
With IE
    Do Until .readyState = 4: DoEvents: Loop
    
End With
      
'Credentials Passing and processing
IE.document.getelementbyid("userid").Focus
IE.document.getelementbyid("userid").Value = ""
IE.document.getelementbyid("userid").Value = CR.Cells(1, 2).Value
IE.document.getelementbyid("pwd").Focus
IE.document.getelementbyid("pwd").Value = CR.Cells(2, 2).Value
Set tags = IE.document.getElementsByTagName("input")
For Each tagx In tags
    If tagx.Value = "Sign In" Then
        tagx.Click
        Exit For
    End If
Next
'IE processing wait logic
Application.Wait (Now + TimeValue("00:00:03"))
        While IE.Busy = True
            DoEvents
        Wend
With IE
    Do Until .readyState = 4: DoEvents: Loop
End With

'Calculation of number of UserID's to process
sh.Cells(2, 6).Value = sh.UsedRange.Rows.Count - 1
'Loop for each user ID
For i = 2 To sh.UsedRange.Rows.Count
    If sh.Cells(i, 1).Value <> "" Then
        If sh.Cells(i, 2).Value <> "Complete" Then
            Set varitm = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.getelementbyid("OPRID_VW_OPRID")
                    varitm.Focus
                    varitm.Value = ""
                    varitm.Value = sh.Cells(i, 1).Value
            Set varitm1 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.getelementbyid("#ICSearch")
                    varitm1.Focus
                    varitm1.Click
            'IE processing wait logic
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
            With IE
                Do Until .readyState = 4: DoEvents: Loop
            End With
            'Selecting GL Link
            Set varitm2 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_LINKS_OPR_DEF_LINK_GL1")
                    varitm2.Focus
                    varitm2.Click
            'IE processing wait logic
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
            With IE
                Do Until .readyState = 4: DoEvents: Loop
            End With
            ' Re-setting up GL configuration
            Set varitm3 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_LEDGER")
                    varitm3.Focus
                    varitm3.Value = ""
            Set varitm4 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_LEDGER_GROUP")
                    varitm4.Focus
                    varitm4.Value = ""
            Set varitm5 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_SOURCE")
                    varitm5.Focus
                    varitm5.Value = ""
            Set varitm6 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_COPY")
                    varitm6.Focus
                    varitm6.Click
            Set varitm7 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_DELETE_CONTROL")
                    varitm7.Focus
                    varitm7.Click
            Set varitm8 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_UNPOST_FLG")
                    varitm8.Focus
                    varitm8.Click
            Set varitm9 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_SKIP_SUMLED_UPD_SW")
                    varitm9.Focus
                    varitm9.Click
            Set varitm10 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_SKIP_ESSBASE_LOAD")
                    varitm10.Focus
                    varitm10.Click
            'Return to main Tab
            Set varitm10 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("ICTAB_0")
                    varitm10.Focus
                    varitm10.Click
                    'IE processing wait logic
                    Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
                    
                    With IE
                        Do Until .readyState = 4: DoEvents: Loop
                        
                    End With
            'Selecting Procurement Link
            Set varitm11 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_LINKS_OPR_DEF_LINK_PM1")
                    varitm11.Focus
                    varitm11.Click
                    'IE processing wait logic
                    Application.Wait (Now + TimeValue("00:00:03"))
                            While IE.Busy = True
                                DoEvents
                            Wend
                            
                    With IE
                        Do Until .readyState = 4: DoEvents: Loop
                        
                    End With
            'clicking Sub-Procurement Link---Open POPUP Window
            Set varitm12 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_PM_WRK_OPR_DEF_APVCHR_PB")
                    varitm12.Focus
                    varitm12.Click
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
                    
            With IE
                Do Until .readyState = 4: DoEvents: Loop
                
            End With
'Debug.Print IE.LocationName, IE.LocationURL, IE.AddressBar, IE.Application

            For Each IE In shell
            'Print URL for Each Window but only one URL is getting printed as POPUP window dont have URL associated, it is getting open from DO-Modal
            sh.Cells(i + 7, 5).Value = IE.LocationURL
            Exit For
            Next

Please help. This is very necessary for me.

Regards:
Aditya jain
 
Upvote 0
Sorry, but HTML Code is not getting upload. Trying with Tags.
Below is code from HTML
HTML:
<div class="PSMODAL" id="pt_modals" style="display: block; background-color: transparent;">
	<div class="popupDragFrame" id="ptModalShadow" style="cursor: nw-resize;"> 
	</div><div id="ptMod_0" style="z-index: 9999;">
		<div class="PSMODALTABLE" id="ptModTable_0" style="left: 270.5px; top: 25px; width: 859px; height: 120px;">
			<div class="PSMODALHEADER" id="ptModHeader_0" style="display: block;">
			<div class="PSMODALCONTENT" id="ptModContent_0">	
			<div class="PSMODALBOTTOM" id="ptModBottom_0" style="height: 14px; display: block;">
		</div>
	</div>	
</div>
 
Upvote 0
Hi,
I'm not certain in the goal of finding IE windows in your code
but to list all open IE windows you may use this part of the code
Rich (BB code):
  Dim w As Object
  For Each w In CreateObject("Shell.Application").Windows
    If w.Name Like "*Internet Explorer" Then
      Debug.Print w.LocationURL
    End If
  Next
 
Upvote 0
Hi Zvi,

Thanks for response. Let me elaborate some more. Upon executing the below code from VBA,
Code:
'clicking Sub-Procurement Link---Open POPUP Window
            Set varitm12 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_PM_WRK_OPR_DEF_APVCHR_PB")
                    varitm12.Focus
                    varitm12.Click

A popup window appear which is not associated with any URL. It is getting appear through the PSMODAL page functionality (just like submit/cancel popup page)
and there is no URL Bar. Code for html is provided below.

HTML:
<div class="PSMODAL" id="pt_modals" style="display: block; background-color: transparent;">
	<div class="popupDragFrame" id="ptModalShadow" style="cursor: nw-resize;"> 
	</div><div id="ptMod_0" style="z-index: 9999;">
		<div class="PSMODALTABLE" id="ptModTable_0" style="left: 270.5px; top: 25px; width: 859px; height: 120px;">
			<div class="PSMODALHEADER" id="ptModHeader_0" style="display: block;">
			<div class="PSMODALCONTENT" id="ptModContent_0"> 	
			<div class="PSMODALBOTTOM" id="ptModBottom_0" style="height: 14px; display: block;">
		</div>
	</div>	
</div>

The Popup page which is getting appear, having multiple setups, I need to perform the setup on the popup page, then close the popup page and return to main window.
So the Sequence is like,
Main window > Click on Link > POPUP page> Control transfer to popup page > do modification on POPUP Page > Save POPUP page> Close POPUP page> Conrol to main Page.

Please suggest.

Regards:
Aditya Jain
 
Upvote 0
A popup window appear which is not associated with any URL. It is getting appear through the PSMODAL page functionality (just like submit/cancel popup page) and there is no URL Bar.
Even there is no visible URL bar it's one of the IE windows.
Apply the suggested code of the post #94 to your code, then check URL of the popup window printed in the Immediate window of VBE.
After that just filter that URL in the code by if-then-else.
And use: Set IE = w or just use w object for further parsing (which is out of this thread goal as for me)
 
Last edited:
Upvote 0
Thanks Zvi, I incorporated the code you provided but the "for" loop is getting executed only once and giving the output as the parent URL at immidiate window of VBE.
Code:
Sub Collector_Proc()
 
'Variable Declaration
Dim IE As Object
Dim AJ As String
Dim K As String
Dim rl As String
Dim w As Object
'Set iePopup = CreateObject("InternetExplorer.application")
Dim shell As ShellWindows
'Variable Initialization
Set shell = New ShellWindows
Set IE = CreateObject("InternetExplorer.application")
Set sh = ThisWorkbook.Sheets("Collector")
Set CR = ThisWorkbook.Sheets("Credentials")
'URL capture and Navigation
AJ = CR.Range("B5").Value
IE.navigate AJ
IE.Visible = True
'IE processing wait logic
Application.Wait (Now + TimeValue("00:00:03"))
While IE.Busy = True
           DoEvents
      Wend
With IE
    Do Until .readyState = 4: DoEvents: Loop
    
End With
      
'Credentials Passing and processing
IE.document.getelementbyid("userid").Focus
IE.document.getelementbyid("userid").Value = ""
IE.document.getelementbyid("userid").Value = CR.Cells(1, 2).Value
IE.document.getelementbyid("pwd").Focus
IE.document.getelementbyid("pwd").Value = CR.Cells(2, 2).Value
Set tags = IE.document.getElementsByTagName("input")
For Each tagx In tags
    If tagx.Value = "Sign In" Then
        tagx.Click
        Exit For
    End If
Next
'IE processing wait logic
Application.Wait (Now + TimeValue("00:00:03"))
        While IE.Busy = True
            DoEvents
        Wend
With IE
    Do Until .readyState = 4: DoEvents: Loop
End With

'Calculation of number of UserID's to process
sh.Cells(2, 6).Value = sh.UsedRange.Rows.Count - 1
'Loop for each user ID
For i = 2 To sh.UsedRange.Rows.Count
    If sh.Cells(i, 1).Value <> "" Then
        If sh.Cells(i, 2).Value <> "Complete" Then
            Set varitm = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.getelementbyid("OPRID_VW_OPRID")
                    varitm.Focus
                    varitm.Value = ""
                    varitm.Value = sh.Cells(i, 1).Value
            Set varitm1 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.getelementbyid("#ICSearch")
                    varitm1.Focus
                    varitm1.Click
            'IE processing wait logic
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
            With IE
                Do Until .readyState = 4: DoEvents: Loop
            End With
            'Selecting GL Link
            Set varitm2 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_LINKS_OPR_DEF_LINK_GL1")
                    varitm2.Focus
                    varitm2.Click
            'IE processing wait logic
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
            With IE
                Do Until .readyState = 4: DoEvents: Loop
            End With
            ' Re-setting up GL configuration
            Set varitm3 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_LEDGER")
                    varitm3.Focus
                    varitm3.Value = ""
            Set varitm4 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_LEDGER_GROUP")
                    varitm4.Focus
                    varitm4.Value = ""
            Set varitm5 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_FS_SOURCE")
                    varitm5.Focus
                    varitm5.Value = ""
            Set varitm6 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_COPY")
                    varitm6.Focus
                    varitm6.Click
            Set varitm7 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_DELETE_CONTROL")
                    varitm7.Focus
                    varitm7.Click
            Set varitm8 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_UNPOST_FLG")
                    varitm8.Focus
                    varitm8.Click
            Set varitm9 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_SKIP_SUMLED_UPD_SW")
                    varitm9.Focus
                    varitm9.Click
            Set varitm10 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_GL_SKIP_ESSBASE_LOAD")
                    varitm10.Focus
                    varitm10.Click
            'Return to main Tab
            Set varitm10 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("ICTAB_0")
                    varitm10.Focus
                    varitm10.Click
                    'IE processing wait logic
                    Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
                    
                    With IE
                        Do Until .readyState = 4: DoEvents: Loop
                        
                    End With
            'Selecting Procurement Link
            Set varitm11 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_LINKS_OPR_DEF_LINK_PM1")
                    varitm11.Focus
                    varitm11.Click
                    'IE processing wait logic
                    Application.Wait (Now + TimeValue("00:00:03"))
                            While IE.Busy = True
                                DoEvents
                            Wend
                            
                    With IE
                        Do Until .readyState = 4: DoEvents: Loop
                        
                    End With
            'clicking Sub-Procurement Link---Open POPUP Window
            Set varitm12 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_PM_WRK_OPR_DEF_APVCHR_PB")
                    varitm12.Focus
                    varitm12.Click
            Application.Wait (Now + TimeValue("00:00:03"))
                    While IE.Busy = True
                        DoEvents
                    Wend
                    
            With IE
                Do Until .readyState = 4: DoEvents: Loop
                
            End With
'Debug.Print IE.LocationName, IE.LocationURL, IE.AddressBar, IE.Application

           ' For Each IE In shell
            'Print URL for Each Window but only one URL is getting printed as POPUP window dont have URL associated, it is getting open from DO-Modal
            'sh.Cells(i + 7, 5).Value = IE.LocationURL
            'Exit For
            'Next

  For Each w In CreateObject("Shell.Application").Windows
    If w.Name Like "*Internet Explorer" Then
      Debug.Print w.LocationURL
    End If
  Next
'sh.Cells(10, 5).Value = IE.LocationName
'sh.Cells(11, 5).Value = IE.LocationURL
'sh.Cells(12, 5).Value = IE.AddressBar
'sh.Cells(13, 5).Value = IE.Application
'Set varitm13 = IE.document.getElementById("ptifrmtgtframe").contentWindow.document.all.Item("OPR_DEF_TBL_AP_PYMNT_RCRD_FLG")
'Set varitm13 = IE.document.getelementbyid("pt_modals").getelementbyid("ptMod_0").getelementbyid("ptModTable_0").getelementbyid("ptModContent_0").getelementbyid("ptModFrame_0").getelementbyid("OPR_DEFAULT").getelementbyid("win0divPAGECONTAINER").getelementbyid("win0divPSPAGECONTAINER").getelementbyid("ACE_width").getelementbyid("win0divOPR_DEF_TBL_AP_AUTH_APPROVE_VCHR").getelementbyid("ACE_OPR_DEF_TBL_AP_AUTH_APPROVE_VCHR").getelementbyid("win0divOPR_DEF_TBL_AP_ORIGIN").getelementbyid("OPR_DEF_TBL_AP_ORIGIN")
 'If iePopup Is Nothing Then
'Set iePopup = GetInstance(TimeOutInSeconds:=5, _
'URL:="11http://www.dowjones.com/DJCom/Images/ContentImages/TheCompany/virtuouscircle.jpg")
'End If
        
      '  varitm13.Focus
      '  varitm13.Click



Set varitm13 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.all.Item("#ICSave")
        varitm13.Focus
        varitm13.Click

Application.Wait (Now + TimeValue("00:00:03"))
        While IE.Busy = True
            DoEvents
        Wend
        
With IE
    Do Until .readyState = 4: DoEvents: Loop
    
End With

Set varitm14 = IE.document.getelementbyid("ptifrmtgtframe").contentWindow.document.getelementbyid("#ICList")
        varitm14.Focus
        varitm14.Click

Application.Wait (Now + TimeValue("00:00:03"))
        While IE.Busy = True
            DoEvents
        Wend
        
With IE
    Do Until .readyState = 4: DoEvents: Loop
    
End With
'IE.Navigate "https://fms-dev.intertek.com/psp/FINDEV1/EMPLOYEE/ERP/c/MAINTAIN_SECURITY.USERMAINT.GBL?FolderPath=PORTAL_ROOT_OBJECT.PT_PEOPLETOOLS.PT_SECURITY.PT_USER_PROFILES.PT_USERMAINT_GBL&IsFolder=false&IgnoreParamTempl=FolderPath%2cIsFolder"
sh.Cells(i, 2).Value = "Complete"
End If
End If

Next i


End Sub

Please suggest further.

Regards:
Aditya Jain
 
Upvote 0
Thanks Zvi, I incorporated the code you provided but the "for" loop is getting executed only once and giving the output as the parent URL at immidiate window of VBE.
Can't provide the exact suggestion without debugging your code which is impossible to me because of unknown links login & password.
So just a guessing - it looks like the only single IE window is used for navigation to some web-pages.
If you see more than one IE window simultaneously then try commenting If-Then-EndIf lines to test all open IE windows, like this:
Rich (BB code):
  Dim w As Object
  On Error Resume Next
  For Each w In CreateObject("Shell.Application").Windows
    'If w.Name Like "*Internet Explorer" Then
      Debug.Print w.Name
      Debug.Print w.LocationURL
    'End If
  Next
 
Upvote 0
If site uses frames then the same URL appears for different content.
In that case checking of a window title instead of URL can help:
Rich (BB code):
  Dim w As Object
  On Error Resume Next
  For Each w In CreateObject("Shell.Application").Windows
    'If w.Name Like "*Internet Explorer" Then
      Debug.Print w.Document.Title
    'End If
  Next
Frame can be used for popup as well.
Then use: Set IE = w.Document.frames(0).Document
where 1 can be used instead of 1
 
Last edited:
Upvote 0
Frame can be used for popup as well.
Then use: Set IE = w.Document.frames(0).Document
where 1 can be used instead of 0
More correctly: Set IE = w.Document.frames(0)
where exact index or name of a frame can be used instead of the zero index.
After that something like this will work: IE.Document.getElementById("Submit").Click
 
Last edited:
Upvote 0

Forum statistics

Threads
1,215,273
Messages
6,123,987
Members
449,137
Latest member
abdahsankhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top