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.
Nice stuff Dick.
You may want to modify the wait code so it exits after some time, otherwise it just hangs there forever.
You don’t need IE for this. I did it with MSXML and no early bound references, it’s also faster:
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
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
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??
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…
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.
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.
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?
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.
The username is “dailydose” and the password is “password”. You won’t see those in the source code.
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
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.
I support it
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.
I have been trying to get the mails directly in to the excel sheet.
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!!!
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}”
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
Matter, thanks for the link. It’s definitely worthwhile, and I’ve bookmarked it! Grazie mille!
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
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
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?
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.
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
That was it..thanks. I needed to adjust the time from 3 to 10. works perfectly. thanks.
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
Matthew: Search for this string
That’s the username. The next div is the password and the one after that is the login button.
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
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.
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.
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.
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:
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
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.
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!
Nope. Anything that processes server-side isn’t going to be much help when you’re parsing html. Sorry.
Hi,
Looking for VBA code that extracts details from Webportal.
For instance need to extract details using roll numbers.
thanks!
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
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
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
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.
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
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!
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…….
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
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
Set a reference (Tools – Reference) to the Microsoft XML library and the Microsoft HTML library.
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
It’s .Submit that “clicks” the button, so you are doing it.
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
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
No, it will only pull the table with an id of sampletable at the URL supplied.
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
—————————
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!
please help to:
http://aplikacje.neonet.pl/UI/Login.aspx
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
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
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
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
•
Which line gives the error?
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?
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.
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
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
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.
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 changeSheet1
to that.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
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
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 & “”
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.
Hello ,
Can you please help me out to get the source code of the link …..
Thanks in advance for your help…!!!!
Sudheer G
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 ?
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
hi i would like your help for open website and get data by excel vba
Hello, Please help me to find Table name in page source code.
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?
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.
Hi, would this work if the website uses 2 factor authentication? Thank you!
No, the second factor would be unavailable to VBA, so it wouldn’t work.
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
hello,
I am looking to login to Linkedin with this.
Anyone can help me?
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?
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.
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
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.