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 "<html>" & ieTable.outerHTML & "</html>"
        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.

50 Comments

  1. Nice stuff Dick.
    You may want to modify the wait code so it exits after some time, otherwise it just hangs there forever.

  2. JP says:

    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

  3. Ben says:

    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

  4. Alain says:

    Thank you. I was looking for this.

    However, how may I do this when the form involves a lot of hidden input elements?

    The login I’m looking for is this: https://www.santander.cl/

    Could you help me out??

  5. Greg Arikian says:

    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.

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

  7. smith says:
    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?

  8. joran says:

    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.

  9. The username is “dailydose” and the password is “password”. You won’t see those in the source code.

  10. Matthias says:

    Hi Guys.. I been reading the read and I am still having problem locating the value for log in. I’m trying to import the website into excel, but it only works on my com due to the log in issue. How do i solve it? The website I’m trying to extract from is. http://holideal.sg/manage/team/success.php

  11. Brian says:

    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.

  12. trx says:

    I support it

  13. Prateesh says:

    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.

  14. Prateesh says:

    I have been trying to get the mails directly in to the excel sheet.

  15. Prateesh says:

    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!!!

  16. Prateesh says:

    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}”

  17. Todd Rimes says:

    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

  18. Valrie Arjune says:

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

  19. Jon Lake says:

    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

  20. Dick Kusleika says:

    Jon: The answer is no. But if you want it to send automatically, going through Firefox/Gmail doesn’t add much. Try here http://www.rondebruin.nl/cdo.htm

  21. Mike Victor says:

    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?

  22. Mike Victor says:

    the link is https://www.sygration.com/cgi-bin/login?/cgi-bin/dashboard. I cant post the password here but i could send it privately.

  23. Dick Kusleika says:

    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
  24. Mike Victor says:

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

  25. Matthew says:

    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

  26. Dick Kusleika says:

    Matthew: Search for this string

    <input name="ctl00$ContentPlaceHolder4$loginbeta$textUsername$_textbox"

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

  27. Matthew says:

    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

  28. Dick Kusleika says:

    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.

  29. Ankit says:

    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.

  30. Dick Kusleika says:

    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.

  31. sam says:

    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:

  32. Ronni says:

    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 "" &amp; ieTable.outerHTML &amp; ""
            clip.PutInClipboard
            Sheet1.Select
            Sheet1.Range("A1").Select
            Sheet1.PasteSpecial "Unicode Text"
        End If
       
        'close 'er up
       ieApp.Quit
        Set ieApp = Nothing
       
    End Sub
  33. 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 "" &amp; MieTable.innerHTML &amp; ""
            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.

  34. [...] Get Data from Website that Requires a Login. Like this:LikeBe the first to like [...]

  35. John says:

    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!

  36. Dick Kusleika says:

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

  37. Dev says:

    Hi,

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

    thanks!

  38. Shlodge says:

    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

  39. Praveen says:

    Hi All,

    am not able to login to https://otsuka.service-now.com/ using the above method.

    kindly help me out.

    Thanks in advance.

    Regards,
    Praveen

  40. J.L. says:

    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

  41. Dick Kusleika says:

    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.

  42. J.L. says:

    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

  43. M.D. says:

    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!

  44. vba code says:

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

  45. Duncan says:

    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

  46. Dick Kusleika says:

    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
  47. Dick Kusleika says:

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

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: