PDA

View Full Version : Almost there: Loop to search website



dmon99
06-27-2014, 06:27 AM
Hello all I am working on a macro that will open a webpage and search it. The macro will loop through a list of values and search the site for specific information. If the information exist on the site then the macro will enter "Y" in the corresponding cell of column F, if the website does not have the information then the macro will enter "N". Also I will need to skip values that have a "Y" in its corresponding row. This is needed because information on the website is updated daily, so this macro will be run periodically to capture the new information. I have a good portion of the code completed and what I have works great. I've tested the loop and it will search 30K+ values with no errors. The would like the array to be adapted to skip the listed values in the array, they don't need to be searched. I need help adding the next operation of the code. The code is below. Any help is greatly appreciated.



Sub SearchESN()
Dim I As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

Dim MyFolder As String
Dim MyFile As String

Dim MyVals As Variant
MyVals = Array("*472908*", "*471905*", "*471914*", "*471935*", "*471917*", "*471920*", "*471933*", "*471932*", "*471934*") 'Enter all the values to search for


' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = True
' Go to website
IE.Navigate "url goes here"
' Statusbar
Application.StatusBar = "THIS PAGE is loading. Please wait..."
' Wait while IE loading...
' Do While IE.Busy
' Application.Wait DateAdd("s", 1, Now)
' Loop
' IE.Visible = True
' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""

MyFolder = "file location goes here"
MyFile = Dir(MyFolder & "\*Data Loop.xls")

Workbooks.Open Filename:=MyFolder & "\" & MyFile

MyFile = Dir

Application.Goto Range("A2"), False

Do Until IsEmpty(ActiveCell)

For Each esn In Selection
For I = LBound(MyVals) To UBound(MyVals)
If esn.Value Like MyVals(I) Then
esn.Interior.ColorIndex = 6 'yellow
Exit For
End If
Next I
Next esn

ActiveCell.Offset(1, 0).Select
Loop
Application.Goto Range("A2"), False
End Sub

11875

11876

westconn1
06-27-2014, 02:57 PM
try like


For Each esn In range("a:a")
if isempty(esn) then exit for
if esn.offset(, 5) = "N" then
' get info from wedpage
end if
Next esn

snb
06-28-2014, 01:06 PM
Why not using a webquery ?

dmon99
06-30-2014, 05:31 AM
Why not using a webquery ?

I was not sure that query would pull the data and input to the sheet like a macro. I will give it a try. Thanks.

dmon99
06-30-2014, 09:47 AM
The query is suffucient to get the required information. Each esn had the be searched and then the page checked for the need data. If the data exist then put a Y in the 6th cell of the same row of currently search esn.

dmon99
07-01-2014, 12:02 PM
The query is suffucient to get the required information. Each esn had the be searched and then the page checked for the need data. If the data exist then put a Y in the 6th cell of the same row of currently search esn.

I meant to say a webquery is insufficient to get the required information.