VBA to Download documents using URLs & Save As to folder

[MRedfern]

Hello,

I have a list of URLs in column A which are for separate reports in Excel format. (The URLs are formatted like in the attachment below)

In column B, I have a list of the names I would like to save/name each file as, e.g Bookings.

I would like help to create a macro that does the following:
1. Opens the URL from cell A1
2. Macro waits until the Open/Save option appears before continuing
3. Choose to "Save As" the file as name stated in cell B1, to desktop without opening
4. Choose yes to replace file that already exists with that name
5. Close the IE window opened in step 1
6. Opens the URL from cell A2
7. Macro waits until the Open/Save option appears before continuing
8. Choose to "Save As" the file as name stated in cell B2, to desktop without opening
9. Choose yes to replace file that already exists with that name
10. Close the IE window opened in step 6
11. etc until end of list of URLs in Column A is reached

Hoping someone can help - sounds simple when I type it out but I have tried before and can't seem to figure out the whole script for the macro.
Many Thanks!

[Kenneth Hobs]

Welcome to the forum!

Since we don't know the url, I don't see how we can help. If the url is a file, an API can be used. If you want to attach an excel file with the link, click Go Advanced button in lower right of reply box. Click the paperclip icon in toolbar or Manage Attachments hyperlink below reply box.

[MRedfern]

Hi Kenneth,

Thanks for the reply.

As the URL is a private company file, I wouldn't be allowed to include it and even if I could, you would have to be an employee for it to work!

Why would you need the actual genuine URL when i've described the process in great detail? Just curious.

Thanks

[Kenneth Hobs]

As I said, if the URL is an actual file, an API method can be used. Even some that may not be an actual file url, the url opens as a file depending on the source code. e.g. http://www.vbaexpress.com/forum/atta...2&d=1506990455

Since you say it is a file, it is easy to post a similar url in a sample file. e.g. A url to an attached file on the website such as that above. Of course Workbooks.Open is an easy way to open/save such urls as well. The API routes is best when you don't want to Open it though and especially so if not an Excel compatible file.

If the website is showing some selection list or whatever, we have to know how the source code is setup. Some are secure and javascript hides what the actual file path is. In those cases, the Internet Explorer object is about your only path to solution. You still need to know source code for the names of say a Submit button.

In cases where the API method does not work, I can get the source code if I know the url. If secure, you would be violating security if you even post the link or source code. So, now you see why helping can be hard in those cases.

Of course the sample file would also tell me if the link url is an actual link or a formula link. Solutions vary depending on which link type was used.

If you have not posted an example file when I get time later, I will make up an example using the url that I detailed, I will show you the API solution. I would show that code now but you would still need the parsing of the link code.

[MRedfern]

Hi Kenneth.

Many thanks - I have limited knowledge i'm afraid and have never heard of API before or what it means but if you can help, i'd really appreciate it.

Maybe if explain the process I go through?

Each report I can access is listed in a website location
Each report in the list as an individual URL
When I click a particular report in the list, the page acts like its loading or has been refreshed
When the report is ready, a "Would you like to Open or Save the file" bar appears
I usually just select open and the report opens in Excel

[Kenneth Hobs]

Post #5 makes me think you will have your work cut out for you. If you know about a web site's source code, that might give you a clue for what you will need for the explorer object method. Search for "excel getelementsbyid" to find examples that use source code elements. Chrome makes looking at a site's source code elements easy. This search show some code that may help as well. "excel explorer submit button"

Attached is a simple example using the API method that I explained. In it, notice that I had to know that the file extension was gif. Normally, one would know such.

[mdmackillop]

Give this a try. I've tested on these locations for a publicly available file.

http://www.sample-videos.com/xls/Sam...et-10-rows.xls

http://www.sample-videos.com/xls/Sam...t-100-rows.xls

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 
Dim Ret As LongPtr 
 
Sub DownloadFile()
    pth = "C:\VBAX\"
    For Each link In ActiveSheet.Hyperlinks
    fname = Split(link.Address, "/")(UBound(Split(link.Address, "/")))
        filename = pth & fname
        URLDownloadToFile 0, link.Address, filename, 0, 0
    Next link
    End
End Sub

[MRedfern]

Thanks mdmackillop,

But looking at the macro, isn't it just splitting the link address into a filename? I'm also not sure what the 2 excel files are as they have no hyperlinks in them?

[mdmackillop]

Try this. These files were just downloadable examples

[MRedfern]

I seem to get an error on opening. Any ideas?....
macroshot1.jpg

[YasserKhalil]

Try changing the red line with this line

#If VBA7 And Win64 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 Long
#Else
    Private Declare 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

[mdmackillop]

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
Dim Ret As LongPtr

[MRedfern]

Thanks! Got it to work
That's an interesting file - I just need to try it with my hyperlinks tomorrow. I expect I will have to insert some additional parts which deal with the IE loading aspect etc

Will have play around!
Thanks again :-)

[snb]

You can open any workbook using

Workbooks.open "http://www.example.com/files/example.xlsx"

or any document

Documents.open "http://www.example.com/files/example.docx"

[MRedfern]

HI Kenneth,

I've done right click and inspect element to look at the code and I can't find any .xlsx documents or anything similar.

I did play around with the following code a while ago but couldn't get the "readystate" check to work so had to get the macro to pause until the report loaded and again when it was opening. The only part I couldn't master was saving it down and replacing the previous version.

Sub Open_URL()


Do
Dim ieDoc As Object
Dim Report As Variant
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate Sheets("Reports").Range("A1").Value
Application.Wait (Now + TimeValue("00:00:20"))
Application.SendKeys "{Enter}"
Application.Wait (Now + TimeValue("00:00:20"))
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop

End Sub

[Kenneth Hobs]

It is best to avoid overwrite or not dialogs.
e.g.
1. Create a temporary folder.
2. SaveAs to that folder.
3. Copy temp folder files to master folder with no prompts but overwrite if they exist.

(2) is where you do your SendKeys() thing. If your note your keyboard presses, no mouse clicks allowed, then SendKeys() can take those actions. Sounds like you know that part.

If you need help with (1) or (2), please post back.

Once you get it working, we can probably skip the IE object. For that kind of thing, we could probably use the sheetfollowhyperlink event:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

Tip: To paste code, do so between code tags. Click the # icon on reply toolbar to insert the code tags easily.

[Satish Raju]

Hi team, can someone help me in this scenario to download files to multiple folders.
Like in the below code how do i change pth for every specific file to specific folder.
Ex: i want to move few files to "OLD" folder, and some files to "NEW" folder some of them to "MODERN" folder.. how can i loop the path as well. Thanks


Sub DownloadFile()
pth = "C:\VBAX"
For Each link In ActiveSheet.Hyperlinks
fname = Split(link.Address, "/")(UBound(Split(link.Address, "/")))
filename = pth & fname
URLDownloadToFile 0, link.Address, filename, 0, 0
Next link
End
End Sub