Using WinHttpReq in vba to download an online file, works only on the first time - need destroy winHTTPReq object between loops

Question

first time here - hoping for some help!! i am working on a project in vba in excel, looking to pull in data files from the web. i have coded this in a loop, where it pulls down a file, does some manipulation and saves it. it then should loop to the next file and pull it down. this works well the first time, but the second time it does not pull it down (although if restart on second filename no issue). I notice the WinHttpReq.responseBody comes back with a long value the second time (but isn't readable). i believe this is because i need to destroy the WinHttpReq object between loops, however when i try to Dim or Set as new object i get an error saying 'expected identifier'. Unsure what object identifier to use.

code is as follows:

Private Sub DownloadFile(myURL As String, target As String)

full code attached - apologies - it is pretty clunky and needs a great deal of tidying up. the issue is in the DownloadFile sub

'Main Procedure Sub Backfill()

Dim url As String, targetZip As String

Dim tbl As ListObject
Dim iRows As Integer
Dim targetFileCSV As String


url = Sheets("Front").Range("urlAddress").Value
targetZipFolder = Sheets("Front").Range("targetFolder").Value & "testZIPData\"
targetCSVFolder = Sheets("Front").Range("targetFolder").Value
tempCSVFolder = Sheets("Front").Range("tempCSVpath").Value
dbTableDest = Sheets("Front").Range("dbTableDestArray").Value
sqlSource = Sheets("Front").Range("sqlSource").Value
destDB = Sheets("Front").Range("sqlDestDb").Value
dbTablesCount = Sheets("Front").Range("dbTableDestArray").Rows.Count
dbTablePrefix = Sheets("Front").Range("dbTablePrefix").Value
Dim reportFilterFieldArray(1 To 3) As String
'the below not currently used
'Dim dbTableDestArray(1 To 3) As String
'Dim tempCSVdelCols(1 To 3) As String

For iTable = 1 To dbTablesCount
reportFilterFieldArray(iTable) = CStr(dbTableDest(iTable, 1))
Next
'the below not currently used
'For iTable = 1 To dbTablesCount
'dbTableDestArray(iTable) = CStr(dbTableDest(iTable, 2))
'Next
'For iTable = 1 To dbTablesCount
'tempCSVdelCols(iTable) = CStr(dbTableDest(iTable, 3))
'Next

Sheets("onlineFiles").Select
Set tbl = ActiveSheet.ListObjects("Document")
iRows = tbl.Range.Rows.Count
  
For i = 1 To iRows - 1

    targetZip = [Document[NeedDownload]].Rows(i)
    
    If targetZip = "Download" Then
    
    targetZip = [Document[ZIP]].Rows(i)
    url = url & targetZip
    targetCsv = Left(targetZip, Len(targetZip) - 4)

    targetFileCSV = targetCSVFolder & targetCsv
    MsgBox url
    
    Call DownloadCSV(url, targetZip, targetCSVFolder, targetZipFolder, tempCSVFolder) '
    
    'Call createTempCSVforBulkInsert(targetFileCSV, reportFilterFieldArray, tempCSVFolder, tempCSVdelCols)
   
    'Call ConnectSqlServer(sqlSource, destDB, reportFilterFieldArray, tempCSVFolder, dbTablePrefix)

    'Call writeToDb(reportFilterFieldArray, tempCSVFolder, dbTablePrefix)
    
    Sheets("directoryFiles").Select
    ActiveSheet.Range("A3").Select
    Selection.AutoFill Destination:=Range("A3:A3000")
    
    Else
    
    End If
Next i
    
    

End Sub

Sub DownloadCSV(url, targetZip, targetCSVFolder, targetZipFolder, tempCSVFolder)

Dim urlSt As String
Dim targetCsv As String, targetTxt As String
Dim targetFileZip As String, targetFileCSV As String
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
Dim newSheet As Worksheet

urlSt = CStr(url)
MsgBox urlSt

targetCsv = Left(targetZip, Len(targetZip) - 4)

targetFileZip = targetZipFolder & targetZip
targetFileCSV = targetCSVFolder & targetCsv

'1 download file
DownloadFile urlSt, targetFileZip
'MsgBox urlSt
'2 extract contents
'CVar is convert to variant
Call UnzipAFile(CVar(targetFileZip), CVar(targetCSVFolder))

'3 rename file
'targetFileCSV = targetFileCSV & ".csv"
'Name targetFileCSV As targetFileCSV
'Save (targetFileCSV)
'Range("C13").Value = targetFileCSV

'4 Load data

End Sub

Private Sub DownloadFile(myURL As String, target As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send

myURL2 = WinHttpReq.responseBody
'MsgBox myURL2
'MsgBox myURL

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile target, 2  ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
Set WinHttpReq = Nothing

End Sub

Sub UnzipAFile(zippedFileFullName As Variant, unzipToPath As Variant)

Dim ShellApp As Object Dim zippedFile As Variant, csvToPath As Variant zippedFile = CVar(zippedFileFullName) csvToPath = CVar(unzipToPath)

'Copy the files & folders from the zip into a folder Set ShellApp = CreateObject("Shell.Application") 'MsgBox zippedFileFullName ShellApp.Namespace(unzipToPath).CopyHere ShellApp.Namespace(zippedFileFullName).items

End Sub

Answer 1

I've modified your code slightly, changing Microsoft.XMLHTTP which could raise an Access Denied error, to MSXML2.ServerXMLHTTP.6.0. See comments in code.

Option Explicit

Sub test()
  Call DownloadFile("https://google.com", "C:\Temp\Test1.txt")
  Call DownloadFile("https://it.finance.yahoo.com", "C:\Temp\Test2.txt")
End Sub

Private Sub DownloadFile(myURL As String, target As String)
    Dim WinHttpReq As Object
    Dim oStream As Object
    
    '*
    '* Microsoft.XMLHTTP -> Access Denied
    '* The standard request fired from a local machine forbids access to sites that aren't trusted by IE
    '* MSXML2.ServerXMLHTTP.6.0 is the server-side object, that doesn't perform those checks
    '*
    Set WinHttpReq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    WinHttpReq.Open "GET", myURL, False
    WinHttpReq.send

    myURL = WinHttpReq.responseBody

    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile target, 2  ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If
    Set WinHttpReq = Nothing
End Sub

Answer 2

ok - i sorted this out. was a problem with the URL update that was being sent back via the loop rather than anything to do with the WinHTTPReq. appreciate the input though - you people are awesome :)