can u do it in bulk instead of changing the url one by one? like i have around 1000 urls i have to download, so changing one by one will take a lot of time, can i write down the 1000 urls & just click 1 download button, and they will download it all for me? also the website need to login first
Excellent video, I really appreciate it. Question, how can I use this in MS ACCESS either to get the URL from a Table or an external Excel spreadsheet?
try like this. Sub testcall() Call download_file("th-cam.com/users/vbaa2zdummyfile1.zip", "C:\folder1") Call download_file("th-cam.com/users/vbaa2zdummyfile2.zip", "C:\folder1") Call download_file("th-cam.com/users/vbaa2zdummyfile3.zip", "C:\folder1") Call download_file("th-cam.com/users/vbaa2zdummyfile4.zip", "C:\folder1") Call download_file("th-cam.com/users/vbaa2zdummyfile5.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
@@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.
Hi, the website I want to download files from requires password login. How do write a macro for that and how do I mask the password login in the macro?
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 th-cam.com/video/Ocd5T_--1kg/w-d-xo.html
I have a very similar VBA scrip. I have hyperlinks in Excel that im trying to batch download as pdfs in a folder on my desktop. It downloads the file but the files are corrupted and can't be opened. Do you know how to fix this?
Hello, I'm receving a compiling error when writing the private declare function. "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." I checked, and the version of Excel that I am using is 64-bit. Is your coding for 32-bit systems? If not, then do you know why I am getting the error? Thanks
Hey, great video. However, I've found that this only works for downloads that do not prompt you with a framed notification bar (the bar that asks "Open" "Save" "Cancel"). I need something that also clicks the save button (minimum) or, even more ninja, clicks the drop-down, selects the Save As, inputs the path & file name and then clicks the save button. I know that all this should be possible, so I'm wondering if you have material that would help accomplish this.
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 i just used your code but i am also getting same 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." Note: I have used the updated code from your blog
Hello, thanks so much for sharing! This is very helpful. One question, how can I loop it? I need to download over 1400 files, I already have the URL and used the VBA you coded it and it worked but I need to repeat it, any help?
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. th-cam.com/play/PLo0aMPtFIFDocT-bLo37Fj_iwenKtUlKr.html&pbjreload=102
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
Hello, Thank you for sharing, it is bight useful, but what if i would like to download multiple files every one of them has a different URL and all of them PDF files as this code returns the file with no type
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 "
@@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
How to import this zip file in excel Power query please help I want to gt data from this zip file without extracting it but am not able to import this zip file in power query
What if ou don't know the file name, for example if the url was something like blah.com/FilerServer.ashx?fileId=1 .. I can't seem to get this to work with the solution
@@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.
Hi Great job. I need one urgentl help on the same topic. My would like download the file from website by passing some I'd and report date. Reports/xls file need to be downloaded from portal by passing paearamter. Could you please share some examples on that part? It would be really helpful for me if guid on this topic. I will look forward to your response. Thanks again for wonderful video.
thanks. Check out the web automation series here th-cam.com/video/2BF_Z8ogTis/w-d-xo.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
Hi @VBA A to Z, I have a problem when I run this exact code. "Acrobat could not open '[name of file]' 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)" I need to download PDFs. Can you please help me here?
This is unsung hero channel. You have some great tutorials here. Keep it up.
Thanks, Holie for your support!
We have more videos coming your way please subscribe and watch out :)
You have no idea how helpful this video is. Thanks for sharing your knowledge.
thanks
O vídeo tem 4 anos mas ainda salva vidas ! Thank you very much 👍
most welcome!
can u do it in bulk instead of changing the url one by one? like i have around 1000 urls i have to download, so changing one by one will take a lot of time, can i write down the 1000 urls & just click 1 download button, and they will download it all for me? also the website need to login first
if you know the folder path you can explore the sftp option
Hi,I need to download pics from company portal using excel ID'S.so mY I use the same code?
yes it should work for any site.
hey , thanks for the video . Does the VBA execution work by double clicking on the excel file?
you can use WOrkbook open event for that
Excellent video, I really appreciate it. Question, how can I use this in MS ACCESS either to get the URL from a Table or an external Excel spreadsheet?
Second Question, how can I download the file if I need to enter credentials Login Name and Password?
I am able to download the file, but I can't save it to the desktop.
I am using sharepoint url where is the sql database.
Please guide me
This is what I am looking for... Thank you so much... If, I have to download multiple file with different link, is it possible?
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("th-cam.com/users/vbaa2zdummyfile1.zip", "C:\folder1")
Call download_file("th-cam.com/users/vbaa2zdummyfile2.zip", "C:\folder1")
Call download_file("th-cam.com/users/vbaa2zdummyfile3.zip", "C:\folder1")
Call download_file("th-cam.com/users/vbaa2zdummyfile4.zip", "C:\folder1")
Call download_file("th-cam.com/users/vbaa2zdummyfile5.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
Can I download Captcha Image from any website (Website open in Webbrowser in Userform) and put in on Userform image?
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.
Hi, the website I want to download files from requires password login. How do write a macro for that and how do I mask the password login in the macro?
up
The file downloaded is corrupted when trying to open.Am getting an error the file extension or format issue with 32 & 64 bit conventions as declared
Why cant i see the downloade file in the destination file?
Make sure the target folder name is correct
Also please show us how to upload file to internet. Keepup good work. Thanks
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 th-cam.com/video/Ocd5T_--1kg/w-d-xo.html
Same like this. Is it possible to download the file from share point using the partial file name
Yes - if you can loop through the files in library
Do you have any code bro
I have a very similar VBA scrip. I have hyperlinks in Excel that im trying to batch download as pdfs in a folder on my desktop. It downloads the file but the files are corrupted and can't be opened. Do you know how to fix this?
The file might be actually corrupted, or maybe version and many other factors.
Try opening it with repair option.
Hello,
I'm receving a compiling error when writing the private declare function.
"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."
I checked, and the version of Excel that I am using is 64-bit.
Is your coding for 32-bit systems? If not, then do you know why I am getting the error?
Thanks
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!
Is it possible to download the files from sharepoint using the partial name
Yes - if you can loop through the files in library
Hey, great video. However, I've found that this only works for downloads that do not prompt you with a framed notification bar (the bar that asks "Open" "Save" "Cancel"). I need something that also clicks the save button (minimum) or, even more ninja, clicks the drop-down, selects the Save As, inputs the path & file name and then clicks the save button. I know that all this should be possible, so I'm wondering if you have material that would help accomplish this.
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 i just used your code but i am also getting same 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."
Note: I have used the updated code from your blog
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
hello, thank you for sharing. What if i need to press a download button and i am not finding any url?
Hi Carolina, url is a required parameter so maybe ErrorHandler or additional code to contruct the url.
Hello, thanks so much for sharing! This is very helpful. One question, how can I loop it? I need to download over 1400 files, I already have the URL and used the VBA you coded it and it worked but I need to repeat it, any help?
Yes sure you can. feel free to email me your file (with sample data) @ vbaa2z.team@gmail.com
Hello, how about multiple lines? Is that possible?
multiple files? yes you can
Really helpful thanks alot!!😊
Glad it helped!
Hey, I m not getting download_status as 0. So how to solve this problem?
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??
Thank you so much. It worked for me!
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.
th-cam.com/play/PLo0aMPtFIFDocT-bLo37Fj_iwenKtUlKr.html&pbjreload=102
Is it possible to download XLSM files from sharepoint using this macro?
yes of course
help! it says i have to update it to 64bit system for it to work, how do i do that
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
Is it possible to download the file without setting up the filename? (Use default name when downloaded)
Perhaps construct the file name using url
@@VBAA2Z Thanks !!!
Suppose a web page has I'd &PWD how to put I'd PWD to open the webpage and then download
try this video th-cam.com/video/4ebheN9midA/w-d-xo.html
Sir my macro file will not run every 1 minutes first run 1 min and second it run every 10 sec
please take a look at this time related video th-cam.com/video/ZW5nlE7mj60/w-d-xo.html&pp=sAQA
File gets corrupted after downloading, any idea why this happens
ensure the file extension is same as source
Hello, Thank you for sharing, it is bight useful, but what if i would like to download multiple files every one of them has a different URL and all of them PDF files as this code returns the file with no type
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
Nice Video. Can you rename the file while downloading?
API does not include that parameter.
Try working with destinationfile_local variable.
or post download use Name Statement.
Hi, how do you unzip the file?
Thank you!
You're welcome!
How to import this zip file in excel Power query please help I want to gt data from this zip file without extracting it but am not able to import this zip file in power query
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
How to make function for downloading zip files for different different dates
You can use same function. It is not file specific.
Change the link, file name with extension accordingly
What if ou don't know the file name, for example if the url was something like blah.com/FilerServer.ashx?fileId=1 .. I can't seem to get this to work with the solution
Awesome 👍
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.
this code donot work in excel 64
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
Very useful!
Hi Great job.
I need one urgentl help on the same topic.
My would like download the file from website by passing some I'd and report date.
Reports/xls file need to be downloaded from portal by passing paearamter.
Could you please share some examples on that part?
It would be really helpful for me if guid on this topic.
I will look forward to your response.
Thanks again for wonderful video.
thanks. Check out the web automation series here th-cam.com/video/2BF_Z8ogTis/w-d-xo.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
thanks for this
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 👍🙂
good
great
thanks!
Gud
Hi @VBA A to Z, I have a problem when I run this exact code. "Acrobat could not open '[name of file]' 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)"
I need to download PDFs. Can you please help me here?