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
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