Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
Hi, I need to do the foll. from excel vba
I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
This task isn't simple. I need to know how much programming experience you
have to determine if I cna help you through the process. I assume you don't want to give out a pasword for me to do the task. If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
this is the web https://alertweb.omgeo.net/cleartrust/ct_logon.jsp
On Jul 21, 6:31*pm, Joel wrote: This task isn't simple. *I need to know how much programming experience you have to determine if I cna help you through the process. *I assume you don't want to give out a pasword for me to do the task. *If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. *Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
try this. Put your password into the code.
Sub OpenWeb() 'put your user name and password here myname = "UserName" mypassword = "Password" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'get web page IE.Navigate2 URL Do While IE.Readystate < 4 And IE.busy = True DoEvents Loop Set UserName = IE.document.getElementById _ ("app_login_acronym_selector_dd_user_id") Set Password = IE.document.getElementById _ ("app_login_acronym_selector_dd_password") Set form = IE.document.getElementsBytagname("Form") UserName.Children(0).Value = myname Password.Children(0).Value = mypassword form(0).submit Do While IE.busy = True And IE.Readystate < 4 DoEvents Loop 'dump the document to the worksheet RowCount = 1 '--------------------------------------------------------------------------- For Each itm In IE.document.all Range("A" & RowCount) = itm.tagname Range("B" & RowCount) = itm.classname Range("C" & RowCount) = itm.tagname Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm '--------------------------------------------------------------------------- IE.Quit End Sub "fi.or.jp.de" wrote: this is the web https://alertweb.omgeo.net/cleartrust/ct_logon.jsp On Jul 21, 6:31 pm, Joel wrote: This task isn't simple. I need to know how much programming experience you have to determine if I cna help you through the process. I assume you don't want to give out a pasword for me to do the task. If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
really good one. I am looking for your reply.
Or let me know which book can i refer. after login, i need select drop down value and then again click enter It will take to main screen then again I need to go for some tab. I was using the below one for login Option Explicit Sub IE_login() Dim ie As InternetExplorer Dim C Dim ULogin As Boolean, ieForm, ieOption Dim MyPass As String, MyLogin As String, myac As String redo: MyLogin = Worksheets("sheet1").TextBox1.Value MyPass = Worksheets("sheet1").TextBox2.Value If MyLogin = "" Or MyPass = "" Then GoTo redo Set ie = New InternetExplorer ie.Visible = True ie.Navigate "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'Loop until ie page is fully loaded Do Until ie.ReadyState = READYSTATE_COMPLETE Loop 'Look for password Form by finding test "Password" For Each ieForm In ie.Document.forms If InStr(ieForm.innerText, "Password") < 0 Then ULogin = True ieForm(1).Value = MyLogin ieForm(2).Value = MyPass ieForm.submit Exit For Else End If Next If ULogin = False Then MsgBox "User is aleady logged in" Set ie = Nothing End Sub Sub SetRefs() Dim ObRef On Error Resume Next ' Adds Internet Controls Ref ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF- A7EB-0000C05BAE0B}", 1, 1 End Sub On Jul 21, 10:18*pm, Joel wrote: try this. *Put your password into the code. Sub OpenWeb() 'put your user name and password here myname = "UserName" mypassword = "Password" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'get web page IE.Navigate2 URL Do While IE.Readystate < 4 And IE.busy = True * *DoEvents Loop Set UserName = IE.document.getElementById _ * *("app_login_acronym_selector_dd_user_id") Set Password = IE.document.getElementById _ * *("app_login_acronym_selector_dd_password") Set form = IE.document.getElementsBytagname("Form") UserName.Children(0).Value = myname Password.Children(0).Value = mypassword form(0).submit Do While IE.busy = True And IE.Readystate < 4 * *DoEvents Loop 'dump the document to the worksheet RowCount = 1 '--------------------------------------------------------------------------*- For Each itm In IE.document.all * *Range("A" & RowCount) = itm.tagname * *Range("B" & RowCount) = itm.classname * *Range("C" & RowCount) = itm.tagname * *Range("D" & RowCount) = Left(itm.innertext, 1024) * *RowCount = RowCount + 1 Next itm '--------------------------------------------------------------------------*- IE.Quit End Sub "fi.or.jp.de" wrote: this is the webhttps://alertweb.omgeo.net/cleartrust/ct_logon.jsp On Jul 21, 6:31 pm, Joel wrote: This task isn't simple. *I need to know how much programming experience you have to determine if I cna help you through the process. *I assume you don't want to give out a pasword for me to do the task. *If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. *Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
I don't know any book to help with this problem. Without seeing the webpage
I can't directly help you. You need to find the object of the drop down box. The code I gave you has some debug statements that will dump the objects to your worksheet. Then look at the worksheet to give you clues to the object names. You can either use ID or tagname like the statements below. Sometimes webpages have table as a tag name which is helpful. Set Password = IE.document.getElementById("app_login_acronym_sele ctor_dd_password") Set form = IE.document.getElementsBytagname("Form") "fi.or.jp.de" wrote: really good one. I am looking for your reply. Or let me know which book can i refer. after login, i need select drop down value and then again click enter It will take to main screen then again I need to go for some tab. I was using the below one for login Option Explicit Sub IE_login() Dim ie As InternetExplorer Dim C Dim ULogin As Boolean, ieForm, ieOption Dim MyPass As String, MyLogin As String, myac As String redo: MyLogin = Worksheets("sheet1").TextBox1.Value MyPass = Worksheets("sheet1").TextBox2.Value If MyLogin = "" Or MyPass = "" Then GoTo redo Set ie = New InternetExplorer ie.Visible = True ie.Navigate "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'Loop until ie page is fully loaded Do Until ie.ReadyState = READYSTATE_COMPLETE Loop 'Look for password Form by finding test "Password" For Each ieForm In ie.Document.forms If InStr(ieForm.innerText, "Password") < 0 Then ULogin = True ieForm(1).Value = MyLogin ieForm(2).Value = MyPass ieForm.submit Exit For Else End If Next If ULogin = False Then MsgBox "User is aleady logged in" Set ie = Nothing End Sub Sub SetRefs() Dim ObRef On Error Resume Next ' Adds Internet Controls Ref ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF- A7EB-0000C05BAE0B}", 1, 1 End Sub On Jul 21, 10:18 pm, Joel wrote: try this. Put your password into the code. Sub OpenWeb() 'put your user name and password here myname = "UserName" mypassword = "Password" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'get web page IE.Navigate2 URL Do While IE.Readystate < 4 And IE.busy = True DoEvents Loop Set UserName = IE.document.getElementById _ ("app_login_acronym_selector_dd_user_id") Set Password = IE.document.getElementById _ ("app_login_acronym_selector_dd_password") Set form = IE.document.getElementsBytagname("Form") UserName.Children(0).Value = myname Password.Children(0).Value = mypassword form(0).submit Do While IE.busy = True And IE.Readystate < 4 DoEvents Loop 'dump the document to the worksheet RowCount = 1 '--------------------------------------------------------------------------Â*- For Each itm In IE.document.all Range("A" & RowCount) = itm.tagname Range("B" & RowCount) = itm.classname Range("C" & RowCount) = itm.tagname Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm '--------------------------------------------------------------------------Â*- IE.Quit End Sub "fi.or.jp.de" wrote: this is the webhttps://alertweb.omgeo.net/cleartrust/ct_logon.jsp On Jul 21, 6:31 pm, Joel wrote: This task isn't simple. I need to know how much programming experience you have to determine if I cna help you through the process. I assume you don't want to give out a pasword for me to do the task. If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
Ok, Thanks joel.
On Jul 21, 11:30*pm, Joel wrote: I don't know any book to help with this problem. *Without seeing the webpage I can't directly help you. *You need to find the object of the drop down box. *The code I gave you has some debug statements that will dump the objects to your worksheet. *Then look at the worksheet to give you clues to the object names. *You can either use ID or tagname like the statements below. * Sometimes webpages have table as a tag name which is helpful. Set Password = IE.document.getElementById("app_login_acronym_sele ctor_dd_password") Set form = IE.document.getElementsBytagname("Form") "fi.or.jp.de" wrote: really good one. I am looking for your reply. Or let me know which book can i refer. after login, i need select drop down value and then again click enter It will take to main screen then again I need to go for some tab. I was using the below one for login Option Explicit Sub IE_login() * * Dim ie As InternetExplorer * * Dim C * * Dim ULogin As Boolean, ieForm, ieOption * * Dim MyPass As String, MyLogin As String, myac As String redo: * * MyLogin = Worksheets("sheet1").TextBox1.Value * * MyPass = Worksheets("sheet1").TextBox2.Value * * If MyLogin = "" Or MyPass = "" Then GoTo redo * * Set ie = New InternetExplorer * * ie.Visible = True * * ie.Navigate "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" * * 'Loop until ie page is fully loaded * * Do Until ie.ReadyState = READYSTATE_COMPLETE * * Loop * * 'Look for password Form by finding test "Password" * * For Each ieForm In ie.Document.forms * * * * If InStr(ieForm.innerText, "Password") < 0 Then * * * * * * ULogin = True * * * * * * ieForm(1).Value = MyLogin * * * * * * ieForm(2).Value = MyPass * * * * * * ieForm.submit * * * * * * Exit For * * * * Else * * * * End If * * Next * * If ULogin = False Then MsgBox "User is aleady logged in" * * Set ie = Nothing End Sub Sub SetRefs() * * Dim ObRef * * On Error Resume Next * * ' Adds Internet Controls Ref * * ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF- A7EB-0000C05BAE0B}", 1, 1 End Sub On Jul 21, 10:18 pm, Joel wrote: try this. *Put your password into the code. Sub OpenWeb() 'put your user name and password here myname = "UserName" mypassword = "Password" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'get web page IE.Navigate2 URL Do While IE.Readystate < 4 And IE.busy = True * *DoEvents Loop Set UserName = IE.document.getElementById _ * *("app_login_acronym_selector_dd_user_id") Set Password = IE.document.getElementById _ * *("app_login_acronym_selector_dd_password") Set form = IE.document.getElementsBytagname("Form") UserName.Children(0).Value = myname Password.Children(0).Value = mypassword form(0).submit Do While IE.busy = True And IE.Readystate < 4 * *DoEvents Loop 'dump the document to the worksheet RowCount = 1 '--------------------------------------------------------------------------*- For Each itm In IE.document.all * *Range("A" & RowCount) = itm.tagname * *Range("B" & RowCount) = itm.classname * *Range("C" & RowCount) = itm.tagname * *Range("D" & RowCount) = Left(itm.innertext, 1024) * *RowCount = RowCount + 1 Next itm '--------------------------------------------------------------------------*- IE.Quit End Sub "fi.or.jp.de" wrote: this is the webhttps://alertweb.omgeo.net/cleartrust/ct_logon.jsp On Jul 21, 6:31 pm, Joel wrote: This task isn't simple. *I need to know how much programming experience you have to determine if I cna help you through the process. *I assume you don't want to give out a pasword for me to do the task. *If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. *Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate data from Web to Excel.
I tried your codes below and I got an run-time error 91 on the followingline:
UserName.Children(0).Value = myname Thanks, TP "Joel" wrote: try this. Put your password into the code. Sub OpenWeb() 'put your user name and password here myname = "UserName" mypassword = "Password" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://alertweb.omgeo.net/cleartrust/ct_logon.jsp" 'get web page IE.Navigate2 URL Do While IE.Readystate < 4 And IE.busy = True DoEvents Loop Set UserName = IE.document.getElementById _ ("app_login_acronym_selector_dd_user_id") Set Password = IE.document.getElementById _ ("app_login_acronym_selector_dd_password") Set form = IE.document.getElementsBytagname("Form") UserName.Children(0).Value = myname Password.Children(0).Value = mypassword form(0).submit Do While IE.busy = True And IE.Readystate < 4 DoEvents Loop 'dump the document to the worksheet RowCount = 1 '--------------------------------------------------------------------------- For Each itm In IE.document.all Range("A" & RowCount) = itm.tagname Range("B" & RowCount) = itm.classname Range("C" & RowCount) = itm.tagname Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm '--------------------------------------------------------------------------- IE.Quit End Sub "fi.or.jp.de" wrote: this is the web https://alertweb.omgeo.net/cleartrust/ct_logon.jsp On Jul 21, 6:31 pm, Joel wrote: This task isn't simple. I need to know how much programming experience you have to determine if I cna help you through the process. I assume you don't want to give out a pasword for me to do the task. If you give me the login URL I can at least get you started by setting up a macro where you can enter the account and password your self. Each webpage is diferent so there isn't any generalized code that will work. Each webpage requires custom software to retrieve the data. "fi.or.jp.de" wrote: Hi, I need to do the foll. from excel vba I need to login to the website then I need to choose the list from dropdown Select particulat in web then I need to copy any row which has check box. add new workbook paste the data.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automate data entry on Excel? | Excel Discussion (Misc queries) | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
How can I automate filling in a Web form with Excel data? | Excel Programming | |||
automate data transfer from hyperterminal to excel | Excel Programming | |||
How do I automate upload of data from one excel file to another? | Excel Worksheet Functions |