Download csv files from hyperlinks using vba

[Samadhan Gaikwad]

I have around 200 (indirect) hyperlinks when clicked excel asks for opening the csv file when OK, it open that csv file which I need to download. And when copy hyperlink to IE, it ask me to save or open that file.

The length of hyperlink is around 1000 char.
Please help me to download those files to one location.

 

[Samadhan Gaikwad]

I have got below code but it needs to save file manually in downloads page:

Sub TESTING()
'Need to reference to Microsoft Internet Controls
    Dim URL As String
    Dim i As Long
    'URL = Worksheets("References & Resources").Range("URLMSL")
  ' URL = "http://www.mrexcel.com/board2/viewtopic.php?t=69685" 'for TEST
  ' i = 2
  For i = 2 To 192
  URL = Range("E" & i).Value
    Dim IE As Object
    Set IE = CreateObject("internetexplorer.application")
    IE.Visible = True
    IE.Navigate URL
'    Do While IE.ReadyState <> 4
'        DoEvents
'    Loop
    SendKeys "%S"
    Next
    IE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT    'SaveAs
   
End Sub

 

[Samadhan Gaikwad]

I could do this with powershell command as what I want like giving output name also:
Invoke powershell cmd and run following command once:

$client = new-object System.Net.WebClient

Then create commands like this in excel:

$client.DownloadFile(“Download Link”,“File Destination\file name.file extension”)

Paste them all, it's done!