Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: Excel - Importing External Data

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

    Code:
    Sub DoLotsOfWebQueries()
    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
    End Sub
    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) = ...

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Debug Errors

    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

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by ntbluez
    Thanks for the syntax commands.

    However, I am encountering errors in Excel 2003 when running this Macro.
    Not much I can do without knowing what the errors are.

    Also, where do I enter the URL string value within the code?
    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:

    Code:
    Sub test()
    urlcount = 32 ' Or however many
    ReDim urllist(1 To urlcount), labellist(1 To urlcount)
    urllist(1) = "http://whatever/"
    labellist(1) = "first label"
    urllist(2) = "http://anotherone/"
    labellist(2) = "second label"
    ...
    
    For i = 1 To urlcount
        Set addedSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    
    ... and the rest is the same

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •