Executing multiple web queries?

[stringer47]

Hi,





I'm using excel for Mac 15.23. I have hundreds of .txt files that are serving as web queries to bring data into excel.





Currently, I'm executing each query individually (Data > Get External Data > Run Saved Query....) then repeating with the next query, and the next one, and the next one....





Obviously, this procedure is relatively time consuming and I'm wondering if there is a way to execute MANY or even ALL of my queries at once instead of doing them individually as described.





Appreciate the help.

 

[mrshl9898]

Data>Refresh All doesn't refresh the queries?

 

[stringer47]

It would, yes.. But what I'm asking about is initially setting up my workbook with the queries.

I really don't have a need to have the data refresh, so perhaps I'm going about this the wrong way. I'm trying to import weekly football statistics from specific dates.

Instead of copying and pasting every week into excel, I thought this may be faster.

Maybe my question should be, is there a better way to pull data off of web pages?

Thanks for the reply.

 

[mrshl9898]

Is it a matter of changing the URL each week or are these all on one table?

It would be good to see some examples and what you are trying to achieve one week vs the next

So URL's and values.

 

[stringer47]

Ultimately, I'm trying to generate a data table that consists of about 30 statistical categories for each team, for each week of the season, from 2012-current. This data table will serve as my source for a vlookup function to bring the stats to the correct team/week/season in a schedule workbook. Here are the URLs for two weeks of one stat category I wish to use:

College Football Stats - College FB Team Third Downs per Game | TeamRankings.com

College Football third downs per game, by team.

www.teamrankings.com

College Football Stats - College FB Team Third Downs per Game | TeamRankings.com

College Football third downs per game, by team.

www.teamrankings.com

Thanks for the input, agian.

 

[mrshl9898]

I haven't done this kind of thing before, however.

I'd consider something like this, doing a season at a time:

**Note it looks for columns 2012 and 2011 in the below Queries.Add, this will need to change through the seasons I assume.

Sub Macro1()

Dim mydate As Date
Dim DateStr As String
Dim counter As Long

counter = 50  '''just a random number, cannot be duplicated through the book
mydate = DateValue("Sep 4, 2012")  '''Starting date of imports


Do Until mydate > DateValue("Oct 4, 2012")  '''end date
DateStr = Format(mydate, "yyyy-mm-dd")


    Sheets.Add After:=ActiveSheet
    ActiveWorkbook.Queries.Add Name:="Table " & counter & "", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.teamrankings.com/college-football/stat/third-downs-per-game?date=" & DateStr & """))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Rank"", Int64.Type}, {""Team"", type text}, {""2012"", type number}, {""Last 3"", type number}, {""Last 1"", Int64.Type}, {""Home"", type tex" & _
        "t}, {""Away"", type text}, {""2011"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table " & counter & """;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table " & counter & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With

ActiveSheet.Name = DateStr
mydate = mydate + 7  '''go to next week
counter = counter + 1
Loop
  

End Sub

 

[stringer47]

Wow.. So, first... Thanks for the help. Second, pardon my ignorance, as I obviously have lots to learn. That being said, what do I do with what you wrote!? Is it a file I run in excel, or do I type that directly into the workbook??

What is this "code" called? I'd like to learn more about it.

Again, sorry for the newbie questions and thanks for your time!

 

[mrshl9898]

Oh, sorry.

Follow steps 1 to 4 here

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

Then hit the play button

 

[stringer47]

I gave it a shot and I get a 438 error. Thanks... really interesting stuff, hopefully I can pick some of this up!

 

[mrshl9898]

Strange, in the VBA window (Alt+F11) go to Tools>References and tell me which ones are ticked