Commandtype 0 ошибка

Я пытаюсь запустить этот макрос в Excel, который извлекает данные с веб-сайта в цикле.
Есть одна таблица, которую мне нужно извлечь из каждой из 50 веб-страниц, и есть цикл для запуска функции, которая извлекает данные из каждого веб-сайта.

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

Код ошибки, который я получаю: Run Time Error ‘5’ Недопустимая процедура или аргумент

Строка, которую он выделяет, является «.CommandType = 0 «

Источник

I am trying to run this macro in Excel that pulls data from a website in a loop.
There is one table I need to pull from each of about 50 webpages and the loop is there to run the function that pulls data from each website

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

The error code I’m getting is Run Time Error ‘5’ Invalid procedure or argument

The line it is highlighting is «.CommandType = 0»

Источник

Lots of similar questions going on but google and stack overflow are not touching the part I think I need. I am trying to import a pipe delimited text file with a macro. When I record macro, this is what I get:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:UsersjohnsmithDesktopMacro TinkeringABC_Financials_ALL(Delimited).txt" _
    , Destination:=Range("$A$1"))
    .CommandType = 0
    .Name = "ABC_Financials_ALL(Delimited)_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

When I try to run the macro, it fails, and when I debug, it points me in the direction of «.CommandType = 0»

Anyone know if there is a value that will get this macro going? Or are my problems worse than that?

asked Aug 18, 2015 at 20:42

Alright.

More confident in just deleting possible trash off Mukul215’s suggestion, I kept deleting stuff and retrying.

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:UsersjohnsmithDesktopMacro TinkeringABC_Financials_ALL(Delimited).txt" _
    , Destination:=Range("$A$1"))
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

And this one does it.

answered Aug 18, 2015 at 21:11

user2989297user2989297

1731 gold badge1 silver badge9 bronze badges

Источник

Я пытаюсь запустить этот макрос в Excel, который извлекает данные с веб-сайта в цикле.
Есть одна таблица, которую мне нужно вытащить из каждой из около 50 веб-страниц, и цикл там, чтобы запустить функцию, которая извлекает данные с каждого сайта

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"))
        .CommandType = 0
        .Name = "profile_team.asp?hfid=11&season=" & startYear & "-" & endYear
        'other stuff omitted  for brevity
    End With
Next startYear

код ошибки, который я получаю, — это ошибка времени выполнения » 5 » недопустимая процедура или аргумент

в строке подсветка «.CommandType = 0»

Источник

microsoft-excel-2013vba

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

The error code I’m getting is Run Time Error ‘5’ Invalid procedure or argument

The line it is highlighting is «.CommandType = 0»

Related Question

Не пропустите эти материалы по теме: