Microsoft Office Forums - View Single Post

BobBridges · 02-01-2014, 06:11 PM

Ah, I see. I was picturing logic that works roughly like this:

Code:

For <page counter> = 1 To <last page>
  Run the query for page <page counter>, writing over previous page
  Read through the downloaded data and rearrange it into a receiving worksheet
  Next <page counter> 'that is, go back and do the same for the next page

But you have in mind doing it this way:

Code:

For <page counter> = 1 To <last page>
  Run the query for <page counter>, copying below the previous page
  Next <page counter>
Arrange the assembled data, ALL pages, into a receiving worksheet

Well, that should work too. You may have an additional complication detecting the end of each page and the beginning of the next, but it probably won't be difficult, much less insurmountable. So, ok, we'll go that way:

Quote:

Isn't there an option to leave the first webdata copied alone and copy new data below the previous data?

As usual, there are a couple of ways. You were already doing it the first time, sort of: Using RefreshStyle=xlInsertDeleteCells, every time you queried the next page Excel inserted a column, moving the previous page's data rightward to column L, and put the new page in column K. I changed the option to xlOverwriteCells—which caused it to write over the old data rather than preserving it—because I was thinking you would eventually write logic inside the loop to grab all the downloaded data before you went around for the next page.

If you want to grab all the pages, then move all their data around, that has to change. I thought it would be easy: Just use the same QueryTable, changing the Destination each time the loop iterates. It turns out, though (I played with it today) that the Destination property is read-only; it's set at QueryTable.Add time and cannot be changed. So if you're going to run all the pages before rearranging any of them, we'll have to preserve all the data by putting each page in a different Destination in the worksheet.

You were trying to do that when you found the last row and set a new range for the Destination; you just did it wrong. You were thinking that if you set the second range to K1:K<lastrow>, Excel would automatically put the second page after that. But no; the second query goes in K1, because after all that's what it thought you were telling it to do. Here's what you do instead: set the Destination to K<lastrow+1>. In my program I did it this way:

Code:

  For Y = 1 To 5 'Mid(ActiveCell.Value, 2) - 1
    URL = "URL;" & Range("A3").Value & "&page=" & Y
    Set vd = Range("K" & Rows.Count).End(xlUp).Offset(1, 0)
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=vd)
      .WebSelectionType = xlEntirePage
      .WebFormatting = xlWebFormattingNone
      .Refresh BackgroundQuery:=False
      End With
    Next Y

By the way, once you use the For...Next statement, you don't need to increment Y any more. You're doing this:

Code:

For Y = 1 To Mid(ActiveCell.Value, 2) - 1
    'blah, blah, blah
    Y = Y + 1 'drop this statement
Next Y

The loop itself is already adding 1 to Y each time it executes. Your additional statement ("Y = Y + 1" statement) is causing the program to increment Y twice in each loop; in effect it's fetching every other page, ie 1, 3, 5 and so on; you're missing half the results and getting only 10 pages. Drop the extra statement.