1

I have the code written and am attempting to pull several individual pieces of data from a website but my macro pulls several duplicates over and over.

Sub GetData()
Application.ScreenUpdating = False
Dim objIE As InternetExplorer
Dim itemEle As Object
Dim desc As String, pt1 As String, pt2 As String, price As String
Dim y As Integer
Dim Number As String
Dim data1 As Variant, data2 As Variant, data3 As String
Dim WebsiteLink As String
Dim o.TEMP, p.TEMP, t.TEMP
Dim StatusBarCount As Integer
Dim M.Value2019 As String, B.Use As String, Fram As String
Dim x As Integer
Dim counter As Integer
Dim Year As String
StatusBarCount = 0
o.TEMP = ""
p.TEMP = ""
t.TEMP = ""
Worksheets("Results").Activate
Range("A3").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row

On Error Resume Next

Set objIE = New InternetExplorer
objIE.Visible = False
Do While objIE.readyState = READYSTATE_COMPLETE: DoEvents: Loop

Worksheets("Results").Activate
x = 3
counter = 2
For x = 3 To LastRow Step 1 'LastRow

Number = Range("B" & x).Value
    objIE.navigate "website/" & Number 'M.data
Do While objIE.readyState = READYSTATE_COMPLETE: Loop
'Do While objIE.readyState = 4: DoEvents: Loop
'Do Until objIE.readyState = 4: DoEvents: Loop
While objIE.Busy
DoEvents
Wend


For Each itemEle In objIE.document.getElementsByClassName("data-table cssWidth100")
    M.Value2019 = itemEle.getElementsByTagName("td")(1).innerText

'            B.Use = objIE.document.getElementsByTagName("td") 
(99).innerText
'            Year = objIE.document.getElementsByTagName("td")(101).innerText
'            Fram = objIE.document.getElementsByTagName("td")(92).innerText
        x = counter
        Range("T" & x).Value = M.Value2019
'            Range("U" & x).Value = Year
'            Range("V" & x).Value = B.Use
'            Range("W" & x).Value = Fram
       Exit For
Next
For Each itemEle In 
objIE.document.getElementsByClassName("cssDetails_TopContainer 
cssTableContainer cssOverFlow_x")
    B.Use = itemEle.getElementsByClassName("cssDetails_Top_Cell_Data")(6).innerText
'        Year = itemEle.getElementsByClassName("cssRight")(7).innerText
'        Fram = itemEle.getElementsByClassName("cssRight")(2).innerText
'            Range("U" & x).Value = Year
        x = counter
        Range("V" & x).Value = B.Use
'            Range("W" & x).Value = Fram
Exit For
Next


'Update the status bar at the bottom of the excel sheet
    M.Value2019 = ""
    B.Use = ""
    StatusBarCount = StatusBarCount + 1
    Application.StatusBar = "Pulling data - Please wait... " & 
StatusBarCount
    Range("X" & x).Value = Number
counter = counter + 1
Next x


objIE.Quit
Application.StatusBar = "Finished!"
MsgBox "Finished!"

End Sub

I expect to receive information pertaining to the specific number entered into the website but the information is usually duplicated when it is returned to the excel. it will have some individual but typically we see something like.

'54,678
'54,678
'48,900
'102,905
'102,905
'102,905

and the next time something similar but different numbers.

Thank you in advance.

alowflyingpig
  • 730
  • 7
  • 18
David Radford
  • 21
  • 2
  • 1
    You should [Avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Mikku Aug 06 '19 at 02:45
  • In your `For` loops you have `Exit for` thus it won't loop.... it will only look at first object and then exit the loop.. – alowflyingpig Aug 06 '19 at 03:24
  • Please provide test input values (ideally those to go with expected output) – QHarr Aug 06 '19 at 06:40
  • Does this really work? Don't variables with "." e.g. Dim o.TEMP, p.TEMP, t.TEMP give syntax errors? – QHarr Aug 06 '19 at 07:27

0 Answers0