Microsoft Office Forums - View Single Post

YounesB3 · 01-31-2014, 11:51 AM

Quote:

Originally Posted by BobBridges

4) You're changing the query target range each time you run the loop. I can't see that it hurts anything, but I can't see that it help either; so in my code I eliminated the check for the last row.

First of all, WOAH!!! That is amazing information!!! Thanks!!

I pretty much followed everything in your post except for the lastrow thingy...

The reason I wanted to change the range of the webquery each time to the lastrow is for it to do the following :

First page : copy web data to K1
Define lastrow (e.g. : 260)
Second page : leave the web data previously copied and copy second page to line 260 (lastrow).
Define new lastrow.
Yadi yadi yada.

I tried the 3 options (xlInsertDeleteCells, xlOverwriteCells & xlInsertEntireRows) but the 3 don't work. Isn't there an option to leave the first webdata copied alone and copy new data below the previous data?

Here's the adjusted code :

Code:

Sub Test()

Sheets("Temp").Select

URL = "URL;" & Range("A3").Value

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("K1"))
        .RowNumbers = False
        .RefreshStyle = xlOverwriteCells
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With

    Range("K1").Select
    Cells.Find(What:="page:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.Find(What:="…", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
For Y = 1 To Mid(ActiveCell.Value, 2) - 1

LastRow = Range("K" & Rows.Count).End(xlUp).Offset(0, 1).Row
URL = "URL;" & Range("A3").Value & "&page=" & Y

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("K1:K" & LastRow))
        .RowNumbers = False
        .RefreshStyle = xlOverwriteCells
        .SaveData = False
        .AdjustColumnWidth = False
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With

Y = Y + 1

Next Y

End Sub