#16
|
||||
|
||||
Well, when I run a query, it retrieves the identified table from the web page and fills the data in on the worksheet. That part is straightforward enough. After that you just have to arrange the data into something you can work with.
But you're asking how you get the data from the web site in the first place, which leads me to suspect your results are different. What's happening when you run the program? If it's not filling in the data from the web page, what is it doing? And by the way, what happens when you run that query manually, without VBA? Because after all you're trying to get VBA to to automatically what you can already do by hand, right? So how well does it work when you do it by hand? |
#17
|
|||
|
|||
Quote:
My mistake... sorry :/ I wrote to the support team of Metacritic, but I haven't received any replies unfortunatly... The error I get is "Invalid web query". Also, when I do it manually, I don't do it with a query, I copy paste each page individually and make some removal of data manually (it takes quite a while). |
#18
|
||||
|
||||
Ah, that explains something: When I ran the program I, too, kept getting an invalid Web query. "But" (I thought) "it works for her, so I must be missing something."
However, here's what I found that you can do: You can set up the query manually. After that, your VBA program can do the refresh and reformat the data to suit you. At least, my program could refresh it automatically. Try it: 1) Create the query manually. Now it's stored permanently in the worksheet. 2) Set up a program with just this one statement in it: Code:
ActiveSheet.QueryTables(1).Refresh 3) Go back to your worksheet and delete or change a few values that the query downloaded when you first set it up. 4) Run the one-statement program I described in step 2. You should see that the program did the Refresh, which caused the data you changed or deleted to restored; that proves that the program works, even though it doesn't seem to know what to do with those square brackets when you try to get it to set up the query in the first place. After that, you just have to add to your program whatever statements you need to toss out the data you don't want and arrange the rest into a usable format. If you don't know how to do that, don't despair; you're making progress. Just prove to yourself that the above works, so far, and then come back and we'll talk more. |
#19
|
|||
|
|||
Quote:
Also, I need the data from all the different pages. For example, for the next 3 months, it's going to be the 19 pages : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=09-31-2013&date_range_to=12-31-2013&score_range_from=60&search_type=advanced The 3 months after that, it's going to be X pages (today 3 pages) : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=12-31-2013&date_range_to=03-31-2014&score_range_from=60&search_type=advanced |
#20
|
||||
|
||||
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. |
#21
|
|||
|
|||
Quote:
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 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 |
#22
|
||||
|
||||
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. |
#23
|
|||
|
|||
Quote:
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 |
#24
|
||||
|
||||
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?
|
#25
|
|||
|
|||
Quote:
|
#26
|
||||
|
||||
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 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 Code:
For Y = 1 To Right(ActiveCell.Value, 2) 'blah, blah, blah Loop 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) 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 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 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. |
#27
|
|||
|
|||
Quote:
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 |
#28
|
|||
|
|||
I thought about it and I could do a work around if this is not possible. Just let the data be added in column L, M, N, etc. and then make another macro that copies that data in column K with last rows.
Then another macro would take data from column K and sort it out. |
#29
|
||||
|
||||
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 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 Quote:
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 Code:
For Y = 1 To Mid(ActiveCell.Value, 2) - 1 'blah, blah, blah Y = Y + 1 'drop this statement Next Y |
#30
|
|||
|
|||
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!! |
|
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 |