Macros + Web Query = Huge Headache. Can anyone help me?

[mikey111]

I have a very low comfort-level with macros (I've incorporated them before, but never written my own). But I'm trying to automatically generate info into my spreadsheet, and it seems to require macros and web queries...


Here is a hypothetical project that's very similar to what I'm trying to do. Say I have a long list of urls that look similar to this:


https://live.xbox.com/en-US/Profile?gamertag=major%20nelson

https://live.xbox.com/en-US/Profile?gamertag=Wombatman

https://live.xbox.com/en-US/Profile?gamertag=Michael1

Etc...


(Note that the real project has nothing to do w/Xbox -- this is just an example).


I want to make a macro that will do a web query utilizing each url. The query will grab a piece of info (in this instance, say, the player's location info) and stick it in a neighboring cell. The list of urls is many thousands, so it would be a pain to do it manually. Just as in the example of above, the url name is the same in each case, with the exception of the characters after the "equal" sign.


Can anyone help me out?

 

[NARAYANK991]

Hi ,


I am confused by your requirement specification ; you say you have a long list of URLs , all following an identical format , where a person's name follows a keyword like "gamertag". You wish to isolate the person's name from the URL , and enter this name in an assoicated cell.


What is to be done with a web query ?


Alternatively , if a web query is to be made using the URL , where in the website is the information you have mentioned viz. player's location ?


Can you please clarify ?


Narayan

 

[mikey111]

Hi Narayan,


In the example I provided, the information I would like to obtain from the webquery is the "location" info. The info might be "Chicago, IL", "Japan", etc. So the web query would access the url, copy the "location" info, and paste it into a cell in my spreadsheet.


(This is just a hypothetical, but it's very similar to what I'm trying to achieve with a different site).


Does that make sense?

 

[NARAYANK991]

Hi ,


Can you indicate where in the website , the information will be available ?


Narayan

 

[SirJB7]

Hi, mikey111!


I'd recommend you reading the three green sticky topics at this forums main page.

Among general guidelines, you'll find there that posting a sample data file is of great help for others trying to understand what are you trying to achieve.

The sample file doesn't need to contain the real data but yes the real structure with dummy data sampled across all possible values, so as to don't leave any case without testing.

This goes for files.

But in your case one of the files is replaced by a website. Well, the pre-requisites are the same: "uploading a sample file" would become "link to another site with analogue (or as alike as possible) data storing structure", so as to keep your real data/website private but giving us a real case to provide you with either the solution or the path towards it.

Regards!

 

[mikey111]

Thank you for the suggestion, SirJB7. I provided the sample data below. I would be very grateful for any suggestions on how to approach this -- I'm really at a loss.


http://dl.dropbox.com/u/18326806/Mikey%20sample.xlsx

 

[SirJB7]

Hi, mikey111!


Give a look at this file, open it allowing macros, press the cyan button and check what happens. Warning: if you're gonna run it on a thousand URLs, pour yourself a dozen cups of coffee at least. It seems to be running on an XT of 4.77 Mhz.


http://dl.dropbox.com/u/60558749/Macros%20%2B%20Web%20Query%20%3D%20Huge%20Headache.%20Can%20anyone%20help%20me_%20%28for%20mikey111%20at%20chandoo.org%29.xlsm


Regards!

 

[mikey111]

@SirJB7 -- That's exactly what I wanted. Thank you so much for providing me with this. This is amazing, and I'm able to use it right away. I'm going to study the macro that you created so I can understand it all.


Thank you again!

 

[SirJB7]

Hi, mikey111!


You're welcome back whenever needed/wanted.

The macro is really simple, it just loops thru column A until a blank, and calls another procedure with parameters URL and destination row (column is 2 always). How did I build this second magic procedure? Excel did it for me, I just recorded a macro and then adapted it to be called from first simple subroutine. There's been nothing hidden or unrevealed behind this.

I tell you this so as you don't think you'll have something strange to discover and study for a long time, it'll take you 5 minutes... and then go to Excel help, QueryTable object, and you won't get much more: just apply the recipe.


Regards!

 

[williamkscheung]

@SirJB7 -- I am totally a new user to VBA, and really amazed by your macro here which can definitely solve my headache in importing data from various web links when it is revised appropriately.

Here are some sample webpages' links that I want to import the data from:

http://txhist.centadata.com/tfs_centadata/Pih2Sln/TransactionHistory.aspx?type=1&code=VDRRURFHRO
http://txhist.centadata.com/tfs_centadata/Pih2Sln/TransactionHistory.aspx?type=1&code=VDRRURFARO

I tried to change the codes based on your file, but failed to import the data in a new spreadsheet. (I can only import the entire page in the first link, and then it stops... I would like to have one spreadsheet obtaining one webpage link data for the further data analysis, how can I make it?

Highly appreciated if you can throw me some light on this, many many thanks!!!

Will