Get Data from Website that Requires a Login

If you want to get data into Excel from a website that requires a login, you may have already been frustrated that web queries don’t work so well. By automating Internet Explorer and the login process, you can get to that data. This example shows how to login, retrieve a table, and paste it into Excel.

I’ll be using a website I created to demonstrate. The username is dailydose and the password is password. Behind the login, there’s a table that I stole from Contextures.com. I steal that table for all my examples, so don’t tell Debra. If you attempt to get the table, the site recognizes that you’re not logged in and sends you to the login page. Only after logging in can you get to the goods.

To automate Internet Explorer, we’ll need to set a reference to Microsoft Internet Controls.

While you’re in the References dialog, set a reference to Microsoft Forms 2.0 Library. This will already be checked if you have a userform in your project. I’ll use it to put some text into the clipboard.

Before I get to the code, I need to do a little homework. I go to the login page using Firefox and choose Page Source form the View menu.

I take note of the names of the two controls on the form, login and password. I’ll need those later. I also note that the input type is submit. Next, I go to the page I ultimately want and look at its source.

I note that the id for the table I want is “sampletable”. If the table you want doesn’t have an id, you need to loop through all of the tables and try to find some unique characteristic of the table to identify it. Here’s an example that loops through all the tables until it finds the 15-Year-Fixed Average Interest Rate table on Yahoo!’s finance page.

I have all the data I need to produce the code

Sub GetTable()

Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject

‘create a new instance of ie
Set ieApp = New InternetExplorer

‘you don’t need this, but it’s good for debugging
ieApp.Visible = True

‘assume we’re not logged in and just go directly to the login page
ieApp.Navigate “http://severe-frost-552.heroku.com/login”
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

‘fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.login.Value = “dailydose”
.Password.Value = “password”
.submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

‘now that we’re in, go to the page we want
ieApp.Navigate “http://severe-frost-552.heroku.com/”
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

‘get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item(“sampletable”)

‘copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText “” & ieTable.outerHTML & “”
clip.PutInClipboard
Sheet1.Select
Sheet1.Range(“A1”).Select
Sheet1.PasteSpecial “Unicode Text”
End If

‘close ‘er up
ieApp.Quit
Set ieApp = Nothing

End Sub

The code first goes directly to the login page. This wouldn’t be necessary if you already had a cookie remembering that you logged in. However, rather than check to see if we get to the right page, I go login every time.

Next we populate the two controls, login and password, and submit it.

This actually takes us right where we want to be. But that isn’t always the case. Sometimes you’ll want to go a different page than the one that the login script redirects to. Next in the code, I navigate to the page I want.

You’ll notice that every time I navigate somewhere (or submit, causing a redirect), I run the same six lines of code. Those six lines, condensed to two with colons, cause the code to wait around until the page is done loading.

Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Now that the administrative work is done and my page is loaded, I get the table that has the id “sampletable”. I grab the OuterHTML property, surround it with html tags, and stuff it in the clipboard via the DataObject. The html tags trick Excel into thinking the format of the DataObject is html. Duly tricked, Excel will parse the html and try to make sense of it. It should work just as if you had copied from the web page and pasted into Excel. I select cell A1 and Paste Special Unicode Text.

Finally, I quit IE.

I don’t know if this will work on every website, but it’s worked on a few that I’ve tried. I’ve also experienced some problems with it on other people’s machines. We never got to the bottom of it, but we think it’s firewall related. If you have problems, leave a comment.

92 thoughts on “Get Data from Website that Requires a Login

  1. You don’t need IE for this. I did it with MSXML and no early bound references, it’s also faster:

    Sub GetTable()

    Dim xml As Object ‘ MSXML2.XMLHTTP60
    Dim htmlDoc As Object ‘ MSHTML.HTMLDocument
    Dim htmlBody As Object ‘ MSHTML.HTMLBody
    Dim ieTable As Object
    Dim clip As DataObject

    Set xml = GetMSXML

    With xml
          .Open “POST”, “http://severe-frost-552.heroku.com/session”, False
          .send “login=dailydose&password=password”
    End With

    Set htmlDoc = CreateHTMLDoc
    Set htmlBody = htmlDoc.Body
    htmlBody.innerHTML = xml.responseText

    Set ieTable = htmlBody.all.item(“sampletable”)
       
        ‘copy the tables html to the clipboard and paste to teh sheet
      If Not ieTable Is Nothing Then
            Set clip = New DataObject
            clip.SetText “<html>” & ieTable.outerHTML & “</html>”
            clip.PutInClipboard
            range(“A1”).Select
            ActiveSheet.PasteSpecial “Unicode Text”
        End If

    End Sub

    Function CreateHTMLDoc() As Object ‘ MSHTML.HTMLDocument
    Set CreateHTMLDoc = CreateObject(“htmlfile”)
    End Function

    Function GetMSXML() As Object ‘ MSXML2.XMLHTTP
    On Error Resume Next
      Set GetMSXML = CreateObject(“MSXML2.XMLHTTP”)
    End Function

  2. Thanks a lot to both Dick and JP. It’s great to follow you and challenging for me.
    I’m quite new to VBA. I wonder why if I run JP’s code I get “Run-Time error’1004′ PasteSpecial Method of Worksheet class failed” I have to take out “Unicode Text” after PasteSpecial. I’ve been looking for a solution in some forums but no satisfactory answer.
    Keep up your excellent blog. Cheers

  3. Hello all, I’m trying to use vba to retrieve data from the following web site;
    https://www.smithbarney.com/cgi-bin/login/login.cgi
    I’m using Excel 2007 and the code I have so far is…

    Sub WebLogin()
      Dim a As String
      Set ie = CreateObject(“InternetExplorer.Application”)
      With ie
        .Visible = True
        .navigate “https://www.smithbarney.com/cgi-bin/login/login.cgi”
        Do Until .readyState = 4
          DoEvents
        Loop
        .document.all.Item(“username”).Value = “MyUserId”
        .document.all.Item(“password”).Value = “MyPassword”
        .document.forms(0).submit
      End With
    End Sub

    I went to the web page and clicked view source, but couldn’t determine which login and password I should put in the code. I think the problem is username and password not matching the source code.
    Once I login to the web page I would like to import a page, then logout and close the page.
    Thanks, Any help would be most appreciated.

  4. Greg: The password is definitely id=”password”, but the username appears to be generated via javascript. I don’t know how this would work in that case. I tried disabling javascript to see if there was an alternative login, but no dice.

  5. Private Sub LaunchGamil(username As String, password As String)
       
        Const strURL_c As String = “http://mail.google.com”
       
        Dim objIE As SHDocVw.InternetExplorer
        Dim ieDoc As MSHTML.HTMLDocument
        Dim tbxPwdFld As MSHTML.HTMLInputElement
        Dim tbxUsrFld As MSHTML.HTMLInputElement
        Dim btnSubmit As MSHTML.HTMLInputElement
       
        On Error GoTo Err_Hnd
            ‘Create Internet Explorer Object
           Set objIE = New SHDocVw.InternetExplorer
            ‘Navigate the URL
           objIE.Navigate strURL_c
            ‘Wait for page to load
           Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
            ‘Get document object
           Set ieDoc = objIE.Document
            ‘Get username/password fields and submit button.
           Set tbxPwdFld = ieDoc.all.Item(“Passwd”)
            Set tbxUsrFld = ieDoc.all.Item(“Email”)
            Set btnSubmit = ieDoc.all.Item(“signIn”)
            ‘Fill Fields
           tbxUsrFld.Value = username
            tbxPwdFld.Value = password
            ‘Click submit
           btnSubmit.Click
            ‘Wait for transistion page to load
           Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
            ‘Wait for main page to load
           Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop

    Err_Hnd: ‘(Fail gracefully)
       objIE.Visible = True

    End Sub

    sorry as i am new so i didnt see that well.Can i use same script for yahoo? plus i wana change a little bit in it that it open firefox in it insted of IE.can you help me in this regards?

  6. I went to the web page and clicked view source, but couldn’t determine which login and password I should put in the code. I think the problem is username and password not matching the source code.

  7. I have been able to get the code above to work, but am having issues modifying it for other uses for inputting data into a website. I have written the code below and I get the error “Object Doesn’t support this property or method” after it opens the website and gets to the first field input command. Any suggestions on where I am going wrong or how I can accomplish this?

    Sub Travel()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object

    Set ieApp = New InternetExplorer

    ieApp.Visible = True

    ieApp.Navigate "www.travelocity.com"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.Document

    With ieDoc.forms(0)
    .PickupCity.Value = "DTW"
    .PickupDate.Value = "01/01/2012"
    .PickupTime.Value = "0830"
    .DropOffdate.Value = "01/14/2012"
    .DropOffTime.Value = "0830"
    .submit
    End With

    End Sub

    Here is an excerpt from the page source identifying the first field I am trying to input data into.

  8. Guys, you are all great. Being a first timer, with your suggestions, i could execute a VBA script and open a temporary yahoo email id. I don’t know if I’m bugging you people, but here are a few doubts i got in this experiment.
    User Name: vbascript@yahoo.com
    Password : 01vba80script

    1. How to reassign the navigation to the page after we have logged in?
    ‘now that we’re in, go to the page we want
    ieApp.Navigate “http://severe-frost-552.heroku.com/”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    In this case we can’t hard code the URL, as in the above example, as it is dynamically generated. Is there a way to capture the URL that appears after successful login?

    2. There’s a tab “INBOX”. How to write code to directly open INBOX tab after successful login?

    3. Once under INBOX tab, i didn’t find any tables in the source code to display the three samples mails received.

    Kindly help me.

  9. Here’s the source code of the login page of a site i tried to create automatic login for using help from your examples above.

     
    Login :

     

     
    Password :

    I’m unable to create a successful login as i could not find out how to assing user name and password to the text boxes, given their strange names!!!

    Help me out!!!

  10. Sorry, missed out on the code in my last post. I don’t the formatting rules for posting the code. BTW, in the above context, the name for user name field is input name=”portlet_5_6{actionForm.userName}” and the password is input name=”portlet_5_6{actionForm.password}”

  11. Please forgive my ignorance, but where does this code “go”? I tried putting a couple of pastes of some of the code above into a macro in Excel 2010. The first time I got an error that DataObject type was undefined. The second time, I got the same error about InternetExplorer. Thank you in advance for a non-flamey response.

    ~Todd

  12. Matter, thanks for the link. It’s definitely worthwhile, and I’ve bookmarked it! Grazie mille!

  13. Hi, I’m new to this forum so I apologise if I’m asking a question that has already been covered.

    Can VBA open Firefox, navigate to compose a new Gmail message, fill in the To:, Subject:, Attachments: and message body? Each element, including the attachment path is held in a related access form field.

    Me.To
    Me.Subject
    Me.Body
    Me.Attachment_Name_Path

    I have managed to do it crudely with IE but most of my colleagues use Firefox as the default browser.

    If it could ‘click’ the send button as well that would be a bonus.

    Many thanks, Jon

  14. Dick, I am using your code to try to get some data from a web site, and it works to login, and also to paste into excel, but the data is missing. I only get parts of the table. Are you able to help?

  15. Mike: I’m not sure which of the 100 tables you want, but one of the problems is that there some funky redirect going on. You need to wait a couple of seconds after login for the javascript to populate the tables before you can read them in. Change this part of the code:

    'now that we’re in, go to the page we want
    ieApp.Navigate "http://www.sygration.com/cgi-bin/dashboard"
    Application.Wait Now + TimeSerial(0, 0, 3)
    Do While ieApp.Busy
    DoEvents
    Loop

  16. That was it..thanks. I needed to adjust the time from 3 to 10. works perfectly. thanks.

  17. Hi Guys

    Ive tried to follow the instrustions above but am unable to find the relevant fields when I view the source of the webpage, maybe someone here could help me,, thanks

    The website im trying to log into is : http://www.belldirect.com.au

  18. Matthew: Search for this string

    That’s the username. The next div is the password and the one after that is the login button.

  19. Hi Dick thank you for your help however i was still unable to get it to work, I found username password and login buttons, but get run time error 438 object doesnt support this property or method

    ‘fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
    .input Name = “ctl00$ContentPlaceHolder4$loginbeta$textUsername$_textbox” = “dailydofdse”
    .input Name = “ctl00$ContentPlaceHolder4$loginbeta$textPassword$_textbox” = “password”
    .ID = “ContentPlaceHolder4_loginbeta_divNormalLoginButton”
    End With
    Do While ieApp.Busy: DoEvents: Loop

    thanks for your time
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

  20. Matthew: The login controls don’t appear to be part of a form tag or there’s some javascript business that creates the POST action. I don’t think this method will work. You can try JPs method up in the second comment. I couldn’t get it to work, but it might be worth trying with a real username and password.

  21. Dick: I have tried using your code and it is working fine till the code line mentioned below:

    Set ieDoc = ieApp.Document

    At the above line code stops and gives an error

    Run-time error ‘-2147467259 (80004005)”
    Automation Error
    Unspecified Error

    Any idea as to why am I getting this error?

    Thanks in advance.

  22. Ankit: Well, it looks like that web app was deleted. In other bad news, I can’t seem to find the source code – possibly also deleted. I probably looked at it and couldn’t figure out why I wrote it. I’ll try to recreate it and get it back up.

  23. hi, i read all comments about getting the explorer to login to a webpage where username and password is needed, i am trying to get my code to do that for me, but iwas unsuccesful, probably i dont get the right input name, let me give here the source page maybe some1 can tell me exactly what to put in the VBA,

    <tr

    E-Mail Address:

    Password:

  24. Hello Dick.

    I Use your code, but i dont get data. The login works fine. I cant see a table id, but it would all so be fine with the whole site? Can yoy helt me?

    Here is my code:

    Sub GetTable()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object
    Dim clip As DataObject

    'create a new instance of ie
    Set ieApp = New InternetExplorer

    'you don’t need this, but it’s good for debugging
    ieApp.Visible = True

    'assume we’re not logged in and just go directly to the login page
    ieApp.navigate "http://www.installationsblanket.dk/Logon.asp"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.document

    'fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
    .Brugernavn.Value = "***"
    .Adgangskode.Value = "***"
    .submit
    End With
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    'now that we’re in, go to the page we want
    ieApp.navigate "http://www.installationsblanket.dk/selskab/600.asp"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    'get the table based on the table’s id
    Set ieDoc = ieApp.document
    Set ieTable = ieDoc.all.Item("")

    'copy the tables html to the clipboard and paste to teh sheet
    If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "" & ieTable.outerHTML & ""
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
    End If

    'close 'er up
    ieApp.Quit
    Set ieApp = Nothing

    End Sub

  25. Hey Dick.

    First of all thanks for all the answers.
    I want data from a website with pass and login. I use your way, but i did not get data in excel.
    The login works fine, but there is no table id of the table i want, so what to do. Is there a way to get the whole page?

    Here is my code:

    Sub GetTable()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object
    Dim clip As DataObject

    'create a new instance of ie
    Set ieApp = New InternetExplorer

    'you don’t need this, but it’s good for debugging
    ieApp.Visible = True

    'assume we’re not logged in and just go directly to the login page
    ieApp.navigate "http://www.installationsblanket.dk/Logon.asp"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.document

    'fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
    .Brugernavn.Value = "roan"
    .Adgangskode.Value = "roan"
    .submit
    End With
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    'now that we’re in, go to the page we want
    ieApp.navigate "http://www.installationsblanket.dk/selskab/600.asp"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    'get the table based on the table’s id
    Set ieDoc = ieApp.document
    Set ieTable = ieDoc.all.Item("")

    'copy the tables html to the clipboard and paste to teh sheet
    If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "" & MieTable.innerHTML & ""
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
    End If

    'close 'er up
    ieApp.Quit
    Set ieApp = Nothing

    End Sub

    Im pretty new in VBA, what can i write at last to get the table?

    Thanks for help.

  26. Hi

    Will it be possible to adapt this code for a ComponentArt_Grid? The source code of the HMTL that I want to extract the data from has the following code:

    window.SharesAndIndices1_smartGrid1 = new ComponentArt_Grid(‘SharesAndIndices1_smartGrid1′);
    SharesAndIndices1_smartGrid1.Data = [[[new Date(2013,2,26,0,0,0),’26 Mar 2013′],16062,15600,15778,16000,1490791,[0.00,’0.00′],[0.00,’0.00′],[3.64,’3.64′],[7.67,’7.67′],[13.04,’13.04′],[3.64,’3.64′]],[[new Date(2013,2,25,0,0,0),’25 Mar 2013′],15713,15250,15400,15600,2273264,[0.00,’0.00′],[0.00,’0.00′],[3.73,’3.73′],[7.87,’7.87′],[12.71,’12.71′],[3.73,’3.73’]],

    Which then continues for all the entries. How will one import this into Excel?

    Thanks!

  27. Nope. Anything that processes server-side isn’t going to be much help when you’re parsing html. Sorry.

  28. Hi,

    Looking for VBA code that extracts details from Webportal.
    For instance need to extract details using roll numbers.

    thanks!

  29. Hi,

    I am trying to login to the following site

    http://www.football-investor.net/index.php?page=login

    I changed my details to

    .user.value = “MyUserName”
    .Pass.value = “MyPassword”
    .Submit

    I am having problem with “.Submit” it doesn’t seem to work…

    The html code shows the following
    <input class="button" value="Login" name="Submit" type="submit"

    So using .Submit should be correct. (I tried with and without caps)

    When I use .submit, it takes me to this page and nothing happens. Even I use no password, it doesn't give me an error message.
    http://www.football-investor.net/verify.php

    Any help to tips will be greatly appreciated.

    Kind Regards,

    Shlodge

  30. I’m using this code and it works like a charm. The only problem I’m having is that once I log in, if I want to do it again VBA will throw an error with the username. I was wondering Hor can I create an If statement before:

    ‘assume we’re not logged in and just go directly to the login page
    ieApp.Navigate “http://severe-frost-552.heroku.com/login”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    to check if the user is already logged in. If not then perform the next step. Else jump to:
    ‘now that we’re in, go to the page we want
    ieApp.Navigate “http://severe-frost-552.heroku.com/”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    Thanks in advance,

    J

  31. J.L.: There’s nothing that’s going to tell you if you’re logged in or not. There are a couple of options. 1) Log out every time, then the login part will always work. 2) Try to navigate to a page that requires a login and see if it redirects you to the login screen. If it does, you’re not logged in. If it doesn’t, you are.

  32. I thought there was an option to create an if statement to check if someone is logged in do a sign out procedure.

    Thanks! for the response

  33. Many thanks for this!

    I was looking to extract information from my ebay account and this does the trick.
    However, the info in the table I copy is formatted ## / ## whereby # represents a number.
    So when it tries to paste 10 / 10 in the worksheet for example, it auto-changes to 10/okt in my sheet.

    I tried putting sheet.Range(“A1”).NumberFormat = “@” before the paste statement in the VBA code,
    so that excel wouldn’t auto-format the pasted values. However this doesn’t seem to work. It seems that what I’m pasting has an inherent format to it that overrides the format I’m setting on my cells.

    So my question is: how can I adjust the code so that excel won’t format the pasted values to a date format?

    Many thanks in advance!

  34. Hello,
    Can anyone tell me how to navigate between two aspx pages.Actually i am facing problem while finding elements in html code.
    Text box lies in header.aspx and the corresponding data opens in default.aspx. I am not able to find any solution on it.Please rply…….

  35. HI,

    I’m fairly new to vba scripting and trying to do something similar to the cases described above but can’t seem to get any of the above examples to work.

    I need to login in to the site http://www.dairyweb.com.au as a starting point and then progress to a further page and select table data and paste back into excel. Can someone help me with the starting point by helping me identify what username and password controls/labels I need from this website html to use for the code? Any help would be greatly appreciated.

    Thanks

  36. Duncan: You can try this, but I can’t tell what’s behind the frame, so I don’t know if it will do you any good.

    Sub GetTable()

    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument

    Set xHttp = New MSXML2.XMLHTTP

    xHttp.Open "POST", "https://web1.dairyweb.com.au/dwfront25", False
    xHttp.send "form-login-username=uname&form-login-password=pword"

    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText

    Stop

    End Sub

  37. Set a reference (Tools – Reference) to the Microsoft XML library and the Microsoft HTML library.

  38. Hello,

    I am using this part of the code above to login to a website and it I am not sure what code to use to press the login button. The button id is “loginBtn”, but for some reason, I cannot get it to work. Help Please!!!!

    Sub GetTable()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object

    Set ieApp = New InternetExplorer

    ieApp.Visible = True

    ieApp.navigate “http://www.egnyte.com/login/”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.document

    With ieDoc.forms(0)
    j_username = “XXXXX”
    j_password = “XXXXX”
    j_domain = “XXXX”
    .submit

    End With

    End Sub

  39. Has anyone tried this on a mac? i dont have IE so none of the programming works with excel on the mac. Nor do i have any references for “microsoft internet controls”.
    thanks

  40. Dick – This is an excellent form of teaching others how to scrape data from a website. I have a question. What happens if the table in the example was larger and I’d have to click through many pages to see all the information? Will the code pull the data from all the other pages, or would I have to manipulate the code to go through each page?

    Thanks,

    Harrison

  41. i was trying to login to one of the website in my office intranet but it is giving below error. can anyone help on this?
    —————————
    Microsoft Visual Basic
    —————————
    Run-time error ‘-2147417848 (80010108)’:

    Automation error
    The object invoked has disconnected from its clients.
    —————————
    OK Help
    —————————

  42. Excel 2007, IE 8, XP

    Great tutorial.

    My problem is that it randomly starts redirecting me back to the login page and the ends the macro without doing anything.

    If I put a step in the code at the .submit line… it will pause and I can then manually click the Login button which takes me to the main page like normal.

    But if I hit continue in VBA, it is redirecting me back to the login page, which prevents getting the table id and passes ieTable as Nothing.

    Very frustrating.
    Could it be the website? – I can manually do it with no problem.
    Maybe the timing? – I’ve put Application.Wait Now + TimeSerial(0, 0, 3) in a few different spots with no change.

    Any more ideas? Thanks!

  43. I try to login but not sucess.

    Please help me.

    I use code bellow

    Sub GetTable()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object
    Dim clip As DataObject

    ‘create a new instance of ie
    Set ieApp = New InternetExplorer

    ‘you don’t need this, but it’s good for debugging
    ieApp.Visible = True

    ‘assume we’re not logged in and just go directly to the login page
    ieApp.Navigate “http://www.ktzmico.com/en/home/index.aspx”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.Document

    ‘fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
    .login.Value = “133691”
    .Password.Value = “mypassw”
    .submit
    End With
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    ‘now that we’re in, go to the page we want
    ieApp.Navigate “https://www.ktzmico.com/en/tradingaccount/historical-data.aspx?chgPage=f&period=Daily&sort=ASC&symbol=ptt&sDate=1/29/2015&eDate=3/30/2015”
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    ‘get the table based on the table’s id
    Set ieDoc = ieApp.Document
    Set ieTable = ieDoc.all.Item(“sampletable”)

    ‘copy the tables html to the clipboard and paste to teh sheet
    If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText “” & ieTable.outerHTML & “”
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range(“A1”).Select
    Sheet1.PasteSpecial “Unicode Text”
    End If

    ‘close ‘er up
    ieApp.Quit
    Set ieApp = Nothing

    End Sub

  44. Hi, thank you for this and for your replies!!

    I am trying to run this code on a site that has no table id or class, don’t know the code necesarry to set ieTable in this case. I tried with

    but this ended up conflicting with

    i would really appreciate your help with this matter.

    -Luis

  45. I am trying to pull tables from web queries to different sheets in Excel. This code used to work just fine because IE supported the http://username:pass@URL format. It no longer does that and am running this on a site where modifying the registry is not an option. I have tried everything to get it to automatically log in, but I just can’t seem to get it to fill out the forms. Below is the original code (ip and login information has been altered to protect site)

    Sub Web_Query()

    Dim serverIP As String
    Dim serverSheet As String
    Dim strConnectString As String
    Dim QT As QueryTable
    Dim i As Integer

    On Error Resume Next

    For i = 1 To 5
    Select Case i
    Case 1
    serverIP = “192.168.1.4”
    serverSheet = “SITE0”
    Case 2
    serverIP = “192.168.1.5”
    serverSheet = “SITE1A”
    Case 3
    serverIP = “192.168.1.6”
    serverSheet = “SITE1B”
    Case 4
    serverIP = “192.168.1.7”
    serverSheet = “SITE2”
    Case 5
    serverIP = “192.168.1.8”
    serverSheet = “SITE3”
    Case Else
    serverIP = “192.168.1.4”
    serverSheet = “SITE0”
    End Select

    strConnectString = _
    “URL;http://username:pass@” & serverIP & “/status?title=Status&timeFormat=use24Hour&stationName=*&swid=*MeterPowerAlarm*&hideDescription=on&status1=*inAlarm*&sort=swid&content-type=text%2Fhtml”

    Worksheets(serverSheet & ” Alarms”).Select

    ‘ Clear out the old data
    Worksheets(serverSheet & ” Alarms”).Cells.Select
    Selection.ClearContents
    Range(“A1″).Select

    ‘ On the Workspace worksheet,
    ‘ clear all existing query tables
    For Each QT In Worksheets(serverSheet & ” Alarms”).QueryTables
    QT.Delete
    Next QT

    ‘ Define a new Web Query
    Set QT = ActiveSheet.QueryTables.Add _
    (Connection:=strConnectString, Destination:=Range(“A1”))

    With QT
    .Name = serverSheet
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    End With

    ‘ Refresh the Query
    QT.Refresh BackgroundQuery:=True

    Next

    ThisWorkbook.Sheets(1).Select

    End Sub

  46. Hi I am trying to take information from this page – http://flow.gassco.no/

    I am using the following code following an attempt at editting some of the above but getting a runtime 91 error

  47. It opens the IE page, but gives and error of “Object doesn’t support this property or method”.

    If I then click the “accept” button myself in the IE page I get and error of object variable or With block variable not set.

    I assume the problem is with the html object I have entered?

  48. Hi guys,

    This is great. However, I also get the error “Object doesn’t support this property or method” once I hit ok, it takes me to the website but that’s it. Is there a trick to it? or is it because is no longer supported.

  49. Hey i am doing data entry job as part time i need your help to automate my work http://mca.gov.in/DCAPortalWeb/dca/MyMCALogin.do?method=setDefaultProperty&mode=31
    is the web page i want to use i had excel that contains CIN/FCRN values i need to submit them and get their email id stored in another excel can you please help me in this regards.Hoping to get positive response from you please send code to my mailid castlepumpy@gmail.com i am sending a cin number for reference for login purpose U15122UP2014PTC066398 PLEASE DO HELP me in this regard

  50. After automatically lo-gin to my website I want to export the table data into Excel sheet. I am trying below code but its not working.I am getting errors like “Excel VBA, error 438 “object doesn’t support this property or method” and “Object Variable- Or With Block Variable is not set: RUn time Error 91”. Can anyone helps me to extract the data from URL and save into Excel.

    Dim htmlDoc As HTMLDocument
    Dim MyBrowser As InternetExplorer

    Private Sub CommandButton_strong text_1_Click()
    Dim oDom As Object
    Dim MyHTML_Element As IHTMLElement
    Dim MyURL As String
    Dim htm As Object
    Dim Tr As Object
    Dim Td As Object
    Dim Tab1 As Object
    Dim x As Long, y As Long

    MyURL = “http://mytabledatat/GC8/glogs.webserver.event.EventDiaglogServlet”
    Set MyBrowser = New InternetExplorer
    MyBrowser.Silent = True
    MyBrowser.Navigate MyURL
    MyBrowser.Visible = True

    Do
    Loop Until MyBrowser.ReadyState = READYSTATE_COMPLETE
    Set htmlDoc = MyBrowser.Document
    htmlDoc.all.username.Value = “TEST”
    htmlDoc.all.userpassword.Value = “CHANGEME”
    Set htmlColl = htmlDoc.getElementsByTagName(“input”)
    Do While htmlDoc.ReadyState <> “complete”: DoEvents: Loop
    For Each htmlInput In htmlColl
    If Trim(htmlInput.Type) = “submit” Then
    htmlInput.Click
    Exit For
    End If
    Next htmlInput

    ‘Create HTMLFile Object
    Set oDom = CreateObject(“htmlfile”)

    ‘Get the WebPage Content to HTMLFile Object
    With CreateObject(“msxml2.xmlhttp”)
    .Open “GET”, “mywebsiteurl”, False
    .send
    oDom.body.innerHTML = .responseText
    End With

    End Sub

  51. Dick,

    when i have used your code and run, I am getting error “Object Required” on
    Sheet1.Select
    “Line of code”. Please help me out.

  52. Sheet1 is the CodeName of the sheet. If you look in the Project Explorer (Ctrl+R in the VBE) you can see all of the sheet’s code names. In this

    http://dailydoseofexcel.com/blogpix/tidyupvbe1.gif

    Under Kwiksheets, you can see that I have one worksheet. The CodeName is wshSettings and the tab name is Sheet1. Find out what the CodeName for your sheet is and change Sheet1 to that.

  53. In Macro, Your code is working.
    But When I am trying to get a data in vb.net code its not working.

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports mshtml
    Imports Scripting
    Imports System.Net
    Imports System.IO

    Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim htmlDoc As HTMLDocument
    Dim MyBrowser As SHDocVw.InternetExplorer
    ‘ Dim fso As Scripting.FileSystemObject

    Dim ieTable As Object
    Dim clip As DataObject
    ‘ Dim sFileName As String
    Dim htmlInput As Object
    Dim htmlColl As Object
    Dim oDom As Object

    MyBrowser = CreateObject(“InternetExplorer.Application”)

    MyBrowser.Silent = True
    MyBrowser.Navigate(“http://b2botm.co.uk/GC3/glog.webserver.servlet.umt.Login”)
    ‘ MyBrowser.Visible = True

    Do
    Loop Until MyBrowser.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE

    htmlDoc = MyBrowser.Document
    htmlDoc.all.username.Value = “TEST”
    htmlDoc.all.userpassword.Value = “CH”

    htmlColl = htmlDoc.getElementsByTagName(“input”)

    Do
    Loop Until htmlDoc.readyState “SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE”
    For Each htmlInput In htmlColl
    If htmlInput.Type = “submit” Then
    htmlInput.Click()
    Exit For
    End If
    Next htmlInput

    Do Until MyBrowser.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE : Loop
    MyBrowser.Navigate(“http://b2botm.co.uk/GC3/glog.webserver.event.EventDiagServlet”)

    Do Until MyBrowser.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE : Loop

    ‘Get the table based on the table’s id
    oDom = MyBrowser.Document

    ieTable = oDom.all.Item(“table_queue_grid”)

    If Not ieTable Is Nothing Then
    clip = New DataObject
    clip.SetText(“” & ieTable.outerHTML & “”)
    clip.GetText()

    End If

    I am unable to copy and paste my table “table_queue_grid” which I want to save . Any Sugesstion

  54. Hi,
    I am working on similar requirement but I am not a VB developer.
    I tried to use the script in a vb script file on my desktop ( not excel) but it is not working.
    I checked on google that format should be different.
    I shall be thankful if anyone can suggest a simple VBScript format ( non excel macro)

    Many thanks

  55. Hi,

    I am trying to implement this and have the login section working, but I have the issue that my table does not have an id. It is the only table on the page however, and I think I have found it by calling it by class using this:

    Set ieTable = ieApp.Document.getElementsByClassName(“stats draggable fixed3”)

    I am however getting a 438 error on this line, can anyone help? I am very new to HTML, so don’t really know what I’m doing!

    clip.SetText “” & ieTable.outerHTML & “”

  56. The problem with this solution is that a login via i.e. is not the same as a login via excel.

    I can login to a password protected site using a browser but that does not mean that I can run a web query from excel and be logged into the same site it requires a login using excels web query box first for a site cookie to be associated with an excel login.

    This solution simply launches a web browser and logs the browser into a site it does not log excel in to be able to run a web query.

  57. Hello ,

    Can you please help me out to get the source code of the link …..

    Thanks in advance for your help…!!!!

    Sudheer G

  58. How to perform this if there is MFA , so after entering userid & password, a new token is received on mobile and needs to be entered. Can this be automated ?

  59. Hello All

    Thanks for all your codes.

    I have one more query .

    Firstly i login the website by using ID & Password , then home page will get open there are multiple tab to click over there and i need to click on ” My Team Attendance” .
    After clicking this only then will get one more option “Show Attendance” when i will click these two options then attendance table will open.

    Please help me that how i click these two options and fetch the data.

    I will really help me a lot.

    Thanks

    Jai Hind

  60. Hey, I am a beginner. Can I extract data from website which is supported only on mozilla firefox in macros and also which has a login?

  61. Hello,

    I want to know how to insert an SQL query to fetch data from the database after login with username and password following using your procedures. An example will be appreciated.
    Thanks.

  62. Dick Kusleika. When I saw your first thread of 2012 now. I thought maybe you are alive or not. You seems to be a legend. and legend doesn’t live long. Sir. I’m running a call center. I use Genesis for that. Genesis gave me a ip based dashboard to watch out the pulse stats. Where i can see how many calls are in the queue and how many answered and abandoned as well. But when a call comes in the Que. It waits for 20 sec or gets dropped. No notification that there’s a call in the queue. So i tried to import this data to excel and tried to use the module. Beepnow. Like when live call section being greater than 0 beepnow function should give me a beep. So i can ask my agents to stay ready. There’s a call. But I can’t do that. Because it’s id password based. And not able to import data. That’s my first problem. And the second problem is even i would be able to import data with your help, but auto-refresh time is one minute but call gets dropped in 20 seconds. Give me another code in which i can refresh my dashboard on excel within every 5 seconds at least. Thank you

  63. Hi,

    I try to use your code in order to log in to a website, when I run the code, it redirects to this page: HTTP 404 Not found”.

    What is the reason for it?

  64. Dear Dick,
    Thank you for this great coding. Please I need your help on a site. It’s more complex than this coding but similar. It’s a password protected site and I know the password. After a successful login code was executed, I will like to use data from the excel sheet column A to get their corresponding results on the website then paste the results in Column B of the excel sheet.
    For example: Excel Column A contains 100 different phone number, what the site does is to manually input one number at a time then you click get location to get the location of that particular number.

    The code I want on excel, is to automatically get all the location of the 100 numbers on Column B of the excel after the code has successful login to the site.

    Thank you boss.

  65. Sub test()

    Dim ie As InternetExplorer
    Dim ht As HTMLDocument
    Set ie = New InternetExplorer

    Dim mySh As Worksheet
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Select
    ActiveSheet.Name = “temp”

    ie.Visible = True
    URL = “https://marketchameleon.com/Account/Login”
    ie.navigate URL

    While ie.Busy
    DoEvents
    Wend

    Set ht = ie.document
    While ie.Busy
    DoEvents
    Wend

    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    Set elems = ht.getElementsByTagName(“input”)
    ‘ ht.getElementsByClassName(“field-wrapper”)(1).Value = “Anil”
    For Each elem In elems
    If elem.Name = “UserName” Then
    elem.Value = “dolly@fddeutschb.com”
    Exit For
    End If
    Next

    Set elems = ht.getElementsByTagName(“input”)
    ‘ ht.getElementsByClassName(“field-wrapper”)(1).Value = “Anil”
    For Each elem In elems
    If elem.Name = “Password” Then
    elem.Select
    elem.Value = “dolly29”
    Exit For
    End If
    Next

    Set elems1 = ht.getElementsByTagName(“button”)
    ‘ ht.getElementsByClassName(“field-wrapper”)(1).Value = “Anil”
    For Each elem In elems1
    If elem.Type = “submit” Then
    elem.Click
    Exit For
    End If
    Next

    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE
    Sheets(“MC2”).Select
    Set jsht = ActiveSheet
    MC2Row = jsht.Cells(jsht.Rows.Count, “B”).End(xlUp).Row
    LastColumn = jsht.Cells(4, jsht.Columns.Count).End(xlToLeft).Column

    For m = 6 To LastColumn
    TickerValue = Worksheets(“MC2”).Cells(4, m).Value
    URL = “https://www.marketchameleon.com/Overview/” & Trim(TickerValue) & “/Earnings/EPS-Results/”

    ie.navigate URL

    Dim tbl As HTMLTable
    Dim trCounter As Integer
    Dim tdCounter As Integer
    Set mySh = Sheets(“temp”)
    trCounter = 1
    tdCounter = 1

    ‘tab_EPS-Results
    ‘sym_earnings

    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    Sheets(“temp”).Select
    Selection.ClearContents

    ie.Visible = False
    ie.Visible = True
    ‘ie.Refresh

    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    Application.Wait (Now() + TimeValue(“0:00:03”))

    For Each tbl In ie.document.getElementById(“symov_main”).getElementsByTagName(“table”)
    ColumnCount = 0

    For Each Tr In tbl.getElementsByTagName(“tr”)
    tbl.getElementsByTagName (“tr”)
    ColumnCount = ColumnCount + 1
    For Each Td In Tr.getElementsByTagName(“td”)

    ‘If trCounter > 4 Then Exit For
    mySh.Cells(trCounter, tdCounter).Value = Td.innerText

    tdCounter = tdCounter + 1
    Next Td
    tdCounter = 1
    trCounter = trCounter + 1
    Next
    Next

    Call Reformat_Data(TickerValue)
    Next
    Sheets(“temp”).Select
    ActiveWindow.SelectedSheets.Delete
    End Sub

    Sub Reformat_Data(Ticker)
    PriceValue = “”
    YearQuarter = “”
    Dim sht As Worksheet
    Dim LastRow As Long
    Sheets(“temp”).Select
    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.Count, “A”).End(xlUp).Row
    Sheets(“MC2”).Select
    Set jsht = ActiveSheet
    MC2Row = jsht.Cells(jsht.Rows.Count, “B”).End(xlUp).Row
    LastColumn = jsht.Cells(4, jsht.Columns.Count).End(xlToLeft).Column

    For i = 1 To LastRow
    Worksheets(“temp”).Select
    QuarterValue = Range(“C” & i).Value
    QuarterExist = InStr(QuarterValue, “Q”)
    YearValue = Left(QuarterValue, 4)
    If InStr(YearValue, “Earnings Reported”) > 1 And QuarterExist >= 1 And Len(QuarterValue) > 4 Then
    YearValue = Left(QuarterValue, 4)
    Else
    YearValue = Left(QuarterValue, 4)
    End If

    If QuarterExist >= 1 And YearValue “” And Len(QuarterValue) > 4 Then
    PriceValue = Left(Sheets(“temp”).Range(“D” & i).Value, 6)
    YearQuarter = QuarterValue

    For k = 6 To LastColumn
    GetTicker = Sheets(“MC2”).Cells(4, k).Value
    If Ticker = GetTicker Then
    For j = 7 To MC2Row
    MC2Year = Sheets(“MC2”).Range(“B” & j).Value
    If MC2Year = YearQuarter Then
    Sheets(“MC2”).Cells(j, k).Value = PriceValue
    GoTo escape:
    End If
    Next
    End If
    Next
    escape:
    End If

    Next

    End Sub

  66. Hi,
    Iam using your code to extract data from my intranet web application to excel. It is a .iql query. It’s working exactly as expected.
    But the problem is, after saving the file, I close it and reopen. Now if click “Refresh all” data is not refreshed. Exciting data wiped off and some error appears.
    If I click “Data”->”From Web” once and login to my site again close the window and click “Refresh All” then it is working fine. Please helpe to avoid this step. I using your code on a click of a vba button.
    Thanks in advance.

Comments are closed.


Posting code? Use <pre> tags for VBA and <code> tags for inline.