VBA and web data

BobBridges · #1 01-23-2014, 11:54 AM

Ah, that explains something: When I ran the program I, too, kept getting an invalid Web query. "But" (I thought) "it works for her, so I must be missing something."

However, here's what I found that you can do: You can set up the query manually. After that, your VBA program can do the refresh and reformat the data to suit you. At least, my program could refresh it automatically. Try it:

1) Create the query manually. Now it's stored permanently in the worksheet.
2) Set up a program with just this one statement in it:

Code:

ActiveSheet.QueryTables(1).Refresh

That's assuming that you have just the one web query in the active worksheet, or at least that we're talking about the first one.
3) Go back to your worksheet and delete or change a few values that the query downloaded when you first set it up.
4) Run the one-statement program I described in step 2.

You should see that the program did the Refresh, which caused the data you changed or deleted to restored; that proves that the program works, even though it doesn't seem to know what to do with those square brackets when you try to get it to set up the query in the first place.

After that, you just have to add to your program whatever statements you need to toss out the data you don't want and arrange the rest into a usable format. If you don't know how to do that, don't despair; you're making progress. Just prove to yourself that the above works, so far, and then come back and we'll talk more.

YounesB3 · #2 01-24-2014, 02:08 PM

Quote:

Originally Posted by BobBridges

Ah, that explains something: When I ran the program I, too, kept getting an invalid Web query. "But" (I thought) "it works for her, so I must be missing something."

However, here's what I found that you can do: You can set up the query manually. After that, your VBA program can do the refresh and reformat the data to suit you. At least, my program could refresh it automatically. Try it:

1) Create the query manually. Now it's stored permanently in the worksheet.

2) Set up a program with just this one statement in it:

Code:

ActiveSheet.QueryTables(1).Refresh

That's assuming that you have just the one web query in the active worksheet, or at least that we're talking about the first one.
3) Go back to your worksheet and delete or change a few values that the query downloaded when you first set it up.
4) Run the one-statement program I described in step 2.

You should see that the program did the Refresh, which caused the data you changed or deleted to restored; that proves that the program works, even though it doesn't seem to know what to do with those square brackets when you try to get it to set up the query in the first place.

After that, you just have to add to your program whatever statements you need to toss out the data you don't want and arrange the rest into a usable format. If you don't know how to do that, don't despair; you're making progress. Just prove to yourself that the above works, so far, and then come back and we'll talk more.

Thanks again for your help! However, I don't think the above can really work with what I want to do mostly because the website changes each 3 months. That's why there is a formula in K4 (L4 previously).

Also, I need the data from all the different pages.

For example, for the next 3 months, it's going to be the 19 pages : 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

The 3 months after that, it's going to be X pages (today 3 pages) : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=12-31-2013&date_range_to=03-31-2014&score_range_from=60&search_type=advanced

BobBridges · #3 01-24-2014, 02:54 PM

1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.

Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?

2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.

YounesB3 · #4 01-24-2014, 04:11 PM

Quote:

Originally Posted by BobBridges

1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.

Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?

2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.

By replying to your post, I just found out that the brackets can be replaced!

On the website, [= %5B & ]= %5D

I now have the first part of the code which works:

Code:

Sheets("Temp").Select

URL = "URL;" & Range("K4").Value

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L5"))
        .Name = Range("K5").Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Here are the next problems...

How do I automate it to have the correct amount of pages each time? For instance, last 3 months have 19 pages. That means 19 web querys and 19 different webpages? What if next time it's 23 pages?

And how do I make the data to follow suit? For instance, the first page of data copied from the website end on L259 with the first query. The second page should start on L260, but this will change each 3 months. How do I make the query to look for last row of page 1 of data and then choose its destination to follow suit? I'm not sure if I'm clear? lol :P

BobBridges · #5 01-25-2014, 04:55 PM

Doh! <head slap> I knew about the %xx syntax in a URL; I completely failed to think how that would solve your problem. My apologies, Younes.

Ok, next, how to have your program figure out how many "pages" to go after. But how do you know? In fact, what's an example of multiple pages? I'm not sure what we're talking about: Do you do a different search every month and the URL generates the pages, or do you go to a master page and it says that this month there are 14 pages in the listing, or what?

Your second question is clear enough, but I'm inclined to say it'll be simpler, once your program knows there are to be n pages of output, to create n new worksheets, put one query on each, and then have your program pull the data from each in turn and assemble them on master worksheet. Not that it can't be done the other way, but IMO it'll be easier to keep track of the data movements this way. But I want to wait to see what this multiple-page thingy looks like, first.

YounesB3 · #6 01-26-2014, 10:42 AM

Quote:

Originally Posted by BobBridges

Doh! <head slap> I knew about the %xx syntax in a URL; I completely failed to think how that would solve your problem. My apologies, Younes.

Ok, next, how to have your program figure out how many "pages" to go after. But how do you know? In fact, what's an example of multiple pages? I'm not sure what we're talking about: Do you do a different search every month and the URL generates the pages, or do you go to a master page and it says that this month there are 14 pages in the listing, or what?

Your second question is clear enough, but I'm inclined to say it'll be simpler, once your program knows there are to be n pages of output, to create n new worksheets, put one query on each, and then have your program pull the data from each in turn and assemble them on master worksheet. Not that it can't be done the other way, but IMO it'll be easier to keep track of the data movements this way. But I want to wait to see what this multiple-page thingy looks like, first.

Nah it's not simpler, because I have another macro based on the column L.

But I found a way to define my maximum page (see variable X). The macro has evolved quite a lot, but I have a new problem (probably the last one).

Here's the code :

Code:

Sheets("Temp").Select

URL = "URL;" & Range("K4").Value

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L5"))
        .Name = Range("K5").Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

    Range("L5").Select
    Cells.Find(What:="page:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.Find(What:="…", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
Dim X As Integer
Dim Y As Integer

X = Right(ActiveCell.Value, 2)
Y = 1

Do Until X = Y

LastRow = Range("L" & Rows.Count).End(xlUp).Row
URL = "URL;" & Range("K4").Value & "&page=" & Y

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("L2:L" & LastRow))
        .Name = Range("K5").Value & "&page=" & Y
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Y = Y + 1

Loop

End Sub

The problem is that, for each loop, it seems to add a new column or something... But I want everything in column L... I'm assuming the part in orange is what causes the problem, but I'm not sure what...

BobBridges · #7 01-29-2014, 03:21 PM

I've a notion the problem has to do with the fact that you're using the ADD method each time in your loop. That means Excel is adding a new query to the same worksheet each time; I imagine it wouldn't like having two queries point to the same destination cell, so maybe it responds by adding a column. But I shouldn't guess; instead, how about if you post a copy of your workbook so I can look at it in detail?

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