Download WAV file from URL link

[Billy Kirk]

Hello,

I have a list of URL links in excel and each link goes to a breif WAV file recording. In fact, when you click on the link the option pops up to either open or download. Is there a way to through VBA, MACRO, or other automated means to run through this list of over 40,000 and download, save these wav files into a folder and name the file based on the contents in column A and B in that same row? So cell M2 has the URL link and the file would be saved based on the contents in cells A2 and B2.

Thanks.

 

[Kenneth Hobson]

If you know the URL link, it is easy. If not, you have to do some iexplorer automation depending on the site.

 

[Billy Kirk]

Yes I do know the URL link I have the entire list in excel.

 

[Billy Kirk]

Can anyone direct me to a source where I can make this work. Also, it is not images that we will be downloading and saving but rather a wav audio file.

 

[Kenneth Hobson]

Change the value in Main() to suit. e.g. Change value of sFolder. You may need to concatenate the ".wav" if not in column B. As-is, it assumes that B2 is like Ken.wav.

I set it up so you can test just row 2. Remove Exit Sub line when it works as needed for the first one.

'Written:  October 26, 2016
'Author:   Leith Ross
'Summary:  Function Downloads A Web Site Resource To A Local Disk File

Private Const E_OUTOFMEMORY As Long = &H8007000E
Private Const INET_E_DOWNLOAD_FAILURE As Long = &H800C0008

Private Declare Function URLDownloadToFile Lib "urlmon.dll" Alias _
  "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
  ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadURLtoFile(ByVal URL As String, ByVal vFolder As Variant, ByVal FileName As String) As Boolean
  Dim Msg             As String
  Dim oFolder         As Object
  
  With CreateObject("Shell.Application")
    Set oFolder = .Namespace(vFolder)
  End With
  
  If oFolder Is Nothing Then
    MsgBox "Folder '" & vFolder & "' Not Found.", vbExclamation
    Exit Function
  End If
  
  Select Case URLDownloadToFile(0&, URL, vFolder & FileName, 0&, 0&)
    Case 0: DownloadURLtoFile = True
    Case E_OUTOFMEMORY: Msg = "Insufficient Memory To Complete The Operation."
    Case INET_E_DOWNLOAD_FAILURE: Msg = "The Specified Resource Or Callback Interface Was Invalid."
  End Select
  
  If Not DownloadURLtoFile Then Debug.Print Msg
End Function

Sub Main()
  Dim Cell As Range, Cell2 As Range, sFolder As String
  Dim FSO, fn As String
  
  Set FSO = CreateObject("Scripting.FileSystemObject")
  
  'sFolder = CreateObject("Wscript.Shell").Specialfolders("Desktop") & "\"
  sFolder = "c:\t\"
  
  'On Error Resume Next
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Set Cell2 = Cell.Offset(, 1)
    fn = FSO.GetFilename(sFolder & Cell2.Value2)
    If Dir(sFolder & Cell2.Value2) = "" Then _
      DownloadURLtoFile Cell.Value2, sFolder, fn
      Exit Sub
  Next Cell
  
  MsgBox "Done...", 64
  Set FSO = Nothing
End Sub

 

[Billy Kirk]

Is this assuming that in Row 1 is headers and in Row 2 is the data. With the contents of cells A2 and cell B2 set as the name of the downloaded saved file and the wav file URL link in cell C2?

 

[Kenneth Hobson]

Url column a, base file name column b.

 

[Billy Kirk]

Hello,
With the URL in column A and the base file name in column B and with a new temp folder on the desktop it worked. The file downloaded into folder with the exact contents of cell B2 as the name of the file. The file type is "File". Is it possible to save it as an audio file like a .wav file?

 

[Kenneth Hobson]

As i explained, if b2=ken.wav, and the url link file is a wav file, it saves as such. As i also explained, if b2 is say just ken, and the url file is a wav file, you have to concatenate the file extension. E.g. After the fn = line add: fn=fn & ".wav"