runtime error 13 type mismatch vba while downloading mutliple files from web

Question

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error:

Excel VBA Run-time error '13' Type mismatch

I didn't change anything in the macro and don't know why am I getting the error. Furthermore it takes ages to update the macro every time I put it running (the macro has to run about 9000 rows).

ERROR is somewhere "FileData = WHTTP.ResponseBody"

Sub Test2()
    Dim A As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object

    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0

    If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"

    For A = 1 To 228
        MyFile = Cells(A, 1).Text
        TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
        WHTTP.Open "GET", MyFile, False
        WHTTP.Send
        FileData = WHTTP.ResponseBody

        FileNum = FreeFile
        Open "C:\MyDownloads\" & TempFile For Binary Access Write As #FileNum
            Put #FileNum, 1, FileData
        Close #FileNum
    Next
    Set WHTTP = Nothing
    MsgBox "Open the folder [ C:\MyDownloads ] for the downloaded file..."
End Sub

Answer 1

Put On Error Resume Next above the line causing an error (probably this line WHTTP.Send). Put this block of code after your line with an error.

Files/Web Addresses/Registry keys - YOU MUST ASSUME IT MAY NOT WORK and trap errors so you know why (and where it's not working). Usually these are not programming questions.

If err.number <> 0 then 
    ERRString = ErrString &  "" 
    ERRString = ErrString &  "Error getting file" 
    ERRString = ErrString &  "==================" 
    ERRString = ErrString &  "" 
    ERRString = ErrString &  "Error " & err.number & "(0x" & hex(err.number) & ") " & err.description 
    ERRString = ErrString &  "Source " & err.source 
    ERRString = ErrString &  "" 
    ERRString = ErrString &  "HTTP Error " & WHTTP.Status & " " & WHTTP.StatusText
    ERRString = ErrString &     WHTTP.getAllResponseHeaders
    Msgbox ErrString
End If

Answer 2

Just download direct using API call and URL

Option Explicit

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" ( _
    ByVal pCaller As LongPtr, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As LongPtr, _
    ByVal lpfnCB As LongPtr _
    ) As Long
    Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
    Alias "DeleteUrlCacheEntryA" ( _
    ByVal lpszUrlName As String _
    ) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" _
                             Alias "URLDownloadToFileA" ( _
                             ByVal pCaller As Long, _
                             ByVal szURL As String, _
                             ByVal szFileName As String, _
                             ByVal dwReserved As Long, _
                             ByVal lpfnCB As Long _
                             ) As Long
    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
                             Alias "DeleteUrlCacheEntryA" ( _
                             ByVal lpszUrlName As String _
                             ) As Long
#End If

Public Const BINDF_GETNEWESTVERSION As Long = &H10
Public Const folderName As String = "C:\Users\User\Desktop\Blah.zip" '<== Change to destination

Public Sub downloadIFolder()
    Dim ret As Long
    ret = URLDownloadToFile(0, "http://www.bseindia.com/BSEDATA/margins/VAR290716.zip", folderName, BINDF_GETNEWESTVERSION, 0)
    MsgBox ret
End Sub