BY CATEGORY▼
VBA CATEGORIES▼
- Excel VBA - Basics (24)
- VBA User Forms (21)
- Excel VBA - pivot tables (9)
- Excel VBA - charts (6)
- VBA - advanced (13)
- VBA - working with files (11)
- VBA - linking applications (10)
- VBA - working with Outlook (14)
- Built-in VBA functions (9)
- VBA - working with data (55)
- VBA - scraping websites (24)
- VBA - working with shapes (3)
- VBA - classes and structures (6)
VBA - SCRAPING WEBSITES VIDEOS▼
- Excel VBA Part 46 - Querying Web Pages with Query Tables
- Excel VBA Part 47.1 - Browsing to Websites and Scraping Web Page
- Excel VBA Part 47.2 - Scraping Website Tables and Clicking Links
- Excel VBA Part 47.3 - Internet Explorer vs XML HTTP Request
- Excel VBA Part 47.4 - Logging in to a Website with Windows Security
- Excel VBA Part 47.5 - Basic HTTP GET and POST Requests
- Excel VBA Part 48 - Scraping Multiple Web Pages
- Excel VBA Part 49 - Downloading Files from Websites
- Excel VBA Part 57.1 - Getting Started with Selenium Basic and Google Chrome
- Excel VBA Part 57.2 - Basic Web Scraping with Selenium and Google Chrome
- Excel VBA Part 57.3 - Using Different Web Browsers in Selenium
- Excel VBA Part 57.4 - Finding Web Elements in Selenium
- Excel VBA Part 57.5 - Implicit and Explicit Waits in Selenium
- Excel VBA Part 57.6 - Working with Multiple Tabs in Selenium
- Excel VBA Part 57.7 - Using Select Drop Down Lists in Selenium
- Excel VBA Part 57.8 - Printing in Google Chrome using Selenium
- How do I open Google Chrome in full screen mode using Selenium in VBA?
- Is it possible to start Google Chrome hidden using Selenium in VBA?
- How do I access elements in the Shadow DOM using Selenium in VBA?
- How do I Refer to a Parent Element in Selenium for VBA?
- How do I hold Shift and click a link in Selenium for VBA?
- How do I scroll down a web page using Selenium in VBA?
- How do I copy CSV data from a website using VBA?
- How do I open Google Chrome with a user profile using VBA?
VBA - scraping websites videos | Excel VBA Part 47.4 - Logging in to a Website with Windows Security
Posted by Andrew Gould on 03 June 2019
This video explains how to use VBA to enter Windows Security credentials when you connect to a website. You'll learn how to pass a username and password when you send an XML HTTP request. You'll also learn how to use the Windows Script Host Shell object to access the security dialog box and write a username and password using the SendKeys method.
This video has the following accompanying files:
| File name | Type | Description |
|---|---|---|
| Logging in to a Website with Windows Security.xlsm | Excel workbook with macros |
Click to download a zipped copy of the above files.
There are no exercises for this video.
Making a video bigger
You can increase the size of your video to make it fill the screen like this:
Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.
When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.
Improving the quality of a video
To improve the quality of a video, first click on the Settings icon:
Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.
Choose to change the video quality:
Click on Quality as shown to bring up the submenu.
The higher the number you choose, the better will be your video quality (but the slower the connection speed):
Don't choose the HD option unless you have a fast enough connection speed to support it!
Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.
Hi,
This was a great traning and everything worked fine. The issue i have is after login i get redirected due to the type of login process. Normal login works fine. I can login and notice the url being redirected to outside source and redirected back to the customers nornal page for account use. Could you help me get loged in and get to my account after with this method, please?
Hi Chris,
It's quite difficult to offer help for systems that we don't have access to ourselves. Is there a specific thing that isn't working for you? What does your code look like?
Hi Andrew,
hope that you´re still fine!
I have perhaps, a small prob.
I want to login a specific Web SideTherefore I found your video and tried .
But .. I can not login
I can open the specific Web Site.
(After watching your Vidio and typed the Code in a Module)
The First error I got is at the point
Debug.Print...inner Text (see first Sub)
There is nothing shown in the "Direct Window"
I got the error : "Variable of Object or with ... not declared"
Trying the 2nd Sub I got the error at the Step
Do While IE.ReadyState.. READYSTATE_COMPLTE
I found out that I got the error Run Time Errore at the sequence Do While EReadyState (Runtime error 21470 231 79)
and at the sequenz "READYSTATECOMPLTE" it is shown = 4
What have I done?
So.. I tried to activate Net Framework. but I´m not allowed to do this (I´m not the administrator.. unfortunately)
What is the source Code of the Webside?
For User Name
form name ="pnForm"... id = "pnForm"
input name = "a1234..." width = "0" Title = "User name input" ... id = "Enter user name"
What do I see wenn Net Side is openend?
User Name then a field to type in the user name
Below
Password then a field to type in the password
and after this a button to sign in
I hope taht I could descripe my prob and will hope that you have an idea what´s goin wrong ?
Take Care !
Pete
Hi Pete, this video shows a different technique for logging into a website to the one you're describing. I think you need some of the techniques shown in an earlier video https://www.wiseowl.co.uk/vba-macros/videos/vba-scrape-websites/scraping-web-pages/
In that video you can see some examples of entering text into a form and clicking a button. I hope it helps!
Hi Andrew,
Thanks a lot for such very informative videos.
I have tried dozens of different websites but have not got the login pop-up window shown on the video . I even created an account on wise owl and used the URL https://www.wiseowl.co.uk/user/private and also did not see the login window. Am i missing anything? Any website I should use to test that login code?
Thanks and looking forward to hearing from you.
Hi there, no don't worry, you haven't missed anything! Not many websites use Windows security to validate user credentials. The websites which do tend to be internal systems such as the one I used in this video which is Wise Owl's internal diary system. Here's a brief description of how to configure a website to use Windows authentication https://support.microsoft.com/en-gb/help/323176/how-to-implement-windows-authentication-and-authorization-in-asp-net
I hope that helps!
Many thanks Andrew. Actually i noticed later that it was my IE settings to have an auto login using my current credentials. When i change the setting to prompt for username then i started seeing that login security window on the intranet site :) . i a m still getting an error on Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy for a reason but its not a big matter though i believe it smth to do with my intranet site.
Thanks again and much respect !
That's great! Pleased to hear that you're making progress!
Hi, Andrew,
realy appreciated for your kind reply. after study your video i'm able to login the web by using IE mode, thanks again, i will need more study on this XML mode. i study some article about using Fiddler to analyze the setrequestheader and Cookie method.
thanks, have a good day.
You're very welcome, Eric. I hope you find a solution to your problem!
Realy appreciated for those video, is there any way to sign in a web by using XML method.
Hi Eric,
Yes, we do explain how to do that in the video, or did you mean something different?
Thanks for reply, i did see the video, the method is that VBA can pass username and password to the web. but faild. the reason is might username field is encode by base64, i guess. below are the HTML code.
after google it, i found someone said, it has to use a fake cookie or hijact the js script.
<input name="userName" type="text" id="userName" class="ebk3-inText" placeholder="User name" style="background-image: url("data:image/png;base64,iVBORw0"); background-repeat: no-repeat; background-attachment: scroll; background-size: 16px 18px; background-position: 98% 50%; cursor: auto;">
Hi Eric,
The video is related to logging in using basic Windows security. From the code you've posted it looks as though you need to enter a username into an HTML element on the page. I answered a similar question on YouTube recently and include the code below to give you an idea of how to do this:
Sub TestLogin()
Dim ie As SHDocVw.InternetExplorerMedium
Dim doc As MSHTML.HTMLDocument
Dim LoginForm As MSHTML.HTMLFormElement
Dim UserNameInput As MSHTML.HTMLInputElement
Dim PasswordInput As MSHTML.HTMLInputElement
Set ie = New SHDocVw.InternetExplorer
ie.Visible = True
ie.navigate "https://www.marketingscents.com/index?page=login-new"
Do While ie.readyState <> READYSTATE_COMPLETE And ie.Busy
Loop
Set doc = ie.document
Set LoginForm = doc.getElementById("js-login-form")
Set UserNameInput = LoginForm.getElementsByClassName("form-control input-lg")(0)
Set PasswordInput = LoginForm.getElementsByClassName("form-control input-lg")(1)
UserNameInput.Value = "my user name"
PasswordInput.Value = "my password"
LoginForm.submit
End Sub
If the page involves javascript I believe that you need to use the browser rather than the XML approach, although I don't consider myself an expert in this at all! If you need to simulate cookies or obtain security tokens, this is somewhat more complicated.