VBA and web data

YounesB3 · #1 12-31-2013, 11:01 AM

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

L4 cell is the completion of the website with proper date range : ="&date_range_from="&L2&"&date_range_to="&L3&"&sco re_range_from=60&search_type=advanced"

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!

BobBridges · #2 01-03-2014, 09:59 AM

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

YounesB3 · #3 01-03-2014, 10:46 AM

Hehe, sorry about that. Error code 1004 : "Application-defined or Object-defined error"

BobBridges · #4 01-04-2014, 12:12 PM

Hm. What are the contents of L4? Since they form a part of the URL, maybe there's something invalid there?

YounesB3 · #5 01-04-2014, 07:21 PM

Quote:

Originally Posted by BobBridges

Hm. What are the contents of L4? Since they form a part of the URL, maybe there's something invalid there?

I think it's right, but here's the whole chainlink of formulas.

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

BobBridges · #6 01-11-2014, 08:03 AM

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

...and it failed the same way yours did. But when I looked up more about that Connection thingy, I changed the program in just one minor way and it didn't object any more:

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

Mind you, the rest of your program may fail for other reasons. But at least this'll get you to the next step.

Sorry for the delay; I should have gotten to it sooner.

YounesB3 · #7 01-15-2014, 12:53 PM

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

YounesB3 · #8 02-01-2014, 02:18 PM

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