VBA to Pull Data from Log in Website

**rlee12** · 03-02-2013, 08:45 AM

Hello. I am new to excel program and am experience some issues. I am attempting to use excel to log in to a web page and navigate to the correct sub page before copying a table and pasting it into excel.

The issues I am having are below:

1) The first selection is in a drop down box.

2) It opens up a new window you enter user name

3) It needs to log out after finished

I have wrote some code that works using sendkeys... but there has to be a better way of using code to find the specified drop down list, text box, or table on a webpage.

Please Login or Register  to view this content.

**AKK9** · 03-02-2013, 10:15 AM

You can use the HTML Document object to get data from particular elements in a webpage. For example, my code below searches for "tomato" in Google then puts the 10 results into a spreadsheet.

You need to add references to Microsoft Internet Controls and Microsoft HTML Object Library.

Please Login or Register  to view this content.

Also, you can use it to manipulate input elements on a page. For example, if you know there is an input box with id "user", and a button with id "submit", you could do the following:

Please Login or Register  to view this content.

You can also get elements by Name or Class name. To find id's and classes of things on the page just look at the web page source, the F12 Inspect functionality in Google Chrome is really useful for this, I think Firefox has something similar.

Does this help?

**rlee12** · 03-02-2013, 09:15 PM

Still having issues... I attempted to use your code for Google tomato search, but it wouldn't run. I am using Excel 2013. The code below failed...

Please Login or Register  to view this content.

Looking at the source code for the website proved useful as I found ids, but I do not know how to call them out to manipulate them.

I am attempting to select an option from a drop down menu on the website, then login using the text box that appears. I will continue to search for answers in this forum...

**AKK9** · 03-02-2013, 09:35 PM

Did you add the Microsoft HTML Object Library reference in Tools > References?

I gave an example of how to manipulate the elements:

Please Login or Register  to view this content.

That will find an input on the page with id "user" and type "ThisIsMyUserName" in that box. (You can fill in your user/pass with this)

Please Login or Register  to view this content.

That will find an input on the page with id "submit" and Click it. (You can click the login button with this)

Which website are you doing this on?

**rlee12** · 03-03-2013, 08:43 AM

I modified the code you posted to tailor to my needs:

Please Login or Register  to view this content.

It pulls open explorer, goes to the website, and stops. I must have the wrong Id. I tried various combinations after looking through the source code for the web site (www.scbtonline.com). It looks like a script is present before the code I am interest in, but I do not know how I need to modify the code to deal with that.

I am trying to get it to pick an option from a drop down list. I assume that first consists of getting the Id of the drop down box, then clicking it and going down to whichever selection I want.

Essentially I want to automatical login to my bank account and pull data for budgeting.

**AKK9** · 03-04-2013, 06:42 AM

Yeah, looks like you have the wrong id. You're looking for the "input" (buttons/textboxes) and "select" (dropdowns) elements to manipulate.

Ok, so I've changed the code which will allow you to make your dropdown selections and type in your Access ID and submit the form, but, this opens a new window where you enter your password. So I had to add some code which finds the new window and then lets you enter your password and submit that. I'm using Windows 7 and Internet Explorer 10 and this works for me, but I think it you're might behave differently based on your Windows/Internet Explorer version.

Try it out, if it fails let me know your Windows and Internet Explorer version.

Please Login or Register  to view this content.

**rlee12** · 03-04-2013, 10:44 PM

I am running Explorer 10, Windows 7, and Excel 2013. I added the reference libraries you selected and copied your code into excel. I am unable to get past this code:

Please Login or Register  to view this content.

It does not select the appropriate selection in the drop down box. It sits idle. Is there a database of commands and descriptions in excel? I do not know what the "value" is for or what .setAttribute does. Could it be that the newer excel version has different commands?

**AKK9** · 03-09-2013, 10:31 AM

What actually happens when it gets to that line? It throws an error?

Also, setting values for the elements like this won't trigger the visual changes you see when you use the page, but it will make the necessary changes to the form and submit the correct values when the form is submitted.

See here for HTML reference if it helps: http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

I've also added 1 extra line to the code in my last post so please use that code.