I've been tasked with downloading regular data updates from a national reporting website but as this website has user log-in functionality. This means I have to set the macro up to open the webpage and then automatically enter the user ID and password.
Step 1: Create a user form and place one listbox and Web Browser as shown below: To get the Web browser control in toolbox follow below steps: Right click on toolbox. Select additional control (refer below screenshot 1.1) Select Microsoft Web Browser control from the list of controls (refer below screenshot 1.2) Check and OK. Set in the options – Microsoft Internet Controls for the InternetExplorer object and Microsoft HTML Object Library for HTMLDocument object. Of course You can go with the late binding. Setting everything as Object variable is good, but it is easier to begin with early binding. This is a sample program to control the ENA over LAN using Microsoft Excel on an external PC. The sample program is written in Microsoft Excel VBA and using WinSock API in the Windows environment. Revision Number. Operating Conditions. Firmware rev.A.02.xx / A.03.xx. Install Procedures. Download the 'ctrllan.xls' file. I was looking about how to control IE with VBA. And in all the previous posts it suggets you have to activate Microsoft Internet Control, in the references menu in vba. Is it weird that i cant find it? I'm running Excel on vista? Basically i'm trying to do what Leith Ross showed here.
I found a very useful tutorial on accessing google webpages via VBA, but have expanded the detail to show excel 2007 instructions and the method for defining the correct HTMLDoc elements for any webpage. First of all you need to activate certain references from the VB Editor. Go to tools – References and then select
- Microsoft HTML Object Library
- Microsoft Internet Controls (if you can't see this as an option try Microsoft Browser Helpers)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | Dim HTMLDoc As HTMLDocument Dim oBrowser As InternetExplorer Sub Website_Login_Test() Dim oHTML_Element As IHTMLElement Dim sURL AsString OnErrorGoTo Err_Clear sURL ='https://www.examplewebsite.co.uk/login.aspx' Set oBrowser = New InternetExplorer oBrowser.Silent = True oBrowser.timeout = 60 oBrowser.navigate sURL oBrowser.Visible = True Do ' Wait till the Browser is loaded LoopUntil oBrowser.readyState = READYSTATE_COMPLETE Set HTMLDoc = oBrowser.Document HTMLDoc.all.loginID.Value = 'exampleusername' HTMLDoc.all.Password.Value = 'examplepassword' ForEach oHTML_Element In HTMLDoc.getElementsByTagName('input') If oHTML_Element.Type = 'submit' _Then oHTML_Element.Click: ExitFor Next ' oBrowser.Refresh ' Refresh If Needed Err_Clear: If Err <> 0 Then Err.Clear ResumeNext EndIf EndSub |
This code works for the specific website that I am interested in but each web page could potentially have a different description for each HTML Element eg login rather than loginID.
In order to determine the correct element to use you can amend the code to print the HTML element names to the immediate window. This will then enable you to identify the obvious candidate or you could work through each until you find the one that works. Here is the amended code for identifying the appropriate HTML element from a webpage:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | Sub HTML_Element_Printer() OnErrorGoTo Err_Clear sURL = 'https://www.examplewebsite.co.uk/login.aspx' Set oBrowser = New InternetExplorer oBrowser.Silent = True oBrowser.timeout = 60 oBrowser.navigate sURL oBrowser.Visible = True Do ' Wait till the Browser is loaded LoopUntil oBrowser.readyState = READYSTATE_COMPLETE Set HTMLDoc = oBrowser.Document HTMLDoc.all.loginID.Value = 'exampleusername' HTMLDoc.all.Password.Value = 'examplepassword' ForEach oHTML_Element In HTMLDoc.getElementsByTagName('input') Debug.Print oHTML_Element.Name Next ' oBrowser.Refresh ' Refresh If Needed Err_Clear: If Err <> 0 Then Err.Clear ResumeNext EndIf EndSub |
This article will teach you about how you can perform a google search on Internet Explorer using Microsoft Excel.
Steps:
- Create the object of Shell Application
- Get all the windows using shellobject.Windows
- Navigate all the windows
- check if window is Internet Explorer.
- if window is IE then check if it is Google Search browser and store the object
- Get the Page object of Google Search window.
- Identify the objects on the Page using 'GetElementById' and 'getElementsByName'
- Put the search text
- Wait for 3-4 secs
- Click the Search button
Create the object of Shell Application
Set objShell = CreateObject('Shell.Application')
Omniweb download for mac. Get all the windows using shellobject.Windows
Set objAllWindows = objShell.Windows
Navigate all the windows
For Each ow In objAllWindows
check if window is Internet Explorer
If (InStr(1, ow, 'Internet Explorer', vbTextCompare)) Then
if window is IE then check if it is Google Search browser and store the object
If (InStr(1, ow.locationURL, 'www.google.', vbTextCompare)) Then
Set objGoogle = ow
End If
Get the Page object of Google Search window.
Set objPage = objGoogle.Document
Identify the objects on the Page using 'GetElementById' and ' getElementsByName'
Set SearchEditB = objPage.getElementByID('gbqfq')
Set Search = objPage.getElementsByName('btnG')
Put the search text
SearchEditB.Value = 'Excel macro Sumit Jain'
Excel Vba Microsoft Internet Controls Windows 10
Wait for 3-4 secs City car driving download for mac.
check if window is Internet Explorer
If (InStr(1, ow, 'Internet Explorer', vbTextCompare)) Then
if window is IE then check if it is Google Search browser and store the object
If (InStr(1, ow.locationURL, 'www.google.', vbTextCompare)) Then
Set objGoogle = ow
End If
Get the Page object of Google Search window.
Set objPage = objGoogle.Document
Identify the objects on the Page using 'GetElementById' and ' getElementsByName'
Set SearchEditB = objPage.getElementByID('gbqfq')
Set Search = objPage.getElementsByName('btnG')
Put the search text
SearchEditB.Value = 'Excel macro Sumit Jain'
Excel Vba Microsoft Internet Controls Windows 10
Wait for 3-4 secs City car driving download for mac.
Excel Vba Microsoft Internet Controls Examples
FnWait (5)
Excel Vba Microsoft Internet Controls Reference
Click the Search button
Search(0).Click
Excel Vba Microsoft Internet Controls Diagram
Complete Code:
