VBA and web data

BobBridges · #1 01-24-2014, 02:54 PM

1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.

Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?

2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.

YounesB3 · #2 01-24-2014, 04:11 PM

Quote:

Originally Posted by BobBridges

1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.

Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?

2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.

By replying to your post, I just found out that the brackets can be replaced!

On the website, [= %5B & ]= %5D

I now have the first part of the code which works:

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

End Sub

Here are the next problems...

How do I automate it to have the correct amount of pages each time? For instance, last 3 months have 19 pages. That means 19 web querys and 19 different webpages? What if next time it's 23 pages?

And how do I make the data to follow suit? For instance, the first page of data copied from the website end on L259 with the first query. The second page should start on L260, but this will change each 3 months. How do I make the query to look for last row of page 1 of data and then choose its destination to follow suit? I'm not sure if I'm clear? lol :P

BobBridges · #3 01-25-2014, 04:55 PM

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.

YounesB3 · #4 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...

BobBridges · #5 01-29-2014, 03:21 PM

I've a notion the problem has to do with the fact that you're using the ADD method each time in your loop. That means Excel is adding a new query to the same worksheet each time; I imagine it wouldn't like having two queries point to the same destination cell, so maybe it responds by adding a column. But I shouldn't guess; instead, how about if you post a copy of your workbook so I can look at it in detail?

YounesB3 · #6 01-29-2014, 07:53 PM

Quote:

Originally Posted by BobBridges

I've a notion the problem has to do with the fact that you're using the ADD method each time in your loop. That means Excel is adding a new query to the same worksheet each time; I imagine it wouldn't like having two queries point to the same destination cell, so maybe it responds by adding a column. But I shouldn't guess; instead, how about if you post a copy of your workbook so I can look at it in detail?

I just pmed the file to you! Thanks again for your help!

BobBridges · #7 01-29-2014, 11:32 PM

Yep, each time you do a QueryTables.Add giving Dest:=Range("K1"), it bumps the columns that already existed to the right to make room for a new set of data. It turns out its doing that even the first time (I put some data in J2:M2 to be sure).

If you look at this page, you'll see a list of the properties of a QueryTable object. Let's take a look at each of the ones mentioned in your With group:

.Name = Range("A4").Value — This is just a character string, and since you don't use it elsewhere you can probably omit this statement.

.FieldNames = True — This is the default setting; no need to specify it.

.RowNumbers = False — Probably best to leave this in.

.FillAdjacentFormulas = False — "True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed." We could test this, but since you don't have any formulae to the right I would guess it's not important.

.PreserveFormatting = True — Default value; omit.

.RefreshOnFileOpen = False — The default; omit.

.BackgroundQuery = True — You're overriding this value when you execute the Refresh, so omit it.

.RefreshStyle = xlInsertDeleteCells — Ah, maybe this is why columns are inserted. I changed this to xlOverwriteCells instead, and it writes over the old range, which I take it you would prefer.

.SavePassword = False — This is about ODBC connections, not Web queries; remove.

.SaveData = True — Depends on whether you want to recreate these QueryTables every time you run the macro. You haven't gotten that far in your thinking, yet, I expect; later on you may want to set this to False.

.AdjustColumnWidth = True — Default value (even though it doesn't seem to be doing it); remove.

.RefreshPeriod = 0 — Probably the default value.

.WebSelectionType = xlEntirePage — If I remember right, with this web site you need to get the entire page anyway, so leave it.

.WebFormatting = xlWebFormattingNone — Seems appropriate.

.WebPreFormattedTextToColumns = True — The default value.

.WebConsecutiveDelimitersAsOne = True — The default value.

.WebSingleBlockTextImport = False — The default value.

.WebDisableDateRecognition = False — The default value.

.WebDisableRedirections = False — The default value.

That makes the Add a bit less unwieldy:

Code:

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

Now let's look at some minor things elsewhere in your program:

1) You've managed your loop like this:

Code:

Dim X As Integer
Dim Y As Integer
X = Right(ActiveCell.Value, 2)
Y = 1
Do Until X = Y
  'blah, blah, blah
  Y = Y + 1
  Loop

Now, you said you're new at VBA, so let me show you a built-in feature of the language that helps you do loops more easily:

Code:

For Y = 1 To Right(ActiveCell.Value, 2)
  'blah, blah, blah
  Loop

This does everything you were doing; it sets Y to 1, then runs through the code, bumps Y up by 1 and repeats until it's reached the top value.

2) You said to look at Right(ActiveCell.Value,2), and that's great when you have a 2-digit number of pages in a query. But if you only have 9, it'll fail. Do it this way instead:

Code:

For Y = Mid(ActiveCell.Value, 2)

This takes all of the value in that cell starting at the 2nd character, no matter how long it is.

3) You would eventually have noticed this without my pointing it out: It turns out that &page=1 of the query is actually the second page, and &page=19 is the 20th; so your program has been doing the query once too many times. So in my code I'm running only up to Mid(ActiveCell.Value, 2) - 1.

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.

5) I got to thinking about all those Adds. Why create 20 different web queries that have to be stored in the worksheet? I experimented with just using the one over and over; I create the one at the beginning, and call that QueryTable object "qto". Then in the loop I change qto.Connection and refresh the table, and it seems to work:

Code:

  Set qto = ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("K1"))
  With qto
    .RowNumbers = False
    .RefreshStyle = xlOverwriteCells
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .Refresh BackgroundQuery:=False
    End With
  .
  .
  .
  For Y = 1 To Mid(ActiveCell.Value, 2) - 1
    qto.Connection = "URL;" & Range("A3").Value & "&page=" & Y
    qto.Refresh BackgroundQuery:=False
    Next Y

The final product looks like this:

Code:

Sub Test()
  Sheets("Temp").Select
  URL = "URL;" & Range("A3").Value
  Set qto = ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("K1"))
  With qto
    .RowNumbers = False
    .RefreshStyle = xlOverwriteCells
    .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
    qto.Connection = "URL;" & Range("A3").Value & "&page=" & Y
    qto.Refresh BackgroundQuery:=False
    Next Y
  End Sub

Now, you understand that you still haven't figured out what to do with the data this reads in. I mean, I figured out how to overwrite the data from each web fetch with the next one; but you have to insert code that grabs that data, before it's replaced, and stick in somewhere into your worksheets. Otherwise you do the Refresh and you lose all the data you just got from your previous fetch. But I suppose you knew that already; it's the next stage of the work, right?

I should proofread all this before I submit it, but I think I'll go to bed instead and hope I haven't made any embarrassing errors.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Match two sets of data and display specific data	lolly150	Excel	1	05-14-2012 10:33 PM
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
Powerpoint: adding data to trend lines w/o data labels	HaiLe	PowerPoint	0	04-11-2011 09:21 AM