URLDownloadToFile file type issues

[lmvk99]

Hi guys,

I have mish-mashed the following code together to go and grab an excel file from a website, download it and rename it.

Sub DownloadFilefromWeb()    Dim strSavePath As String
    Dim URL As String, ext As String
    Dim buf, ret As Long
    For Each cell In Range("new_url")
    If cell.Value = "" Then Exit Sub
    URL = cell.Value
    buf = Split(URL, ".")


    strSavePath = ThisWorkbook.Path & "\" & "DownloadedFile" & cell.Row - 1 & ".xls"
    ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)
    Next
End Sub

The problem is I keep getting errors about file types. When i manually download the file I can see it is in .xls format. but then I use that in the code and then try and open my downloaded files i get the error "The file you are trying to open "DownloadedFile.xls", is in a different format than specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open now?" I say yes and I get a corrupted file. yet if I manually download the same file it is fine. If I try any other file extension type I get the error "Excel found unreadable content in 'DownloadedFile1.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook click yes". I click yes and it tells me the file cannot be openned. Finally I tried the code with no extension. The file appears in the folder as expected by when i try and open with excel, nothing happens.

Any ideas?

The complicating factor is that the file extension may change so I'd rather not specify if at all possible. Note that the URL does not contain the file extension.

Thanks in advance!!!