Automate data from Web to Excel.

[fi.or.jp.de]

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.

 

[Joel]

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]

this is the web https://alertweb.omgeo.net/cleartrust/ct_logon.jsp

 

[Joel]

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]

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

 

[Joel]

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_selector_dd_password")
Set form = IE.document.getElementsBytagname("Form")

 

[fi.or.jp.de]

Ok, Thanks joel.

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_selector_dd_password")
Set form = IE.document.getElementsBytagname("Form")

 

[tp803]

I tried your codes below and I got an run-time error 91 on the followingline:

UserName.Children(0).Value = myname

Thanks,

TP