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.

You can download any files that you need to follow the video here.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 2 threads Add post
01 Sep 19 at 14:15

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.

02 Sep 19 at 07:26

You're very welcome, Eric.  I hope you find a solution to your problem!

28 Aug 19 at 11:14

Realy appreciated for those video, is there any way to sign in a web by using XML method.

29 Aug 19 at 07:32

Hi Eric,

Yes, we do explain how to do that in the video, or did you mean something different?

29 Aug 19 at 08:06

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(&quot;data:image/png;base64,iVBORw0&quot;); background-repeat: no-repeat; background-attachment: scroll; background-size: 16px 18px; background-position: 98% 50%; cursor: auto;">

Andrew G  
29 Aug 19 at 11:44

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
    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"
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.