• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Download file from URL using VBA

Status
Not open for further replies.
G

GillB

New Member
Hi,
I'd appreciate any help on this.
I have a URL that when clicked it automatically downloads an Excel file to the Downloads folder on the C drive (C:\Users\Gill\Downloads)
I am then going to use the data in that downloaded Excel file to populate a report - that's the easy bit!

I'm having trouble (a) downloading the file from the URL and putting it in the C:\Temp\ folder and then (b) just opening the file using a Macro.
This is the code I am using at the moment:

Dim myURL As String
myURL = "http://regonline.blahblah.com/activereports/smartLink.aspx?eventid=Xhp4iBccSYE=&crid=161285"

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:\Temp\")
oStream.Close
End If

The debugger stops the code at WinHttpReq.Send.

After that I need to open the file automatically and then import the data into another file. I'm stuck just on opening the file, not the importing of the data...

In and ideal world I would have a macro that when the user clicks the button in the report workbook it would automatically to the following steps:
1. download the file from the static URL
2. import the data from the downloaded file directly in a specified worksheet in the report workbook.

I'm sorry I have nothing more to give on this, but any help at all would be very welcome.

Thanks
GillB
 
Hi, GillB!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, I assume that the source of your code is that of Deepak's last post since it's almost identical except for the login to the website with user Id and password and the save in .CSV format. Which in fact is based on this article of MSDN network:
http://social.msdn.microsoft.com/Fo...el-2007-use-vba-to-download-save-csv-from-url

But in your code you're not specifying the filename, just the folder path.

Besides, if you're running Windows Vista or higher you won't be able to directly writing on drive C, so if the error is at the save line, try changing the path to something like:
c.\users\<your_user>\desktop (for Win Vista, 7, 8)
c:\documents and settings\users\<your_user>\desktop (for Win XP)

Regards!

PS: To get rid of those differences you can use this as full path:
Code: 
Environ("SystemDrive") & Environ("HomePath") & "Desktop" & Application.PathSeparator & "file.xlsx"
 
Last edited:
Thanks to both Deepak and SirJB7 for the above.
A few responses to the above;
Deepak:
- the Url doesn't work because I deliberately made it not to work - there is sensitive information in the download, so I couldn't share it correctly.
- the firewall is not the issue as I can download the file myself with no issue from several firewalled locations which I have tested.

SirJB7:
- The code was a copy and paste from one of the links you provide above - to be very honest, I have a rudimentary idea of how that code is supposed to work, but not a thorough knowledge.
- The URL does not specify the filetype, but when clicked it downloads automatically an Excel file to the Downloads folder on my C drive.
- I'm running Windows 7 Home Basic
- The code is stopping at
oStream.SaveToFile "C:\Users\Gill\Documents", 2 ' 1 = no overwrite, 2 = overwrite
no matter if I change the filepath it still stops at the same location in the code

- Did I do something wrong in posting to this forum? I searched I felt exhaustively for an answer to my problem on this forum but with no success so hence why I posted my question. I'm sorry if this is replication of the question, but I honestly could not find the solution I was looking for anywhere.

- Also I am keen to point out that on clicking the URL the file automatically downloads to the Downloads folder. I just want to not have my users clicking the URL. I want the macro to call the URL, download the file, and extract the data from the downloaded file into a new workbook automatically.

Please, please help??
 
Hi, GillB!

The formal part first. You didn't anything wrong posting to this forum, we're here to help and learn, so just do it! (Nike motto). And I know that sometimes isn't easy to find something here at these forums, even if you remember exactly many keywords, but we're encouraged and recommended to do so first and if any doubt or nothing found then creating a new thread. So nothing to worry about, come in and enjoy... or suffer... but anyhow do come in. :)

Your issue. Have you understood the modifications I posted earlier? If so, could you give them a try? However give a look at the uploaded file, it has a sample working code to download a neither sensitive nor copyrighted file from this website and save it in the desktop as "file.xlsx".

The fixed code is this:
Code: 
Option Explicit

Sub DownloadXLFileFromURL()

    Dim myURL As String, sFilename As String
    myURL = "http://img.chandoo.org/hw/max-change-problem.xlsx"
    sFilename = Environ("SystemDrive") & Environ("HomePath") & _
            Application.PathSeparator & "Desktop" & Application.PathSeparator & _
            "file.xlsx"
   
    Dim WinHttpReq As Object, oStream As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False ', "username", "password"
    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 sFilename, 2  ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If

End Sub

Just advise if any issue.

Regards!
 

Attachments

  • Download file from URL using VBA (for GillB at chandoo.org).xlsm
    22.3 KB · Views: 255

Hi,

just a little reminder : every compatible file, whatever a workbook, a text file or any other that Excel loads,
can be directly opened in Excel from the net, no need to download it !
​
 
Thanks to everyone who replied to this thread.
I solved my problem - I think I over complicated the challenge and that was hindering me. So I broke the problem into its most basic steps and that worked.
First I called used this to open the URL:
ActiveWorkbook.FollowHyperlink Address:="https://etc.etc./etc."
this actually opened a file with a file extension called smartlink.aspx

then I did all I needed to do while the file was open. And then I closed it using:
Workbooks("smartLink.aspx").Close

Job done!!!

Again my thanks to everyone who replied and apologies if I wasted any time.
Cheers
Gill
 
Hi, GillB!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hello,

If the file is generated by a button (ex download), it is possible to be automatically download using VBA?

There is a web page, i have to login with user and password, then i have to click "download" button in order to view and download the excel file.
 
Thank you all. New Member, joined today: Your following code to import CSV file from web worked very efficiently quick with your URL. But when I change to my URL, it still creates/updates "file.xlsx", but the file can not be opened by Excel (some message to the effect "corrupt file"

My URL: https://www.nasdaq.com/api/v1/historical/AAPL/stocks/2020-04-30/2020-05-31 , the file name is "HistoricalQuotes.CSV"

Your good working code is:

>>> use code - tags <<<
Code: 
Dim myURL As String, sFilename As String
    myURL = "http://img.chandoo.org/hw/max-change-problem.xlsx"
    sFilename = Environ("SystemDrive") & Environ("HomePath") & _
            Application.PathSeparator & "Desktop" & Application.PathSeparator & _
            "file.xlsx"
  
    Dim WinHttpReq As Object, oStream As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False ', "username", "password"
    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 sFilename, 2  ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If

End Sub
 
Last edited by a moderator:
Earth said:
Thank you all. New Member, joined today: Your following code to import CSV file from web worked very efficiently quick with your URL. But when I change to my URL, it still creates/updates "file.xlsx", but the file can not be opened by Excel (some message to the effect "corrupt file"
Click to expand...
Welcome to the forum, Earth. Please create a new thread for your question as opposed to tacking onto the end of someone else's thread. You're more likely to get visibility of your question (since people will see a new thread with 0 responses), as opposed to here on a thread that's several years old.
 
Status
Not open for further replies.
Back
Top