This actually takes us right where we want to be. Next we populate the two controls, login and password, and submit it. However, rather than check to see if we get to the right page, I go login every time. This wouldn’t be necessary if you already had a cookie remembering that you logged in. The code first goes directly to the login page. ‘copy the tables html to the clipboard and paste to teh sheet ‘now that we’re in, go to the page we want ‘fill in the login form – View Source from your browser to get the control names ‘assume we’re not logged in and just go directly to the login pageĭo Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop ‘you don’t need this, but it’s good for debugging I have all the data I need to produce the code 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. 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. I note that the id for the table I want is “sampletable”. Next, I go to the page I ultimately want and look at its source. I also note that the input type is submit. I take note of the names of the two controls on the form, login and password. I go to the login page using Firefox and choose Page Source form the View menu. I’ll use it to put some text into the clipboard.īefore I get to the code, I need to do a little homework. This will already be checked if you have a userform in your project.

While you’re in the References dialog, set a reference to Microsoft Forms 2.0 Library. To automate Internet Explorer, we’ll need to set a reference to Microsoft Internet Controls. Only after logging in can you get to the goods. If you attempt to get the table, the site recognizes that you’re not logged in and sends you to the login page. I steal that table for all my examples, so don’t tell Debra. Behind the login, there’s a table that I stole from. The username is dailydose and the password is password. I’ll be using a website I created to demonstrate. This example shows how to login, retrieve a table, and paste it into Excel. By automating Internet Explorer and the login process, you can get to that data. 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.

Web query excel 2011 mac