Results 1 to 9 of 9

Thread: Download WAV file from URL link
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Download WAV file from URL link

    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.

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,066
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

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

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

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

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    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.

  5. #5
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,066
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    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.

    Code:
    '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
    Last edited by Kenneth Hobson; Aug 1st, 2019 at 03:29 PM.

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    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?

  7. #7
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,066
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    Url column a, base file name column b.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    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?

  9. #9
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,066
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Download WAV file from URL link

    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"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •