VBA and web data

BobBridges · #1 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.

YounesB3 · #2 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

BobBridges · #3 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.

YounesB3 · #4 02-03-2014, 09:33 PM

Quote:

Originally Posted by BobBridges

QUOTE

Again, thank you so much! I was able to end the macro myself

:

Code:

Sub Temp()

Sheets("Temp").Select

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

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

    Range("K7").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 i As Integer
i = Mid(ActiveCell.Value, 2) - 1

For Y = 1 To i

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)
        .RowNumbers = False
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .SaveData = False
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With

Next Y

Range("$A$6:$L$6").Select
Selection.AutoFilter
Selection.AutoFilter
LastRow = Range("K" & Rows.Count).End(xlUp).Row
Range("L7").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(OFFSET(RC[-1],-1,0),5)=""game "",1,IF(LEFT(RC[-1],5)=""game "",1,IF(LEFT(RC[-1],13)=""Release Date:"",1,IF(ISNUMBER(RC[-1]),IF(RC[-1]>60,1,0),0))))"
Selection.AutoFill Destination:=Range("L7:L" & LastRow)
Dim myRange As Range
Dim myCell As Range
Dim A As Range
Set myRange = Range("K7:K" & LastRow)

ActiveSheet.Range("$A$6:$L$100000").AutoFilter Field:=12, Criteria1:="0"
Range("K7:L" & LastRow).Select
Selection.ClearContents
ActiveSheet.Range("$A$6:$L$100000").AutoFilter Field:=12
Range("K7:L" & LastRow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

Do
    Range("K6").Select
    Cells.Find(What:="Game*", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Set myCell = ActiveCell
    Set A = ActiveCell.Offset(3, 0)
    Range(myCell, A).Select
    Selection.Copy
    Range("A6").Select
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Range(myCell, A).ClearContents

Loop Until Range("B1").Value = 1

Columns("K:L").Select

For X = 1 To i
    Selection.QueryTable.Delete
Next X

Selection.ClearContents
Range("K6").Select
ActiveCell.FormulaR1C1 = "Metacritic field"
Range("L6").Select
ActiveCell.FormulaR1C1 = "Formula"

End Sub

Some other questionmark on my head :

- The ".SaveData = False" statement seems to be saving the connections with the website even if it's set to false. I ran the macro twice just to make sure it wasn't a bother and it wasn't. I made the last part of the code delete all webqueries associated in the workbook.
- I'm not sure why your "vd" statement and my lastrow didn't work :P
- I might have made too much lines in my code hahaha!

But these are non fondamental...! The automation is finally a success!!

BobBridges · #5 02-06-2014, 09:37 PM

My understanding of the SaveData setting, from reading the documentation, is that it determines whether to retain the downloaded data when you save the workbook. The QueryTable itself is always saved, and I would expect that to include the connection.

"Didn't work" is no help; I can't guess at a cause without symptoms. If you want me to try to explain what's wrong with my vd and your lastrow, you gotta say what they did instead working. Did the program keep going infinitely? Did the report come out right but colored yellow? Did your PC display "VD doesn't work!!" and then explode? What?

YounesB3 · #6 02-06-2014, 10:38 PM

Quote:

Originally Posted by BobBridges

My understanding of the SaveData setting, from reading the documentation, is that it determines whether to retain the downloaded data when you save the workbook. The QueryTable itself is always saved, and I would expect that to include the connection.

"Didn't work" is no help; I can't guess at a cause without symptoms. If you want me to try to explain what's wrong with my vd and your lastrow, you gotta say what they did instead working. Did the program keep going infinitely? Did the report come out right but colored yellow? Did your PC display "VD doesn't work!!" and then explode? What?

Haha no, your vd statement does work, it's my lastrow which doesn't! I'm not sure why :P

Your vd statement copies the data at the correct location (cell #) while the lastrow always overwrites at the same cell even though I (tried to) state otherwise..

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