+ Reply to Thread
Results 1 to 10 of 10

Web queries for multiple pages?

  1. #1
    Registered User
    Join Date
    02-09-2007
    Posts
    39

    Web queries for multiple pages?

    Hello, and thanks in advance for helping.

    My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )

    I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.

    Thanks!
    -Richard
    Last edited by pink; 12-11-2008 at 10:32 PM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    I believe they have to be created individually, though we might be able to automate the process if there's some consistent format to the urls (e.g. increment by one for each page).

    63 pages is a lot of info - are you sure your computer can cope?

    HTH

  3. #3
    Registered User
    Join Date
    02-09-2007
    Posts
    39
    Thanks for the reply!

    There's only about 15 lines of data for each page (with 4 columns), so 72 pages would not be bad at all (I hope).

    The url's do have a pattern to it. Its listed as */current?page=2, */current?page=3, etc

    I would post the url, but it requires login to a private account, so I can't do that.

    How would I go about setting this up "automatically"?

    Thanks again

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    Well, there are more sophisticated ways of doing this, but I'm trying to make it easy to follow:

    I recorded a macro (saving it in "personal macro workbook") - not knowing what website you're querying I decided to use the Yahoo! Finance FTSE100 data - if you specify date ranges these are used in the URL - so it's a good example for this. All I did, whilst recording, was set up a new web query for 1 Jan 00 - 1 Feb 00 (my data will overlap slightly but this is an example so pedantry is not welcome). I then opened my VBA editor (Alt+F11) and had a look, this is what I got:
    Please Login or Register  to view this content.
    It doesn't take a whole lot of detective work to understand that:
    b=1&a=00&c=2000&e=1&d=01&f=2000
    means 1 Jan 00 - 1 Feb 00 (NB January is month 0, February, month 1)

    It's not neccessary to understand the mechanics/syntax of the add method for query tables (although most aspects are self-explanatory anyway). The most important thing is the web table 22 ( in this example) - if your webpages aren't identically arranged this simply will not work(!) This refers to where you click the arrow button pertaining to the table whose data you want (if you've done a couple of web queries this should make sense). So I edited the code like this:

    Please Login or Register  to view this content.
    So, for the number of queries I want, I add a new page (simple to see?), then using ampersands, I insert changing numbers into the URL of each web query (shown in bold), I used the format command just to make the values two digits long - this is probably not needed in your example,
    Please Login or Register  to view this content.
    should be enough.

    It worked on my computer, try it on yours.

    If this makes sense you should be able to create your own version with your URL in no time at all.

    HTH

  5. #5
    Registered User
    Join Date
    02-09-2007
    Posts
    39
    Cheeky Charlie, you are amazing.

    I haven't gotten the chance to try it out yet, but I will hopefully soon. I've never programmed a macro before, but hope that my very limited knowledge in HTML and C will guide me thorugh.

    Thank you for your work, I'm SURE it'll lead me to success. If not, expect to see another post here.

    Thanks.
    Richard

  6. #6
    Registered User
    Join Date
    02-09-2007
    Posts
    39
    It works! However, I'm having trouble with a tweak. I'm trying to fit all the data in one sheet. Having i run from 0 to 9 and having 15 rows of data in one page, I've removed the sheets.add command and inserted the following code under destination:

    Please Login or Register  to view this content.
    But Excel gives me a compile error saying sub or function is not defined when I use the product command.

    I did some searching online, and it seems like its tied to "excel link" or something from vba. I don't know where to turn this feature on/off w/ excel 2003.

    Thanks.


    Edit: I got it working! I used Destination:=Range("A" & 1 + (i * 15))) instead, and changed .RefreshStyle = xlOverwriteCells

    Now for a few more tweaks, and a enter-password macro, I should be all set!
    Last edited by pink; 12-11-2008 at 05:11 PM.

  7. #7
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    Now for a few more tweaks, and a enter-password macro, I should be all set!
    famous last words... but I'm glad you're happy, I put some thought into this thread.
    Depending on how 'related' any other problems you might come across are, consider starting new threads and marking this as solved.

    CC

  8. #8
    Registered User
    Join Date
    02-09-2007
    Posts
    39
    I will do that now! Thank you once again!

  9. #9
    Registered User
    Join Date
    07-31-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Web queries for multiple pages?

    Thanks to all these discussion, I successfully made my own macro which scraped infomation from

    URLs http://www.sda.gov.cn/WS01/CL0027/index_1.html...index_2.html...index_3.html etc.

    but I'm facing a little problem, actually the items in these URLs are added in a daily basis and the most recent info will be stored in
    http://www.sda.gov.cn/WS01/CL0027/index.html while the outdated one will be moved to the subsequent pages i.e. index_1, index_2....

    Thus, my question are
    1. How can i included the first page http://www.sda.gov.cn/WS01/CL0027/index.html in my macro loop?
    2. Is it able to add a checking against the total number of pages so that the macro wont miss any newly added page?

    I m just new to the macro stuff, any help would be appreciated!
    Thanks in advance!

    Sam.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Web queries for multiple pages?

    Sam,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1