0

What I am trying to do is grab data form mutable URLs, at the same time using cells A1 to A10 as the last string of text on the URLs QueryTables.

  • Example
    Cell A1= B0006SH4PA

  • URL or QueryTables would changed in reference to cells text

    myURlocation=B0006SH4PA ' then data scraped from this URL then loop to next cell down

  • This would continue to cell A10 with each cell having different test making a different QueryTable for every cell.

This is code I have at the moment

Sub URL_Static_Query()
Dim i As Integer
   
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;myURlocation=" & Range("a1"), _
         Destination:=Range("a1"))
   
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub
Community
  • 1
  • 1
user8035099
  • 11
  • 3
  • So what's the outcome of your code? And why do you set the `Destination` to overwrite your data in the `A` column? – A.S.H Jul 01 '17 at 15:13
  • it only pulls data from one URL, I need it to pull it from mutable URLs looping through each cell a1 to a10 as the last string of text – user8035099 Jul 01 '17 at 15:21
  • You are right is will override with my reference. Sorry QueryTables is very new to me. – user8035099 Jul 01 '17 at 15:24

1 Answers1

0

Try this, it will write the data starting from column B below each other.

  Dim i As Long
  For i = 1 To 10
     With Sheet1.QueryTables.Add(Connection:="URL;myURlocation=" & Range("a" & i), _
        Destination:=sheet1.Range("b999999").End(xlUp).Offset(1))

        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
     End With
  Next i
A.S.H
  • 29,101
  • 5
  • 23
  • 50