Create a download macro that picks up URL from cell value

[Fishboy]

Hi all,


I have incorporated a macro which on button press goes to a pre-defined web address (which is a direct link to a download), then saves to a pre-defined as a pre-defined filename. The code I am currently using is as follows:


Sub Download_CSV()
Application.ScreenUpdating = False
Dim myURL As String
myURL = "www.somewebsite.com/mydownload"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile ("C:\My Folder\Filename.csv"), 2
oStream.Close
End If
End Sub


What I would like to do is to incorporate the ability for the URL and destination filepath to be defined by values on the main spreadsheet.


For example, on my spreadsheet I have a cell containing my desired URL (lets say A1 for arguements sake) and that would allow the macro to interpret

myURL = Worksheets("Sheet1").Range("A1").Value or something similar.


I would also like to do the same thing for the save filepath, so lets say A2 has a value of C:\My Folder\Filename.csv, the macro would be able to interpret

oStream.SaveToFile (Worksheets("Sheet1").Range("A2").Value), 2


Is this something that can be worked into my code? Do I need entirely new code to accommodate this? Can this not be done at all?


Any help is, as always, greatly appreciated.

 

[Fishboy]

Hmmm, bumping.

I don't know if nowadays I am just asking for more than Excel is capable of, or I am asking things that none of the experts understand, but this current query and my last three topics have not so much as had a "Sorry, dont know how to help" response from anybody.

 

[John_w]

What I would like to do is to incorporate the ability for the URL and destination filepath to be defined by values on the main spreadsheet.


For example, on my spreadsheet I have a cell containing my desired URL (lets say A1 for arguements sake) and that would allow the macro to interpret

myURL = Worksheets("Sheet1").Range("A1").Value or something similar.


I would also like to do the same thing for the save filepath, so lets say A2 has a value of C:\My Folder\Filename.csv, the macro would be able to interpret

oStream.SaveToFile (Worksheets("Sheet1").Range("A2").Value), 2

You've answered your own question. That is exactly the code you would use. Replace the first myURL = statement with the line above (you don't need the second myURL = statement), and the SaveToFile statement with the line above.

 

[Fishboy]

Thanks John_W, I must have made a typo or something when I originally tried this as it didn't work, but using my own examples from here fixed the problem!

Thank you for taking the time to look this over for me.