Hey there, let's break down your two problems— the sudden runtime error '13' (Type Mismatch) at FileData = WHTTP.ResponseBody and the slow performance when processing 9000 rows of data. Here's how to fix both:

1. Resolving Runtime Error '13' #

The error happens because WHTTP.ResponseBody isn't returning a byte array as expected. This usually stems from failed HTTP requests (e.g., invalid URLs, 404 errors, timeouts) that your current code doesn't handle properly. Let's fix that:

Key Issues in Your Current Code: #

• On Error Resume Next masks errors during the Open/Send steps, so you don't know if a request failed before trying to assign ResponseBody to FileData.
• No validation for invalid URLs (empty cells, malformed links) in column A.

Fixed Code Snippet with Error Handling: #

Sub Test2_Fixed()
    Dim A As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String, TempFile As String
    Dim WHTTP As Object
    Dim savePath As String
    
    ' Initialize save path
    savePath = "C:\MyDownloads\"
    If Dir(savePath, vbDirectory) = Empty Then
        MkDir savePath
    End If
    
    ' Create WinHTTP object with proper error checking
    On Error Resume Next
    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
    If Err.Number <> 0 Then
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
    End If
    On Error GoTo 0
    
    If WHTTP Is Nothing Then
        MsgBox "Failed to create WinHTTP object!", vbCritical
        Exit Sub
    End If
    
    ' Loop through rows (adjust 228 to your actual last row)
    For A = 1 To 228
        MyFile = Trim(Cells(A, 1).Text)
        
        ' Skip empty or invalid URLs
        If MyFile = "" Or InStr(MyFile, "http") = 0 Then
            Debug.Print "Skipping invalid URL at row " & A
            GoTo NextRow
        End If
        
        ' Extract filename from URL
        TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
        If TempFile = "" Then
            Debug.Print "Could not extract filename from URL at row " & A
            GoTo NextRow
        End If
        
        ' Send HTTP request with error handling
        On Error Resume Next
        WHTTP.Open "GET", MyFile, False
        WHTTP.Send
        
        ' Check if request succeeded (status code 200 = OK)
        If Err.Number <> 0 Or WHTTP.Status <> 200 Then
            Debug.Print "Failed to download from URL at row " & A & " (Error: " & Err.Description & ", Status: " & WHTTP.Status & ")"
            Err.Clear
            GoTo NextRow
        End If
        On Error GoTo 0
        
        ' Assign response body only if request succeeded
        FileData = WHTTP.ResponseBody
        
        ' Save file to disk
        FileNum = FreeFile
        Open savePath & TempFile For Binary Access Write As #FileNum
        Put #FileNum, 1, FileData
        Close #FileNum
        
NextRow:
    Next A
    
    Set WHTTP = Nothing
    MsgBox "Open the folder [ " & savePath & " ] for the downloaded files..."
End Sub

Why This Works: #

• We validate URLs before sending requests, skipping empty or malformed links.
• We check the HTTP status code (WHTTP.Status = 200) to ensure the request succeeded before accessing ResponseBody.
• We add debug prints to track which rows are failing, making it easier to troubleshoot bad URLs.

2. Optimizing Performance for 9000 Rows #

Processing 9000 rows with synchronous requests will be slow by default, but we can speed things up with these tweaks:

Quick Performance Wins: #

• Disable Excel's background processes during the macro to reduce overhead:

  ' Add these lines at the start of your macro
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  

  Don't forget to re-enable them at the end (even if an error occurs):

  ' Add these lines before exiting the macro
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  

• Read all URLs into an array first instead of accessing Cells(A,1) in each loop—this is much faster:

  ' Replace the loop setup with this to read URLs into an array
  Dim lastRow As Long
  Dim urlArray As Variant
  
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  urlArray = Range(Cells(1,1), Cells(lastRow,1)).Value
  
  For A = 1 To lastRow
      MyFile = Trim(urlArray(A,1))
      ' Rest of the loop logic...
  Next A
  

• Reuse the WinHTTP object (your code already does this, which is good—creating a new object per request would be way slower).

Advanced: Asynchronous Requests #

For even faster downloads, you could use asynchronous WinHTTP requests (since synchronous requests wait for each download to finish before starting the next). This requires using class modules to handle callbacks, but it can drastically reduce total runtime for large datasets.

内容的提问来源于stack exchange，提问作者nitin goyal