Is there a command line or string sytax I can use to get data instead of using the "Import External Data, New Web Query" function?

I ask because, its tedious to use this "New Web Query" each time I want to import for three HTML tables: Offense, Defense, Special Teams.

There are 32 teams which I would like to get their roster data all at once, instead of performing this function 32 times.

Here is an example of the data I want to import into excel

Dallas Cowboys Roster - NFL Football - ESPN
NFL Football Teams, National Football League Teams, Football Teams - ESPN
New England Patriots Roster - NFL Football - ESPN

To do it in a macro, you need two things:

1. The URL you want to add.

2. The place you want to the resulting data.

Now, you don't know how big the darn thing's going to be. So I'll put them all in separate worksheets.

I'll also assume you're starting this macro with the list of URLs selected. Next to the URLs should be the labels for each URL.

Here's some code I wrote up that works in Office 2007. If you're using and older version but it handles web queries, this ought to work. I had to click around before it finished refreshing all the queries, but it did work.

What you've got to do is:

1. Turn on macros if you're in Office 2007. I forget how to do this, check the help menu. It's annoying.

2. Create a new macro. One way is to start recording and then stop. Then you can view it. If it asks you to assign a control-key shortcut, go with that. It's an easy way to run the macro.

3. Paste that code in between the Sub Macro1 and End Sub Macro1 lines.

4. Select your list of teams, run it, and you should get all your queries done.

See where the code says addedSheet.Cells(1,1)? If you wanted to put all the web queries down a row, it would be Cells(2,1). If you wanted the labels first and the URLs second, you would swap the 1 and 2 where it says urllist(i) = ... and labellist(i) = ...

Thanks for the syntax commands.

However, I am encountering errors in Excel 2003 when running this Macro.
Also, where do I enter the URL string value within the code?

Sub test()
urlcount = Selection.Rows().Count
ReDim urllist(1 To urlcount), labellist(1 To urlcount)
For i = 1 To urlcount
urllist(i) = Selection.Cells(i, 1).Value
labellist(i) = Selection.Cells(i, 2).Value
Next i
Rem Get the list of worksheets so we can add new ones as we go.
For i = 1 To urlcount
Set addedSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
Rem Not sure if this is needed.
addedSheet.Select
addedSheet.Name = labellist(i)
Set Qtbl = addedSheet.QueryTables.Add("URL;" & urllist(i), addedSheet.Cells(1, 1))
Qtbl.Name = labellist(i)
Qtbl.Refresh
Next i

ActiveWindow.SmallScroll Down:=3
End Sub

Not much I can do without knowing what the errors are.

As written, you just type them into the spreadsheet and highlight the cells. Sorry I didn't make that clear.

If you'd rather put the URLs straight in the code, it'd look like this: