☰

使用循环提取数据到 Excel

tag-icon • tag-icon vba microsoft-excel-2013
使用循环提取数据到 Excel

我是第一次来这里，并且还不熟悉 Excel 2013，所以如果我在某个地方搞砸了，请告诉我！

我正在尝试收集 1932 年至 2014 年底特律红翼队的统计数据。我最近了解到，我可以通过“数据”->“获取外部数据”->“从 Web”从网站提取数据

到目前为止，我一直在打开一个新工作表，将数据拉入 Excel，然后重命名工作表以匹配年份

下面是我尝试循环，但它不起作用

理想的情况是，我运行一个宏，它会创建一个新工作表，然后在该工作表中填写从 1932-33 赛季到 2013-2014 赛季的每一年的数据，并将工作表重命名为与年份相匹配。

（例如：我运行宏，它会创建一个名为“1932-33”的工作表，从网站中提取数据并将其放入工作表中。然后，它会创建一个名为“1933-34”的工作表，从网站中提取数据并将其放入工作表中）

重要笔记 这是 1932-33 赛季网站的网址

http://www.whatifsports.com/nhl-l/profile_team.asp?hfid=11&season=1932-33

我发现要更改年份，只需将 URL 末尾的“1932-33”调整为您想要的年份。

任何帮助都将不胜感激！

Sub firstLoopAttempt()
'
' firstLoopAttempt Macro
'

'
Dim i As Integer

For i = 1942 To 2014

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.whatifsports.com/nhl-l/profile_team.asp?hfid=11&season=1942-43" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "profile_team.asp?hfid=11&season=1942-43"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Next i
    ActiveCell.FormulaR1C1 = ""
    Range("C1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("C3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B5").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A8").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A25").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A29").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A30").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A31").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A32").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A33").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A34").Select
    ActiveCell.FormulaR1C1 = ""
    Cells.Replace What:="View Player Profile on Hockey-Reference.com", _
        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A:A").Select
    Range("A4").Activate
    Selection.ColumnWidth = 21.91
    Columns("B:B").Select
    Range("B4").Activate
    Selection.ColumnWidth = 4.09
    Columns("C:C").Select
    Range("C4").Activate
    Selection.ColumnWidth = 3.09
End Sub

答案1

你用 for 循环来循环它，但从来没有用“i”来做任何事情。这可能是它不起作用的原因。;)

尝试这样的事情（警告，这是未经测试的，并且没有实现转换错误检查）：

Dim startYear As Integer
Dim endYear As Integer
Dim strStartYear as String

For startYear = 1942 To 2014

    ' Convert the current start year number to a string, then take the last two characters and assign to strStartYear
    ' So 1942 becomes "42".
    strStartYear = Right(CStr(startYear),2)
    ' Convert the string back into an (integer) number, and add 1 to create the End year.
    endYear = CInt(strStartYear)+1

    ' Use these variables in your other commands to specify the start/end year
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.whatifsports.com/nhl-l/profile_team.asp?hfid=11&season=" & startYear & "-" & endYear _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "profile_team.asp?hfid=11&season=" & startYear & "-" & endYear
        'other stuff omitted  for brevity
    End With
Next startYear

相关内容