VBA - Save a file from a Website

[giffordj]

I ran into a problem with the following code

Dim URL As String
URL = Worksheets("References & Resources").Range("URLMSL")
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Navigate URL
IE.Visible = True

My problem is that on most of the workstations here, it will open internet explorer, then open the file in Excel. I just want it to Save the file. I know there is probably another way to do this that will satisfy my need.

 

[Tejasp]

Hi giffordj,
If you just want to download a file from the net, you don't have to use IE.
Please try something like this with API named "URLDownloadToFileA".

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
 
Sub DownloadFilefromWeb()
    Dim strSavePath As String
    Dim URL As String, ext As String
    Dim buf, ret As Long
    URL = Worksheets("References & Resources").Range("URLMSL").Value
    buf = Split(URL, ".")
    ext = buf(UBound(buf))
    strSavePath = ThisWorkbook.Path & "\" & "DownloadedFile." & ext
    ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)
    If ret = 0 Then
        MsgBox "Download has been succeed!"
    Else
        MsgBox "Error"
    End If
End Sub

When tried above code it takes me to an infinite loop + IE shows Download window with open and save option for the link.

 

[John_w]

I don't see how that code would cause an infinite loop.

To download a file using IE automation, try my code at Using UIAutomationClient to automate the Save As file download in IE11. The first post in that thread explains the download procedure's arguments and how to call it.