rename the file's name

[Verbamore]

Hi everybody,
I have to download a lot of files. I have URL for each of them.I listed all urls in one excel column.
The problem is when I save the second file. When VBA goes and check for the second url, find the file and when try to save it, the code shows me an error because is another file in the same folder with the same name
How i can put an index so from file1 to go file2?

Sub test()

Dim myURL As String
For I = 2 To 4
myURL = Worksheets("sheet1").Range("A2")
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
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 ("C:\Users\verbamore\Desktop\1.pdf")
    oStream.Close
End If
Next I

End Sub

[shg]

Untested:

Option Explicit

Sub test()
    Const sRoot     As String = "C:\Users\verbamore\Desktop\"
    Dim sURL        As String
    Dim i           As Long
    Dim WinHttpReq  As Object
    Dim oStream     As Object
    Dim iFile       As Long

    For i = 2 To 4
        sURL = Worksheets("Sheet1").Range("A2").Value2
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        With WinHttpReq
            .Open "GET", sURL, False
            .Send

            If .Status = 200 Then
                Set oStream = CreateObject("ADODB.Stream")
                oStream.Open
                oStream.Type = 1
                oStream.Write .ResponseBody

                Do
                    iFile = iFile + 1
                Loop While Len(Dir(sRoot & iFile & ".pdf"))

                oStream.SaveToFile sRoot & iFile & ".pdf"
                oStream.Close
            End If
        End With
    Next i
End Sub

That very first line is one you should get used to using.

[Verbamore]

Untested:

Option Explicit

Sub test()
    Const sRoot     As String = "C:\Users\verbamore\Desktop\"
    Dim sURL        As String
    Dim i           As Long
    Dim WinHttpReq  As Object
    Dim oStream     As Object
    Dim iFile       As Long

    For i = 2 To 4
        sURL = Worksheets("Sheet1").Range("A2").Value2
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        With WinHttpReq
            .Open "GET", sURL, False
            .Send

            If .Status = 200 Then
                Set oStream = CreateObject("ADODB.Stream")
                oStream.Open
                oStream.Type = 1
                oStream.Write .ResponseBody

                Do
                    iFile = iFile + 1
                Loop While Len(Dir(sRoot & iFile & ".pdf"))

                oStream.SaveToFile sRoot & iFile & ".pdf"
                oStream.Close
            End If
        End With
    Next i
End Sub

That very first line is one you should get used to using.

thanks shg

[shg]

You're welcome.

Please don't quote whole posts; it just clutters the forum.