Microsoft Office Forums - View Single Post

YounesB3 · 01-26-2014, 10:42 AM

Quote:

Originally Posted by BobBridges

Doh! <head slap> I knew about the %xx syntax in a URL; I completely failed to think how that would solve your problem. My apologies, Younes.

Ok, next, how to have your program figure out how many "pages" to go after. But how do you know? In fact, what's an example of multiple pages? I'm not sure what we're talking about: Do you do a different search every month and the URL generates the pages, or do you go to a master page and it says that this month there are 14 pages in the listing, or what?

Your second question is clear enough, but I'm inclined to say it'll be simpler, once your program knows there are to be n pages of output, to create n new worksheets, put one query on each, and then have your program pull the data from each in turn and assemble them on master worksheet. Not that it can't be done the other way, but IMO it'll be easier to keep track of the data movements this way. But I want to wait to see what this multiple-page thingy looks like, first.

Nah it's not simpler, because I have another macro based on the column L.

But I found a way to define my maximum page (see variable X). The macro has evolved quite a lot, but I have a new problem (probably the last one).

Here's the code :

Code:

Sheets("Temp").Select

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

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L5"))
        .Name = Range("K5").Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

    Range("L5").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
        
Dim X As Integer
Dim Y As Integer

X = Right(ActiveCell.Value, 2)
Y = 1

Do Until X = Y

LastRow = Range("L" & Rows.Count).End(xlUp).Row
URL = "URL;" & Range("K4").Value & "&page=" & Y

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L2:L" & LastRow))
        .Name = Range("K5").Value & "&page=" & Y
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Y = Y + 1

Loop

End Sub

The problem is that, for each loop, it seems to add a new column or something... But I want everything in column L... I'm assuming the part in orange is what causes the problem, but I'm not sure what...