Looping Web Query

**juanpablo** · 07-30-2012, 11:19 PM

Hi,

Im fairly proficient with Excel but rarely do much intense VBA. Anyway, I have been trying to create a looping macro that effectively takes a list of URL's down column A and outputs the data imported from the website into a separate column. Below is a copy of my code. Anyway, the code works fine but I have had to make a 'clunky' adjustment to the destination for the imported data to prevent it from pasting the data in ajdacent columns (i want it to paste it below the previous (or on top of) the previous data dump so the data works downwards not sidewards). Effectively I change the destination to be 20 rows below the previous paste. The problem is that the data from each of the websites seems to vary between 10 rows and 20 rows (and could possible be greater at some future date). I would like the macro to effectively paste each new data dump directly below the previous one.

I thought I had it figured with the RefreshStyle Property (xLEntireRows) in the web query which is meant to insert new rows to make way for the new data but this doesnt seem to be working. The listed URL's are basically ticker codes for different stocks being pulled from Google Finance (an example list is below) The data is anywhere from 10 to 20 rows down and a few columns wide. Even with the xLEntireRows property chosen the data seems to paste across the page.

http://www.google.com/finance?q=asx%3Abhp
http://www.google.com/finance?q=asx%3Ario

Finally, does anybody know if you can put multiple web queries into an iqy file to be run from excel? I am currently running this macro off an old PC and would like to run it from MAC 2011 (which I believe doesnt support web queries) but can import data from Url's specified in an IQY file does work. However, I can only get this to work for a single web query (i.e. a single URL) but was wondering if you could populate the IQY file with multiple URLS and get it to run as a batch. Havent been able to find much info on this but was thinking there should be a way around as being able to only use a single web query would seem to be rather useless I would have thought.

Anyway, any help would be greatly appreciated.

Cheers.

Please Login or Register  to view this content.

**Cutter** · 07-31-2012, 09:18 AM

@ juanpablo

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.