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.
[VBA]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
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
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.
[VBA]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[/VBA]
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.
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.
[vba]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
[/vba]
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 ?
[VBA]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
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 .?
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
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.
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.
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 ?
Reply With Quote
