Excel VBA Introduction Part 49 - Downloading Files from Websites

  • Published on Nov 19, 2016
  • PLEASE NOTE: The design of the website used in this video has changed since the video was recorded. This means that the code shown will no longer work for the website used. You can download both the original code and a revised, working version from this link www.wiseowl.co.uk/videos/excelvba/downloading-files.htm
    If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
    You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-gould/introduction-to-excel-vba/paperback/product-23301058.html
    By Andrew Gould
    Download files here www.wiseowl.co.uk/videos/excelvba/downloading-files.htm
    Excel VBA doesn't have a native method for downloading files from websites but you can declare an API function that will enable you to do this. This video takes you through the process of declaring the API function and using it in your code, along with a bunch of other useful techniques such as using folder pickers, creating folders with FileSystemObjects and opening a Windows Explorer window using the Shell function.

Comments • 103

  • Mohit Khurana
    Mohit Khurana Month ago

    Hi, the downloaded file is showing only 4kb, regardless of which file i choose to download from a given sharepoint page?

  • 5.SL0
    5.SL0 Month ago

    Very helpful video! I was able to create my own code to download .txt files from a site, but I also want to download the PDF version of these files. The PDF file opens up in a new window when I click a button on the site. I try to find the download link when inspecting the element on the button but I can't seem to find it and get this to work, or maybe I'm doing something wrong. Do you have an idea of how to accomplish this? any help will be greatly appreciated!

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago +1

      Ah you're very welcome, really happy to hear that the videos were so useful to you, tahnks so much for watching and for the comments!

    • 5.SL0
      5.SL0 Month ago +1

      WiseOwlTutorials its all good! I appreciate the efforts to try and help! You’ve already helped me a ton with your videos.
      The txt file was the main thing I needed, I thought I’d go the extra step and try to get the pdf as well. But it’s not necessary. I can download other pdf files but I guess the way this site was made doesn’t allow me to do it. Thanks again! I hope you continue to make more vba videos!

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago +1

      Wow, that sounds like a lot of work, great stuff! I'm really not sure how to proceed with getting the results of the pdf file. The jsp extension indicates that it's being generated dynamically by a java server and I'm afraid that I don't know enough about that to give you any useful advice on this one. I think your best bet might be to post (or search for) a question on Stackoverflow.com to get help on this one. Sorry I can't help, but best of luck in finding a solution!

    • 5.SL0
      5.SL0 Month ago

      The link to the pdf that pops up on the second window however is:
      The name of the pdf generated is named print_report_pdfsingle
      Until I save it as a different name.

    • 5.SL0
      5.SL0 Month ago +1

      WiseOwlTutorials so the link on the address bar should’ve worked? Maybe it didn’t work because it opened it in a second window and not the first window?
      And I can’t share the link because you would need to log in to it and it has personal vital info. It’s a website that has employer reports with employee social security numbers. I work for a union and I get these reports so the employees can get their benefits, i.e, health and welfare, pension and annuity, etc.
      The txt file is the important part because I mainly use it to upload the socials, hours worked and wages. I’m able to manipulate that txt file via excel and then upload it to our system. I got that part to work and download properly thanks to you!
      The pdf is generated from the site and looks different than the txt file because the txt file has a bunch of commas separating the data instead of spaces. I use text to columns and sort the data accordingly for upload. The pdf generated looks like the real report, with spaces and looks readable to anyone so they can understand it. I even generate emails if needed. I also learned to generate the emails with your videos! Your channel has been very helpful in making my job easier and more efficient!

  • Yasser Khalil
    Yasser Khalil Month ago +1

    Thank you very much my tutor for this incredible tutorial.
    Why in module `m_ProcessVideoPages` you declared the variable `htmlCell` as IHTMLElement3 and not as IHTMLElement3. Is there a difference or this is just the same ?

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      Hi Yasser, you can read a little about this here stackoverflow.com/questions/13016084/in-mshtml-what-do-the-numbers-mean-like-in-ihtmlelement7 and here stackoverflow.com/questions/9619502/what-happens-when-using-ihtmlelement5-6-for-ie8-9-in-ie6-7-does-it-divert-to
      I hope that helps!

  • Alvaro Lozano Alonso
    Alvaro Lozano Alonso Month ago +1

    Hello Andrew! First of all, I discovered WiseOwl recently and I love it. It is being really useful for my code. Congratulations. Now, I have a question for you. I am trying to download a picture from a website (this is the link of the image itself: portal.cpn.vwg/jctdiss-p/elsa/WebUIProjects/Attachments/Download.aspx?TYPE=B&ID=100696534&LFNR=4&appid=60&authToken=) but it is not possible. I guess that the problem is that the link is not a ".jpg" extension. I can easily download the image just by doing right click on it and then "save image as...". Once it is downloaded, I can see that the image has a ".jpg" extension, but the f*!#/ng authToken= thing is annoying me... is there anything we can do here? you will not have access to the link because it is a Volkswagen Group system... but I guess you will know what to do just by having a look at the link. Thank you in advance!

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      Hi Alvaro, happy to hear that you've been enjoying the videos so far! I believe the issue is less to do with the file type of the image you're attempting to download and more to do with the way your request is authenticated. I think this might be a question for Volkswagen IT support I'm afraid!

  • Andrés Roldán
    Andrés Roldán Month ago +1

    Andrew, again, thanks for your outstanding work. With a combination of your other lessons and this one I was able to create a Macro to automatize the download of several PDF files per time for the users on my job. Soon, I hope I can donate to your project.

    • WiseOwlTutorials
      WiseOwlTutorials  Month ago

      You're welcome Andrés! Thank you for taking the time to leave a comment - it's always nice to know when the videos have helped people in their work!

  • Sheik Abdullah
    Sheik Abdullah 2 months ago

    Hi Andrew,
    I have part numbers list in excel I need to search and download the model,drawing and pdf for the respective part numbers from web portal. Can you please help me on this

    • WiseOwlTutorials
      WiseOwlTutorials  2 months ago

      Hi, I can try to help you with specific questions. If you have any please post them on the relevant video on RU-clip or on our website.

    • Sheik Abdullah
      Sheik Abdullah 2 months ago

      WiseOwlTutorials I can’t able to write please help me on this

    • WiseOwlTutorials
      WiseOwlTutorials  2 months ago

      Hi there, I can try to help if you have a specific question. What have you done so far? Is there a specific part of your code that isn't working?

  • ZERO 0
    ZERO 0 3 months ago

    How to download csv file store on (financials.morningstar.com/ajax/ReportProcess4CSV.html?&t=XBOM:500875®ion=ind&culture=en-US&version=SAL&cur=&reportType=bs&period=12&dataType=A&order=asc&columnYear=10&curYearPart=1st5year&rounding=3&view=raw&r=322320&denominatorView=raw&number=3) in google sheets
    Import data formula not working
    But when i enter this link in IDM then such file got download
    Pls help

  • Ishan Basyal
    Ishan Basyal 7 months ago

    Hi Andrew, Thank you for the videos and the great explanations. I downloaded the files for this video and when I unzipped them, my anti-virus software detected a Trojan. Would you know why this could be? Also, has anyone else encountered a similar problem? Thanks

  • Ivan Onanga
    Ivan Onanga 8 months ago

    Hey @WiseOwlTutorials what's wrong??? it's been a while since the last video??? It's sad! We feel abondonned!

  • Mikko Koskinen
    Mikko Koskinen Year ago

    Hi Andrew, @21min 30 sec, I am able to run the code and I get the "File download started" message. However, the file does not appear in the destination folder. I'm not sure how to troubleshoot the issue or figure out why this the file does not appear.

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      If your destination is your expectation, perhaps the file to be downloaded restricts downloading from the Host. If Destination and Source are correct, w/proper extensions, I would say perhaps a Host issue...

  • roshan kori
    roshan kori Year ago

    Great. First time i have learned that even with VBA you can download the file. I tested too. Works great. Thank You:)

  • Bas Bruijnis
    Bas Bruijnis Year ago +1

    Hi Andrew,
    I get this error on function declaration, text also turns red --> *Private declare function URLDownloadTofile Lib "urlmon"*
    Compile error:
    The code in this project must be updated for use on 64-bit systems.
    Please review and update Declare statements and then mark them with the PtrSafe attribute.
    Any ideas how to fix this? Best regards, Bas

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      You have no name for your function. E.g. Private declare function URLDownloadTofile {function_name} Lib "urlmon"

  • Tarun Arora
    Tarun Arora Year ago

    Hi can anyone help. I am just tying alongside Andrew but i am getting error on the following message:
    Set Links = VideoDiv.getElementsByTagName("a")
    Run time error 91 Object Variable or With Block Variable Not Set
    any help will be appreciated.

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      The 'getElementsByTagName' is a collection, so you have to specify an index of all the "a" tags from the Doc. E.g. getElementsByTagName("a")(27).

  • Tyagi Munde
    Tyagi Munde Year ago

    Noisy background

  • Fabricio Carranza

    Hi again Sr. I hope you are well
    In my work there is a sharepoint where all employees upload excel files , can I use the code you wrote in this tutorial to get the list of all the excel files in a specific folder within the sharepoint? The path of the sharepoint begins with .
    I appreciate your help as always . Take care

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      Yes. SharePoint is MS as well as Excel, so the nice digital ecosystem. I was able to actually create a network drive using the parent URL of the SharePoint, at which you can root around as any other folder. Andrew has a great video FileSystemObject (Part 22) - that will shed some light on your project.

  • Cesar Bertalha
    Cesar Bertalha Year ago

    How do I download excel photo link via VBA?
    I've tried some codes, but I can not open the photo.

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      If you have a shape/image object in a Sheet that's linked to a file of some sort, that will either open a browser w/download box (Ctrl+J), or just the download box by itself. Assign a macro to the picture object that passes the string into the LIB that you would otherwise make the hyperlink as you do now.

    AMBRISH RAVAL Year ago

    Hi, im a newbie in VBA and still learning as of now, may i request for your kind assistance. I want to automate web page filling , thru vba code I am abel to login in web site but not abel to click further - on Inspect element it shows as "" it not showing any name of button, pls help me.Thanks.

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      HTML can assign a link attribute to any type of tag. Luckily for this one, the element has an ID, which has to be unique, per Doc. So you can do - HTMLDoc.getElementByid("navBar0_menu1_dmmy1").click.

  • Bilal Shah
    Bilal Shah Year ago

    Hi Sir.. i need your help in creating a program that uploads a file to webpage. The input type file element in browser does not support directly setting up the file path. I have a workaround using vbs script and send keys but does not really look good to me.
    You help is appreciated if u would loke to show how to set the valie of input type file in a browser with file name in the best possible way.
    Bilal shah

  • Ricardo Gonçalves

    Hi!! I followed all the steps until the minute 20:53. But when running it goes directly to the "File download not started", what could be, if I have seen the code several times and it is the same? Thank you

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      After sometime does it eventually finish? Perhaps a slow Host that takes longer to queue, and the VBA runs so quick it might pass that conditional IF statement before you get a good return from the server. Does the Source file change (does clicking it on a browser show that it generates a token of sorts to the end of the file name, thus having an invalid SourceURL every time?) I would try to use the "OnCodeInstallProblem" function to get the status of the download... Hope this helps.

  • Chanakya S
    Chanakya S Year ago

    I want to download a Excel file from website, I can able select the month on the Web page and then after clicking download i am getting Pop - up with options like save, save as and Open, how can I select the options save, save as or open

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      If you're using HTML object library and using the .click method, you may see that Ctrl+J window (in IE). When you use the LIB function, there's no object, neither HTML nor IE, in which that popup could appear. In Andrew's video, you use the HTML object to get the elements needed, to get the download URL, then pass that into the download function with uses the LIB.

    • Myrslokstok
      Myrslokstok Year ago

      Chanakya S
      I think you can not controll that from excel in a good way.

  • hadi reg
    hadi reg Year ago

    Awesome wiseowl!!

  • Mauro Castagnera
    Mauro Castagnera Year ago

    Dear Andrew, I still can not understand all that you teach, either because I do not understand your language very well and because I'm still at a basic level with VBA, but one thing I've known very well for a long time is that you're really a genius! When I look at your videos and understand the extraordinary talent you have, I keep thinking about how important people like you are, thanks to which the community can learn and advance in its knowledge. God bless you and make you get all the success you deserve in your professional life. My deep appreciation and gratitude comes to you.

  • Sudeep Agrawal
    Sudeep Agrawal Year ago +1

    Followed this video until 19:58 and I get an error message popup indicating : Run-time error: '453' Can't find DLL entry point URLDownloadToFileA in urlmon.dll.Can some one please help me.

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      I got the same error... Shot in the dark, but did you double-click on the title for this text on its page? If so, there's an extra space at the end of it. and if you placed this into your code, that one little extra space invalidates the Alias. Did you check your system32 folder to see if you have the dll?

  • hadi reg
    hadi reg Year ago

    WOW the first time I know mid function returns the string to its very right end from the starting position!!! maan, I've been using VB since year 2000 :D

  • SelfSage
    SelfSage Year ago +1

    Hii how to download .csv file to excel in the same sheet than copying in a destination folder! thanks

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      I would try the "QueryTables.Add" method. It has a lot of ways to delimit (i.e. common one, ."TextFileCommaDelimiter = True"). This way, as long as you have access, it limits the coding you have to do, and less exhaustive on your resources.

  • Hristo Nikodinovski

    Can you pass in UserName and Password with this method?

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      If you're saying you have to login before you can download... you can perhaps login via XML or WinHttp, then attempt to run the Lib function. I'm unsure how access issues react to this download function. Maybe if you logon the one time, then however long the session is, and providing you don't have to pass around cookies, then might work...

  • Nasir Shaikh
    Nasir Shaikh Year ago

    Hi Wise Owl,
    Thanks for all the videos. Would you upload video on "How to upload files on website e.g (Yammer) using VBA?"

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      I would love to ask him this! Maybe you can put the DestinationURL as the actual website directory it'll be stored in. I know with Office products, another was to open a, say Word doc, if you have access, you can just plug the whole URL into the open dialog box (blahblah/thispage/wordfile.docx), then it'll be unsaved in your Temp IE files (most likely), and it's read-only until you save it...

  • Mohan Reddy K
    Mohan Reddy K Year ago

    “Automation Error: Object Invoked has disconnected from its clients”
    I am trying to automate the Internet Explorer to scrape the data. But I am getting continuously the below error. So please share your thoughts / inputs to resolve this error.
    “Automation Error: Object Invoked has disconnected from its clients”
    Thanks a lot in advance!

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      I've always gotten this when the difference of using InternetExplorer or InterExplorerMedium ("medium integrity" per MSDN site). I have to use that one al the time while coding at work. But at home, no intranet to deal with. Try -
      1. DIm IE as InterExplorerMedium
      2. must always use 'navigate' method before setting 'visible' property
      3. try to use late binding (dim IE, then Set IE separately, then after creating instance of IE, perhaps do Application.Wait for a few seconds)
      4. frequently, throw in this to do 'nothing' while IE object is loading/busy
      Loop until IE.busy = False

  • Nicolas Rogelio Sierra Roca

    I like to learn how download a file from a taxes web page, I´m from mexico and I´d like learn how to do a code... another words... I want scrap a web page contains a xml files and save its on my computer... is possible?

  • Khurram Ghori
    Khurram Ghori 2 years ago +1

    Hi, I ran the code at 18:29 and my MS Excel crashes, please advice. Thanks

  • UpYours101
    UpYours101 2 years ago

    jw, did you ever do a video on vba api's? or scrapping/emulating a desktop application or screen? just looking for new projects to learn. thank you

    • UpYours101
      UpYours101 2 years ago

      WiseOwlTutorials i understand completely, thanks for the follow-up and the vids

  • Wajid Azeem
    Wajid Azeem 2 years ago

    Hi Andrew
    If I run the routine (at 18:35 in video) I get "Run-time error '453': Can't find DLL entry point URLDownloadedtoFileA in urlmon". and the line that calls the function gets highlighted in yellow. Please advise solution...

    • Sudeep Agrawal
      Sudeep Agrawal Year ago

      I get the same error, even without the typing mistake.

  • Rhys Williams
    Rhys Williams 2 years ago

    Great video as always.
    Keep up the good work

  • Bookmark Monaco
    Bookmark Monaco 2 years ago

    Hello there, I am at the '54:40 min. watching the video. So I should be able to get access to all videos and call LoadWebPage(). Now I get "Access denied " error message. After debuging a new error message comes, "This method cannot be called after the send method has been called.
    Is this because the wiseowl webpage had enought from me or any other bad luck?

    • Sliced Bread Games
      Sliced Bread Games 7 months ago

      @Alton Benson Yeah, you have to Open a request before using the Send method.

    • Adrian Salabanzi
      Adrian Salabanzi Year ago

      Just add in the value of const WolVidURL a letter "s", so You should have "https" rather than "http" at the beginning of the value.

    • Alton Benson
      Alton Benson Year ago

      found solution

    • Alton Benson
      Alton Benson Year ago

      Yes I am also facing the same issue

  • Ben Green
    Ben Green 2 years ago +2

    Hello Andrew, I just get more impressed by the resource that is Wise Owl all the time. Just downloaded the code at 47.00 and seen the exercises section on your website, amazing! With reference to the C++ code at the beginning; there is talk that VBA is on it's way out and .Net is the way to go, (although I've done work for major multinationals who run surprising simple excel programmes for some of their operations). Can you create a video or a page on your website with an overview of each programming language and it's field of application together with your comments on which one would be a good one to learn and what might happen in the future? I've chosen to improve my VBA skills as I think that is where I'll get the most work from what I've seen in business, but it would be good to have some guidance. Thanks

  • Jay Williams
    Jay Williams 2 years ago

    Hi I was wondering is there a way to use a similar method to upload a file to a website?Thanks

  • Ashish Srivastava
    Ashish Srivastava 2 years ago

    Hi Team, I just came up with the situation like when i am trying to click on any download link via vba at IE11 then it is throwing pop up at the bottom of the webpage asking "Do you want to open or save (File Name) from the (Website Name)" with three tab Open, Save and Cancel. I want to click on save as under save button. Please guide me how I can do this via VBA. your early response will be highly appreciated.

  • Lover Of Life
    Lover Of Life 2 years ago

    Thx for the tutorial. Very impressive.

  • michael genotiva
    michael genotiva 2 years ago +1

    Hi, im a newbie in VBA and still learning as of now, may i request for your kind assistance. I want to automate to a PDF file from a website with user name and password. And will save the PDF file to my desktop folder. Is that possible?

    • michael genotiva
      michael genotiva 2 years ago

      Thank you for you effort to answer my question. :-)

  • Sameer khan
    Sameer khan 2 years ago

    Hi Andrew, I've came across a scenario where instead of url there was javascript_postback enabled href. Is there is a way to automate file download from js enabled href.

    • Sai Manohar
      Sai Manohar 2 months ago

      Hello Andrew and Sameer, could you please help me as well with the solution to this situation?

    • Sameer khan
      Sameer khan 2 years ago

      Thank you Andrew, link worked well for me.
      Any chance of new tutorial on XMLHTTP request with regex to extract inner text.

  • Shabab Alam
    Shabab Alam 2 years ago

    Hi, I am trying to download a csv file but once it downloads, the content within the file turns into HTML jibbrish. Any suggestion to get the file downloaded as it is intended to be would be much appreciated.

    • Shabab Alam
      Shabab Alam 2 years ago

      I see the file as it should be. Here I am posting the link: bulk.ginniemae.gov/protectedfiledownload.aspx?dlfile=data_bulk/mfppprpt.csv

  • Hoàng Mạnh Thắng
    Hoàng Mạnh Thắng 2 years ago

    Hi Andrew,
    Thanks for the tutorial. I saw at the end of the video is that we choose download to this folder manually. Is there any codes to automatically choose the "download to this folder" dialog box? or "cancel" thank you so much

    • Hoàng Mạnh Thắng
      Hoàng Mạnh Thắng 2 years ago

      That would be great!!! SendKeys method seems to works while the IE is current window and visible right?
      For downloading a file, I think it should be IE.visible = False to get our automation target to download the file .
      And in my case, I put the sendkey below the downloading link, then run the code but nothing happen, may be I still stuck on something I don't know.

      Anyway thank you.

    • Hoàng Mạnh Thắng
      Hoàng Mạnh Thắng 2 years ago

      Ya! love so much your reply! I read on that topic but somehow I don't understand the deep root of it. It would be great if you could make a tutorial for this if you have spare times. It could save me a lot of times and get more deeper knowledge into how VBA works.

    • Hoàng Mạnh Thắng
      Hoàng Mạnh Thắng 2 years ago

      Hi, because of login method so I use internet explorer to download file and it show up save or open button. I just curious how to automatically click this. thanks

  • ninjabomberboy
    ninjabomberboy 2 years ago

    Hi Andrew,
    Loving your VBA lessons!
    I was wondering if you could tell me how to log into a website using this method and then start scraping?
    I have noticed that with the MSXML2.XMLHTTP60 .open function, there is an option to put in a username and password, is this the key to logging in? (it doesn't seem to work for me)
    Thank you!

  • savaishra
    savaishra 2 years ago

    Hi Andrew,
    Thanks for the tutorial. Does the same method you have posted above, is suitable for download files from a PLM?
    If not, can you provide instructions for download all files from a PLM?
    I have to download 2000 files having respective unique identification number from PLM.
    Thanks in advance

  • Vladimir Lemos
    Vladimir Lemos 2 years ago

    Hi Andrew, thanks a lot for sharing!
    just amazing, congrats! the best explanation that I ever seen!

  • Kaiyi He
    Kaiyi He 2 years ago

    Hi Andrew. My situation is that I need to select several values (such as client name and reporting period) from a couple of drop down lists in a web page and then download. I am wondering if you have idea how I can possibly pass an option value into the drop down list? The HTML code is some thing like below:


    Thank you so much!

    • Kaiyi He
      Kaiyi He 2 years ago

      @WiseOwlTutorials Thank you Andrew.

  • Jeremy Erickson
    Jeremy Erickson 2 years ago

    Would this also work with PDF documents that are linked in the webpage. I am looking to use this same concept with PDF's however, I am receiving the following error when trying to open the pdf:
    "Acrobat could not open 'test1.pdf' because it is either not a supported file type or because the file has been damaged (for example, it was sent as an email attachment and wasn't correctly decoded).
    fyi: I am about 47 mins into this video after downloading said files and attempting to open them.
    Any help would be greatly appreciated!
    Excellent video by the way, thank you!

    • Jeremy Erickson
      Jeremy Erickson 2 years ago

      I have actually figured out the solution to my problem. Please, disregard my last comment! Thank you for this excellent tutorial!

  • Žan Križanovski
    Žan Križanovski 2 years ago

    Is there any way that you can also pass 2 parameters to log in, and then download the file as shown in the video?

  • Don Bogdala
    Don Bogdala 2 years ago

    Great Tutorial Andrew!

  • krn14242
    krn14242 2 years ago

    Thanks Andrew, Excellent video. Cheers!

  • Forza Algiers
    Forza Algiers 2 years ago

    Thank you for the tutorial, the video and the file

  • Kashif Khan
    Kashif Khan 2 years ago

    Hi Andrew,
    Thank you so much for this lovely video tutorial, God bless you.

    MUSTAFA BAKKAL 2 years ago

    can we get the directlu codes

  • Shahin
    Shahin 2 years ago

    Another unique one. Awesomely nice.

  • Tony Diaz
    Tony Diaz 2 years ago

    Awesome super awesome!!!! As always...