Multiple URL's to Get External Data

[rhysm144]

I Have column A filled with ~300 URL's (each in a new row).

I want to turn each of these URL's into a get external data query each in a new worksheet so they refresh consistently.

The only way I know how to do this is to save each url as a .iqy and to manually run a get external data on a new worksheet for each.

I was wondering if there was any way (likely vba) to automate this process?

Thank you!

 

[wigi]

Hi,

I would advise to record the VBA code while performing the steps manually.
After that, we will make a loop from 1 to 300 to automate the whole thing.

 

[rhysm144]

Problem is I am unsure how to turn up raw url in A1 into a get external data query with saving it as an .iqy first?

 

[wigi]

No, just choose: Data > From Web
and enter the URL by hand.

After that we can make it dynamic such that typing manually is not needed anymore.

 

[rhysm144]

I am using Excel Mac 2016 so as suck don't have data > web

 

[wigi]

Oh like that.
Then I think it's best that someone with Mac Excel experience deals with this one. I never used Excel on the Mac so I wouldn't know what would be the (sometimes subtle) differences/annoyances.

 

[rhysm144]

Yeh, it's really irritating.

Thanks anyways