VBA to download Files from Internet - Super Fast API 

thanks

Thanks, Holie for your support! We have more videos coming your way please subscribe and watch out :)

most welcome!

Glad it helped!

You're welcome!

You're welcome!

@@VBAA2Z will it work if I have a folder on SharePoint website and it is an excel file?

it should yes if you have the source path

@@VBAA2Z worked bro! Thanks again. But how should I redesign the code if I have 2 file paths/2 files to be downloaded. How should I pass the path?

The way to go about would be to convert it to function and call it 2 times with different parameters. If you're looking to learn more practical VBA programming here is free e-course. ru-vid.com/group/PLo0aMPtFIFDocT-bLo37Fj_iwenKtUlKr&pbjreload=102

Thank you! Cheers!

@@VBAA2Z Do you have code to create checksum file by using macros? My requirement is.. I have a file in windows and I have to create checksum file for that file and upload both files in SharePoint by using Excel.

Second Question, how can I download the file if I need to enter credentials Login Name and Password?

Of course, you can find some related videos on our channel but if you have specific site you want me to upload to please let me know. Also please refer to ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Ocd5T_--1kg.html

thanks!

you can use WOrkbook open event for that

Yes, you can. Simple convert it to function and call it multiple times.

@@VBAA2Z Can you provide me an example? So I can edit... Sorry for trouble, I am not expert in coding...

try like this. Sub testcall() Call download_file("ru-vid.comdummyfile1.zip", "C:\folder1") Call download_file("ru-vid.comdummyfile2.zip", "C:\folder1") Call download_file("ru-vid.comdummyfile3.zip", "C:\folder1") Call download_file("ru-vid.comdummyfile4.zip", "C:\folder1") Call download_file("ru-vid.comdummyfile5.zip", "C:\folder1") End Sub Function download_file(url As String, destinationFile_local As String) As Boolean Dim downloadStatus As Variant destinationFile_local = destinationFile_local & "\" & fileName(url) downloadStatus = URLDownloadToFile(0, url, destinationFile_local, 0, 0) If downloadStatus = 0 Then download_file = True Else download_file = False End If End Function

You're welcome

@@VBAA2Z Sir how can we change particular date in url filename using VBA As i download date wise several files on daily basis. Thanks in advance 👍🙂

Yes sure you can. feel free to email me your file (with sample data) @ vbaa2z.team@gmail.com

if you know the folder path you can explore the sftp option

The file might be actually corrupted, or maybe version and many other factors. Try opening it with repair option.

yes it should work for any site.

Hi Carolina, url is a required parameter so maybe ErrorHandler or additional code to contruct the url.

I have updated the code to compile for 64 bit version too. Check below link. vbaa2z.blogspot.com/2019/12/downloading-files-from-internet-vba.html

@@VBAA2Z Thank you so much!

up

if you know the url you can and load them to UserForm.

@@VBAA2Z But Captcha Image url change always

@@VBAA2Z I want to login in services.gst.gov.in/services/login website and the Captcha image url is src= services.gst.gov.in/services/captcha?rnd=0.423352763735521 which i want to put in UserForm Image.

API does not include that parameter. Try working with destinationfile_local variable. or post download use Name Statement.

yes of course

did you get it? same situation

Same problem. Therefore did you get some solution?

any solution???

Found the solution. Works in my case Atleast. Pasting the code below Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _ ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long Sub download_HK_picture() Dim i As Integer Dim LR As Integer LR = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To LR imgsrc = Range("A" & i).Value dlpath = "D:\cdsl" URLDownloadToFile 0, imgsrc, i & ".xml", 0, 0 End Sub

Hi Sunil, please check below link and let me know if you still face issue. vbaa2z.blogspot.com/2020/04/compile-error-code-in-this-project-must.html

Yes - if you can loop through the files in library

Do you have any code bro

multiple files? yes you can

i wrote below code to download multiple pdf files from a list of URL's, and renaming each of files with a name from a corresponding cell, i used For loop but this not working with a result, can i know what's the wrong with my code. "Option Explicit #If VBA7 Then Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _ ByVal pCaller As LongPtr, _ ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserved As LongPtr, _ ByVal lpfnCB As LongPtr) As LongPtr #Else Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _ ByVal pCaller As Long, _ ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserved As Long, _ ByVal lpfnCB As Long) As Long #End If Sub download_file() Dim downloadStatus As Variant Dim destinationFile_local As String Dim url As Variant Dim i As Integer For i = 2 To 103 url = Sheets("sheet2").Cells(i, 11) destinationFile_local = "C:\Users\user\Desktop\New folder (2)\Invoices\.pdf" downloadStatus = URLDownloadToFile(0, url, destinationFile_local, 0, 0) Next i If downloadStatus = 0 Then MsgBox "Downloaded Succcessfully!" Else MsgBox "Download failed" End If End Sub Function fileName(file_fullname) As String fileName = Sheets("sheets2").Cells(i, 1) End Function "

do you have list of links you want to download in sheet? share sample link here.

@@VBAA2Z Thank you for your reply. Yes i have a list of URL's for pdf files i want to download in specific folder but they are from a website - for creating invoices /work purposes- you need a username and password to log in, maybe this what affect the result with no output? further running the code result in one file only and its type not PDF, any ideas for fixing this as the data is confidential ad i can't send a sample.

@@noorandolat8330 Hii Nooran, I have 20 invoice numbers in excel sheet and i want download those invoices into a folder and each file have to be save with the invoice number. (These all invoices wanna download from website) pls help me with a vba code Thanks, Mahesh

@@VBAA2Z Hiii, I have 20 invoice numbers in excel sheet and i want download those invoices into a folder and each file have to be save with the invoice number. (These all invoices wanna download from website) pls help me with a vba code Thanks, Mahesh

Perhaps construct the file name using url

@@VBAA2Z Thanks !!!

Make sure the target folder name is correct

You can use same function. It is not file specific. Change the link, file name with extension accordingly

try this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4ebheN9midA.html

please take a look at this time related video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZW5nlE7mj60.html&pp=sAQA

That'll depend on your file but you should be able to accomplish it without extracting first. below is a sample from Mark White's blog. (ZIPFile) => let Header = BinaryFormat.Record([ MiscHeader = BinaryFormat.Binary(14), BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian), ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian) ]), HeaderChoice = BinaryFormat.Choice( BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), each if _ 67324752 // not the IsValid number? then return a dummy formatter then BinaryFormat.Record([IsValid = false, Filename=null, Content=null]) else BinaryFormat.Choice( BinaryFormat.Binary(26), // Header payload - 14+4+4+2+2 each BinaryFormat.Record([ IsValid = true, Filename = BinaryFormat.Text(Header(_)[FileNameLen]), Extras = BinaryFormat.Text(Header(_)[ExtrasLen]), Content = BinaryFormat.Transform( BinaryFormat.Binary(Header(_)[BinarySize]), (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null ) ]), type binary // enable streaming ) ), ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true), Entries = List.Transform( List.RemoveLastN( ZipFormat(ZIPFile), 1), (e) => [FileName = e[Filename], Content = e[Content] ] ) in Table.FromRecords(Entries)

@@VBAA2Z thnx for reverting plz Share your mail id, I will share my file please share your mail id

@@VBAA2Z this code I already used but my files is showing blank plz help

please check this link : Compile error: The code in this project must be updated for use on 64-bit systems vbaa2z.blogspot.com/2020/04/compile-error-code-in-this-project-must.html

@@VBAA2Z Thank you very much!!

@@Laugny you're most welcome

does the link request login?

@@VBAA2Z yes, but file is not getting downloaded due to not getting download_status as 0. Plz 🙏tell me how to solve this??

ensure the file extension is same as source

thanks. Check out the web automation series here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2BF_Z8ogTis.html Pay special attention to Web Automation using VBA. How to find web objects and control them using VBA

@@VBAA2Z thanks a lot for your quick response, I really appreciate. My requirement is, I need to download the excel report from specific portal , the only challange is here, I have to pass the parameter as date and ID in webpage field then reports get download. I hope you understand my concern. Kindly advise. Thanks in advance

vbaa2z.blogspot.com/2019/12/downloading-files-from-internet-vba.html

add PtrSafe

@@VBAA2Z give me download failed - plz can you make it and give me sheet eng.taah@gmail.com

Yes - if you can loop through the files in library