Solve .CommandType=0 error in excel

Question

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"

Solution

From the MSDN entry regarding QueryTable.CommandType Property (Excel):

You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery.

You're not making an OLEDB query so the QueryType is something else. Since it's something else, then you can't set the CommandType, so just remove it.

Comments

[Ƭᴇcʜιᴇ007]

That was example code I provided you in your other question to show you how you could use a loop in your context. I told you it was untested and incomplete. You were supposed to take the information/example provided and adapt it into your own code, not just run it as-is and expect it to work. :)

[Ƭᴇcʜιᴇ007]

Also, please do your part. What have you research on the error you're getting? What have you tried already in attempts to fix it?

[user3481670]

I have been having problems with .CommandType = 0 in all of my code. Everytime I try to edit my code it doesn't work. I supposed that your code likely had less errors than mine so it would be easier to see what the issue is

[user3481670]

I have seen some people have success with changing the 0 to either Default, SQL, or 1 and none of those have worked for me