|
#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
|
|||
|
|||
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. |
|
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 |