Excel VBA Introduction Part 49 - Downloading Files from Websites

Share
Embed
  • 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 • 109

  • ZERO 0
    ZERO 0 10 days 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

    • WiseOwlTutorials
      WiseOwlTutorials  10 days ago

      Hi sorry, I don't know much about Google sheets. Maybe posting a question on their help forum would be better support.google.com/docs/community/?hl=en&gpf=%23!forum%2Fdocs

  • Ishan Basyal
    Ishan Basyal 4 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

    • WiseOwlTutorials
      WiseOwlTutorials  29 days ago

      Hi Ishan, several other people have reported this - I believe that it's a false positive from your virus scanner detecting code that is downloading content from a website which, of course, is what the video is all about :D
      If you want to be certain then you could type out the code yourself as you follow along with the video. I appreciate that this will take longer but at least you'll be sure that it's not a virus!
      I hope that helps and thanks for watching!

  • Ivan Onanga
    Ivan Onanga 5 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 9 months 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 4 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 9 months 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 9 months 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 4 months ago

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

  • Tarun Arora
    Tarun Arora 9 months 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.
    Thanks
    Tarun

    • Sliced Bread Games
      Sliced Bread Games 4 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 4 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 4 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
    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 4 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.
    Regards
    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 4 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 4 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 9 months 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 4 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?

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi Sudeep, have you tried downloading the sample file from here www.wiseowl.co.uk/videos/excelvba/downloading-files.htm Just to check that it's not an issue with the code you've written.

  • 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

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      :D it's amazing how often I discover little things like this!

  • 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 4 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 4 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 4 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...

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago +1

      Hi Nasir, it's on the list!

  • 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 4 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
      Do
      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?

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi Nicolas, in theory yes, that's certainly possible! You'll need to adapt the code in this video for the specific website that you want to use but the overall process will be similar.

  • Khurram Ghori
    Khurram Ghori Year ago

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

  • UpYours101
    UpYours101 Year 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 Year ago

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

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago +1

      Not yet! So many projects, not enough time!

  • Wajid Azeem
    Wajid Azeem Year 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.

    • WiseOwlTutorials
      WiseOwlTutorials  Year ago

      Hi! I think you might have a typing mistake - It should be URLDownloadToFileA rather than URLDownloadedtoFileA
      I hope that helps!

  • Rhys Williams
    Rhys Williams Year ago

    Great video as always.
    Keep up the good work
    Thanks

  • Bookmark Monaco
    Bookmark Monaco Year 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 4 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
      www.mrexcel.com/forum/excel-questions/944792-vba-runtime-error-access-denied-when-accessing-html-page.html

    • Alton Benson
      Alton Benson Year ago

      Yes I am also facing the same issue

  • Ben Green
    Ben Green Year 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.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Ashish, there are a couple of possibilities here stackoverflow.com/questions/32145437/controlling-ie11-do-you-want-to-open-save-dialogue-window-buttons-in-vba
      I hope that helps!

  • 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. :-)

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Michael, it's certainly possible although exactly how you'll do this will depend on the specific website. All I can suggest is Googling to find similar examples, I'm afraid that I don't have any to share.

  • 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.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Sameer, I have Regular Expressions on my list but I'm not sure when I'll create a video on this topic!

    • 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.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Sameer, the short answer is that I don't know! It seems that you're not the only one struggling with this, here's a possible solution stackoverflow.com/questions/17224915/download-files-from-a-web-page-using-vba-html
      I hope it helps!

  • 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

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      That sounds odd! What happens if you download the file manually? Do you see the same? There should be no difference in the result whether you use code or download the file manually.

  • 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.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      No problem! The SendKeys method is certainly the easiest way to do this. If you have that working already then I wouldn't worry too much about the API technique. I plan to cover the basics of API functions at some point!

    • 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.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Ahh I see what you mean - there are a couple of possibilities here stackoverflow.com/questions/32145437/controlling-ie11-do-you-want-to-open-save-dialogue-window-buttons-in-vba
      I hope that helps!

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

      +WiseOwlTutorials
      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

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      I genuinely have no idea! Sorry, I have no experience with that at all.

  • Vladimir Lemos
    Vladimir Lemos 2 years ago

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

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Thanks Vladimir! Glad to hear that you found it useful. Thanks for watching!

  • 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:

    Client_Name_1
    Client_Name_2
    Client_Name_3

    Thank you so much!

    • Kaiyi He
      Kaiyi He 2 years ago

      +WiseOwlTutorials Thank you Andrew.

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Kaiyi, there seems to be a fairly standard way to do this - these should point you in the right direction:
      stackoverflow.com/questions/15003015/using-excel-vba-to-change-the-value-of-a-dropdown-menu-on-a-website
      stackoverflow.com/questions/35607182/vba-control-a-drop-down-menu
      www.mrexcel.com/forum/excel-questions/527874-visual-basic-applications-changing-selection-html-dropdown-menu-within-form.html
      stackoverflow.com/questions/38658315/vba-code-to-select-from-an-html-drop-down-using-value-rather-than-index
      It seems that you also need to make sure the page detects the selection change by applying the FireEvents method. Hope that helps!

  • 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 +1

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

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      Hi Jeremy,
      I don't know of any specific reason that it wouldn't work with PDF files - I take it that the files open correctly when you download them manually?
      And thank you for watching!

  • Ž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?

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago +1

      In principle, yes, that's possible. It depends on how the website asks for credentials. If there are textboxes into which you can type a username and password you can identify those objects on the page and enter the appropriate values - the basics of this technique is shown in this video ru-clip.net/video/dShR33CdlY8/video.html
      I hope that helps!

  • 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
    MUSTAFA BAKKAL 2 years ago

    can we get the directlu codes

    • WiseOwlTutorials
      WiseOwlTutorials  2 years ago

      The link for downloading the files will be added shortly.

  • Md Shahin Iqbal
    Md Shahin Iqbal 2 years ago

    Another unique one. Awesomely nice.

  • Tony Diaz
    Tony Diaz 2 years ago

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