Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-31-2013, 11:01 AM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default 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
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!
Reply With Quote
  #2  
Old 01-03-2014, 09:59 AM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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...?
Reply With Quote
  #3  
Old 01-03-2014, 10:46 AM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Hehe, sorry about that. Error code 1004 : "Application-defined or Object-defined error"
Reply With Quote
  #4  
Old 01-04-2014, 12:12 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Hm. What are the contents of L4? Since they form a part of the URL, maybe there's something invalid there?
Reply With Quote
  #5  
Old 01-04-2014, 07:21 PM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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
Reply With Quote
  #6  
Old 01-11-2014, 08:03 AM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #7  
Old 01-15-2014, 12:53 PM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 01-15-2014, 01:05 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
This won't make it work any better; it'll just tell you what URL it's being asked to look up, in case there's an error in it somewhere. If the URL still looks right to you, try cutting and pasting it into a browser and see whether it goes anywhere; maybe that'll reveal that the URL isn't valid. That's my suspicion, anyway.
Reply With Quote
  #9  
Old 01-15-2014, 01:11 PM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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
This won't make it work any better; it'll just tell you what URL it's being asked to look up, in case there's an error in it somewhere. If the URL still looks right to you, try cutting and pasting it into a browser and see whether it goes anywhere; maybe that'll reveal that the URL isn't valid. That's my suspicion, anyway.
Nope it works :

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.
Reply With Quote
  #10  
Old 01-15-2014, 01:59 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #11  
Old 01-17-2014, 11:56 AM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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.
Thanks for that indication..!

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?
Reply With Quote
  #12  
Old 01-17-2014, 02:55 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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").
Reply With Quote
  #13  
Old 01-18-2014, 02:03 PM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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").
Well basically now in VBA the code is :

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"))
And in cell L4 : ="[game]=1&date_range_from="&K2&"&date_range_to="&K3&"&sco re_range_from=60&search_type=advanced"

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?
Reply With Quote
  #14  
Old 01-18-2014, 05:17 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA and web data Windows 7 64bit VBA and web data Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #15  
Old 01-19-2014, 09:29 AM
YounesB3 YounesB3 is offline VBA and web data Windows XP VBA and web data Office 2010 32bit
Advanced Beginner
VBA and web data
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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.
Thanks so much!
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft