Tuesday, November 1, 2011

Excel VBA: Download files from the Internet

There is no built-in function in Microsoft Excel which allows you to download contents from the Internet on the fly. To accomplish this task we need to use the API for WinInet. The use and explanation of API in VBA is for advanced users which have prior experience from either Visual Basic 6.0 or .NET.

Pitfalls
It is very important that all open Internet connections are closed as soon as the task is completed. WinInet only allows 2 concurrent connections to a given host. If you forget to shut down the connection after use, you will experience timeouts and misleading error messages. Please refer to the following website for more information related to the maximum allowed concurrent web connections:

Howto
The source code below should be pasted in a "Class Module" in Excel. If you are not sure how to open the VBA editor in Excel for your current Microsoft Office version, please refer to the following page:
  • Display the developer toolbar or ribbon in Excel

Create new class module:
  1. Open the Microsoft Visual Basic for Applications editor in Excel.
  2. Select Insert -> Class Module on the main menubar
  3. Rename the new class module to "WebClient"

Example
To use the code, you shold create a new instance of the class and any of the public methods:
  • DownloadFile - download a specific resource to a local file
  • UrlExists - check if a given URL exists

Dim objClient As New WebClient
Call objClient.DownloadFile("http://www.google.com", "c:\test.html")


Dependencies
The function "ReThrowError" is defined here:

Source Code
For the full source code, please visit
Lagt inn av kl.
Etiketter: , , , , , , , , , , , ,

10 comments:

  1. OliverMarch 13, 2012 at 12:39 AM

    Hi,
    the code is working fine, but if I download a ZIP file, it gets corrupted.
    Do you have any idea?
    Thanks Oliver

    ReplyDelete
  2. UlfMarch 14, 2012 at 9:12 AM

    The corrupted binary file is most likely related to the following statements:

    Dim strBuffer As String * BUFFER_SIZE
    and
    strBuffer = Left(strBuffer, lngRead)

    All downloaded data is treated as string and not binary. For this reason the binary ZIP files are corrupted.

    The problem can most likely be solved by replacing the string with a byte array. This is to ensure that there is no string conversion. However, I have not tested it.

    ReplyDelete
  3. AnonymousAugust 29, 2012 at 8:01 PM

    This code works and is very helpful. Do you have code to upload a file to the web using a similar method?

    ReplyDelete
  4. UlfSeptember 5, 2012 at 10:01 AM

    Uploading files to the webserver depends on the implementation of you web application. To upload files you most likely need to do a HTTP Post using "multipart/form-data" encoding. A full implementation of a form-data post is quite complex in Excel.

    ReplyDelete
  5. AnonymousSeptember 26, 2012 at 8:59 PM

    This code works fine as long as I use it in a situation similar to the example, downloading the www.google.com startpage as a file test.html.

    However, it does not seem to work in the following situation:
    - A hyperlink on a page links to a location like this: http://www.pzrooster.nl/dag/infoweb/export.php?ref=5&id=2Ba&dag=1348441200
    - Clicking the hyperlink brings up a dialog enabling you to open or save a file with a preset name 'mijnRooster.ics' (a calendar file)
    - The file is dynamically generated from PHP

    Running your code does not generate an error and it creates a file as requested, but it is zero byte and contains only one character: the number 1.

    Any suggestions or help will be highly appreciated!

    Thanks,
    Hans

    ReplyDelete
  6. AnonymousNovember 18, 2012 at 7:14 PM

    Further to the above: I found out that the website sets a cookie with a unique code. This code has to be sent with every request. If not, it returns an empty file. I am able now to download the file, sending a valid cookie value along with the request.
    However, I am still in the process of finding out how to fetch the cookie at the time of creation. Again,, any suggestions will be appreciated.

    Thanks,
    Hans

    ReplyDelete
  7. AnonymousDecember 23, 2012 at 9:52 AM

    I also faced the same problem...I think solution to such problem would really help!

    ReplyDelete
  8. AnonymousMarch 13, 2013 at 1:56 PM

    does not work on windows 7 64-bit

    ReplyDelete
  9. AnonymousJune 27, 2013 at 2:49 PM

    Hi,

    This code is working fine but I am trying to download a CSV file and it downloads the file of size 4 KB which is the buffer size. Please suggest whats going wrong.

    Thanks a lot for your time and help

    ReplyDelete
  10. hterzianSeptember 24, 2013 at 11:04 PM

    I got it to work with Windows 7 64-bit and also solve the corruption issues as follows:
    - Replaced "Private Declare Function" with "Private Declare PtrSafe Function" in the API calls.
    - used FileSystemObject to write the file in the DownloadFile subroutine.

    ReplyDelete

Subscribe to: Post Comments (Atom)