PDA

View Full Version : Solved: Help with IE loop Automation



colonna
03-21-2013, 04:12 AM
Hello all,

What im trying to do is a basic web scraper in VBA. At this point i `ve made a script that does what i need , except ... LOOP.
My code is bellow. Im not violating the copyrights , as there is no username or password envolved ... nor any copyright written or accepted .
In the Sheet1 , i have all the Id`s for make and models as well as all the other selection in the "E "column.

what i deed is , that For each row in the sheet1 , to run the script and return data.


Any help will be apreciated.
Sub Macro1()'
' Macro1 Macro
'
'Open the URL
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Navigate "website"
IE.Visible = True 'Can be false/hidden




'Wait for the page to finish loading
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop




IE.Document.getElementById("MARKE").Focus
IE.Document.getElementById("MARKE").selectedIndex = Sheets("Sheet1").Range("A2")
IE.Document.getElementById("MARKE").FireEvent ("onchange")


Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop
IE.Document.getElementById("MODELL").Focus
IE.Document.getElementById("MODELL").selectedIndex = Sheets("Sheet1").Range("C2")
IE.Document.getElementById("MODELL").FireEvent ("onchange")
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop


Dim link As Object

For Each link In IE.Document.Links
Debug.Print link.innertext
Next link


Set doc = IE.Document
For Each link In IE.Document.Links
If link.innertext = Sheets("Sheet1").Range("E2") Then link.Click
Next link
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop


Dim data


data = IE.Document.body.innertext


Sheets("Sheet2").Range("A1") = data

End Sub

colonna
03-22-2013, 12:53 AM
Noone ??? Please help.

Kenneth Hobs
03-22-2013, 07:13 AM
Welcome to the forum!

It is a bit hard to see what you need. Maybe you want the row number? In this example, you can get the row number by c.Row. Run this on an empty Sheet1 activeworkbook.

Notice how I iterate the cells in Column A and use the Offset function to get Column B data. You will probably want to use "&" to build your cell reference in a similar manner.

Sub ken()
Dim cRow As Long, c As Range

'Build a set of data to test in iteration
For cRow = 2 To 10
With Worksheets("Sheet1")
.Range("A" & cRow).Value2 = cRow
.Range("B" & cRow).Value2 = cRow + 1
End With
Next cRow

'Now iterate rows 2 to last row in sheet1 in column A
'Show the cell addresses and values for columns A and B, one row at a time.
For Each c In Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
With c
MsgBox .Address & ": " & .Value2 & vbLf & .Offset(0, 1).Address & ": " & .Offset(0, 1).Value2
End With
Next c
End Sub

colonna
03-22-2013, 07:38 AM
Hello Kenneth and thank you for your help.
What i`m trying to do is , use the data in the Sheet1 to filter the info on the website. Right now , when i run the script , it will start IE , and filter my infos based on 3 cells that contains the info.( the "A2" , "c2" and "E2"). Then will get the data into Sheet2.

What i need is to loop the process in sequnce like this . first run ( the "A2" , "c2" and "E2") + get data , the run thru ( the "A3" , "C3" and "E3") + Get data and so on till there is no more data in the sheet 1. If u want , i can send u the file on e-mail.

Again Big TY. for your help ... and apologies for my bad engleish.

Kenneth Hobs
03-22-2013, 08:01 AM
Just do it like I did. Replace the MsgBox line with your code there but with the concatenated string for the cell name like in the MsgBox. In this example, put data into Sheet1's A, B, and C columns for say rows 2, 3, and 4 and then play this.
Sub ken()
Dim c As Range
For Each c In Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
With Worksheets("Sheet1")
MsgBox "A" & c.Row & ": " & .Range("A" & c.Row) & vbLf & _
"B" & c.Row & ": " & .Range("B" & c.Row).Value2 & vbLf & _
"C" & c.Row & ": " & .Range("C" & c.Row).Value2
End With
Next c
End Sub

colonna
03-22-2013, 09:09 AM
Hello my friend , it looks better ... but i still have a problem. When it goes to the second row , it doesen`t select the 3rd filter , wich is made with a For each. Another BIG TY. Can you tell me what`s wrong ?



Sub ken()
Dim c As Range
For Each c In Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
With Worksheets("Sheet1")
Dim IE As Object
Dim link As Object
Set IE = CreateObject("internetexplorer.application")
IE.Navigate "website(i can tell u the website)"
IE.Visible = True 'Can be false/hidden

'Wait for the page to finish loading
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop

IE.Document.getElementById("MARKE").Focus
IE.Document.getElementById("MARKE").selectedIndex = Sheets("Sheet1").Range("A" & c.Row) & vbLf
IE.Document.getElementById("MARKE").FireEvent ("onchange")

Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop

IE.Document.getElementById("MODELL").Focus
IE.Document.getElementById("MODELL").selectedIndex = Sheets("Sheet1").Range("C" & c.Row).Value2 & vbLf
IE.Document.getElementById("MODELL").FireEvent ("onchange")

Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop
For Each link In IE.Document.Links
Debug.Print link.innertext
Next link


Set doc = IE.Document
For Each link In IE.Document.Links
If link.innertext = Sheets("Sheet1").Range("E" & c.Row) Then link.Click

Next link

Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop
Dim data

data = IE.Document.body.innertext

Sheets("Sheet2").Range("A1") = data

End With
Next c
End Sub

colonna
03-22-2013, 11:01 AM
My friend , Ur A GENIUS . The problem was that at the end of the loop , i should give 2 seconds for browser to quit . :)

An now ...... :) the last thing ... i need to format the text. As u see in my code , the text into .range"A1".
It has to be like the filter element , with the filter text before the info. Any idea .?

Kenneth Hobs
03-22-2013, 11:07 AM
I don't know what you mean by filter. Obviously, innertext is not formatted at all.

Try attaching an XLSM file and show what you need.

colonna
03-23-2013, 02:09 AM
in the sheet2 , you can see the data . but it should look like in the sheet 3.
There is olso a character like ? in a rectangle. It should be something like this :

COL A - The first filter infos
COL B - The second filter info
COL C - The 3rd filter info
and
COL D - the info, with formatting.

Thank you in advance. The "xlsm " is the actual project in macros, module 2, is the one that does what i need.

How can i repay for your help ?
Im an IT engineer in Romania, with a lot of knowledge ... in Hardware (everything you can imagine ) , Win servers , SQL management , SSIS , SSRS . etc

colonna
03-23-2013, 02:14 AM
i atach here a xlsx , made with a different method .... but requires a lot of time .

colonna
03-23-2013, 02:29 AM
As idea , this project is ment to help a frind of mine . The kind of friend that leaves his pregnant wife in the hospital and comes to help me with a tire , in winter time , a distance of 400 km. Without telling me the situation ..... and never asked nothing in return. He is a network admin at a company , and wants to become a sys admin. But he needs this database for a project.

AGAIN , THANK YOU IN ADVANCE ... i`ll repay ur help.

Kenneth Hobs
03-23-2013, 06:47 AM
A simple thankyou is sometimes given with the thread marked as solved.

Your English seems very good to me.

If there was a web site, in English hopefully, that can illustrate what you need, it would help.

I am thinking that XML might help. I may have to look for the example that I did some time back.

I suspect that you may need to find the font used and then set the cell's font to that providing you have that font installed on your computer.

colonna
03-23-2013, 01:02 PM
Hello my friend, the loop select infos from two combobox and one link from a hyperlink. Then it opens the info page. When i get the info page i must get olso the info of selection. Olso the ? Mark in a rectangle is a break char of mysql , a char that cannot be read by vba. Please take a look at my xlsm.

colonna
03-29-2013, 08:15 AM
still need help with formating , basically .. i need to take parts of text , from each cell and copy to desired collumns. The text should be delimited with text. How can i do that ?