#1
|
|||
|
|||
VBA and web data
Hello everyone!
I want to make a macro which takes data from a website. I'm completly new in this, so I tried something based on an example I found on the net. Here is the beginning of it. It bugs on the first line (and everything in orange, I don't know what it means yet). Code:
Sub Test() With ActiveSheet.QueryTables.Add(Connection:= _ "http://www.metacritic.com/search/all/results?cats[game]=1" & Range("L4").Value, Destination:=Range("M5")) .Name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "5" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub What I want to do is basically extract the data from this website with changing date range : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=06-31-2013&date_range_to=09-31-2013&score_range_from=60&search_type=advanced Also, as you can see, there are 16 pages of values, but this might change depending on the date range. I need all data from the +-16 pages. Cross-post here : http://www.mrexcel.com/forum/excel-q...ml#post3673642 Thanks for any help! |
#2
|
||||
|
||||
Younes, you say it "bugs", and I assume you mean Excel gives you an error message. But if so, you didn't tell us what the error is. You must have forgotten; you couldn't possibly have thought it didn't matter...?
|
#3
|
|||
|
|||
Hehe, sorry about that. Error code 1004 : "Application-defined or Object-defined error"
|
#4
|
||||
|
||||
Hm. What are the contents of L4? Since they form a part of the URL, maybe there's something invalid there?
|
#5
|
|||
|
|||
Quote:
L4 : ="&date_range_from="&L2&"&date_range_to="&L3&"&sco re_range_from=60&search_type=advanced" L2 : =CONCATENATE(MID(PC!C1,19,2);"-";MID(PC!C1,16,2);"-";RIGHT(PC!C1,4)) L3 : =CONCATENATE(IF(LEN(((MID(PC!C1,19,2))+3))=1;0&((M ID(PC!C1,19,2))+3);((MID(PC!C1,19,2))+3));"-";MID(PC!C1,16,2);"-";RIGHT(PC!C1,4)) PC!C1 : Last Updated : 31/09/2013 |
#6
|
||||
|
||||
I think I found it, Younes. I've never tried automating a QueryTable before, so I had to go look it up, but the documentation says your string has to start with "URL;". I thought maybe there was something wrong with the URL itself, you'll remember, so I made a copy of your program and plugged in a different URL that I knew was valid:
Code:
Sub Test() URL = "http://www.onelook.com/?w=frick&ls=a" With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Cells(1, 1)) .Name = "" .FieldNames = True .RowNumbers = False ' blah, blah, blah End With End Sub Code:
Sub Test() URL = "URL;http://www.onelook.com/?w=frick&ls=a" With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Cells(1, 1)) .Name = "" .FieldNames = True .RowNumbers = False ' blah, blah, blah End With End Sub Sorry for the delay; I should have gotten to it sooner. |
#7
|
|||
|
|||
Thanks a lot!
New error : run-time error 1004 : invalid web query. This is where it bugs (highlighted below): Code:
Sub Test() Sheets("Temp").Select URL = "URL;http://www.metacritic.com/search/all/results?cats[game]=1" & Range("L4").Value With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("M5")) .Name = "test" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "5" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With |
#8
|
||||
|
||||
Try this:
Code:
Sub Test() Sheets("Temp").Select URL = "URL;http://www.metacritic.com/search/all/results?cats[game]=1" & Range("L4").Value MsgBox URL With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("M5")) 'blah, blah, blah End With End Sub |
#9
|
|||
|
|||
Quote:
http://www.metacritic.com/search/all/results?cats[game]=1 Range("L4").Value L4 value is : &date_range_from=09-31-2013&date_range_to=12-31-2013&score_range_from=60&search_type=advanced If you combine the 2, it works. |
#10
|
||||
|
||||
Yep, I get the same thing. I played around with the URL a while, in the program, and believe it or not, what appears to make it unhappy is the square brackets; my browser thinks they're valid, alright, but when I remove "[game]" from the URL then the VBA error goes away.
This may not be good news for you. Unless there's a way to persuade VBA that this is a valid URL, or a way to get the data from metacritic without the square brackets, you may have to rethink your solution. But the first thing I'd do is start asking around to see whether there's an Excel setting that'll tell it to be less stringent about valid URL characters. Maybe you'll be able to Google something about it. Failing that, maybe the folks at metacritic can offer you a workaround. I might have googled it myself, but I gotta go soon; I have a meeting in a few hours. |
#11
|
|||
|
|||
Quote:
I've simply put the "[game]=1" portion in my L4 formula and it seems to work for now. Now I need help for the next steps! :P Should I make another topic? |
#12
|
||||
|
||||
Really? You put the square brackets in another part of the URL and it worked? Now I'm not sure what to think.
Anyway, my suggestion is that you ask your next question here and I'll see what I think. If it's beyond my expertise, I'll suggest that you start a new thread, so that others will see it (instead of glancing at this one and mutter "oh, someone else already has this one"). |
#13
|
|||
|
|||
Quote:
Code:
Sheets("Temp").Select URL = "URL;http://www.metacritic.com/search/all/results?cats" & Range("L4").Value With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("M5")) So for now, the only thing we managed to do is to connect excel to the website. I need to collect the data from the website in question. So some type of information which is on this page : 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 More specifically : game console, score of game, name of game and published date. Do you know how? |
#14
|
||||
|
||||
I used to—just not using this method. As I said earlier (or maybe that was a different thread), I used to use a .dll that apparently is not available after Win XP. That method involved pulling HTML code from a web page and either parsing the raw HTML or navigating your way downward in a sort of tree structure: The document, then the body within the document, then the third division in the body, then the 14th table (or the element named "XYZ") within that division, then run through the records in that table one at a time and pull out the 3rd and 5th cells from each record...that kind of thing. Probably required more effort than this QueryTable thingy, but more flexible too; that's the usual tradeoff.
Well, I've been wanting to regain the ability to automate web calls, so this'll be my excuse. Let me play with it and see what I can figure out. I'll be back....eventually. |
#15
|
|||
|
|||
Quote:
|
|
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 |