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/attachment.php?attachmentid=20552&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.

[MRedfern]

Thanks Kenneth,

I'm ideally in need of a macro that replaces the clicks and save as steps I go through - I will take a look at your suggestions.

Thankyou!

[MRedfern]

Hi Kenneth,

Maybe I haven't explained properly - the list of URLs I have are in a list in an excel workbook so I can click on each like a hyperlink to run a specific report, I don't have to click the link provided in our company website although I must be signed into our company's security section.

In other words, the macro only needs to click a hyperlink in the list in excel, wait for the 'save' window to appear, click save (sendkeys?), save the file as the name listed next to the hyperlink to specific folder, replace old file, close, ......click next in list and repeat until list ends

Maybe this will make a difference?

[Kenneth Hobs]

Oh, I know "almost exactly" what you need. Since I have no access, I can not help directly. All I can do is to teach you to solve it yourself. This is the 3rd thread this week that I have helped with for a similar issue. In the other two, I was able to help directly. It is funny how things come in 3's sometimes.

The best solution to my mind is the Internet Explorer object method which is problem enough. I guess you can use SendKeys() but I try to avoid it whenever possible. If you want go that route, go for it. Keep in mind SendKeys() success is all about focus and timing, rinse and repeat.

Obviously, just clicking the hyperlink is not going to do it for you even if you go the SendKeys() route. You are going to need a macro to intercept the link's click and to act on the hyperlink's address (url). For that part, this site explains how to run a macro from a link click. https://www.extendoffice.com/documents/excel/4422-excel-run-macro-from-hyperlink.html
We'll deal with that issue later if you can get a macro to do what you want.

If you want to learn the most reliable method, read on. Of course no method is totally reliable as webmaster's change their sites without notice.

First off, you need to learn how to use Chrome or Internet Explorer's Inspector. Right click and Inspect on an inputbox or button, will quickly show you what element you need to deal with. Some elements can be handled by their: ID, Name, or Classname. Form buttons can be "cliicked" by Click or Submit. It just depends.

The second step is to use what you have inspected. There are many threads about how to use the Internet Explorer object. One of the best ways to learn it though is a real world example. e.g. We want to login into mail.yahoo.com automatically. Note how a button is "clicked" after username so code has to wait for the new url to activate before sending the password and another button "click".


'https://chandoo.org/forum/threads/how-to-get-logged-in-in-yahoo-com.36038
Sub Test_LoginYahoo2()
LoginYahoo2 "User", "Password"
End Sub


' Add references in Tools > References for:
' Microsoft HTML Object Library
' Microsoft Forms 2.0 Object Library
' Microsoft Internet Controls
Sub LoginYahoo2(username As String, password As String)
Const strURL_c As String = "http://mail.yahoo.com"
Dim url As String
Dim objIE As SHDocVw.InternetExplorer
Dim ieDoc As MSHTML.HTMLDocument
Dim tbxPwdFld As MSHTML.HTMLInputElement
Dim tbxUsrFld As MSHTML.HTMLInputElement
Dim btnSubmit As MSHTML.HTMLInputElement

Excel.Application.Cursor = xlWait
If InStr(username, "@") = 0 Then username = username & "@yahoo.com"

On Error GoTo Err_Hnd

'Create Internet Explorer Object
Set objIE = New SHDocVw.InternetExplorer
'Navigate the URL
objIE.navigate strURL_c
objIE.Visible = False
'Wait for page to load
Do Until objIE.readyState = READYSTATE_COMPLETE: Loop
Set ieDoc = objIE.document
url = ieDoc.url

ieDoc.getElementsByName("username").Item(0).Value = username
'Do Until ieDoc.getElementsByName("username").Item(0).Value = username: Loop
ieDoc.forms("signin").submit
'ieDoc.getElementById("login-signin").Click

Do Until ieDoc.url <> url: Loop
'Do Until objIE.readyState = READYSTATE_COMPLETE '4: Loop
Set ieDoc = objIE.document

'ieDoc.getElementsByName("password").Item(0).Value = password
'Do Until ieDoc.getElementsByName("password").Item(0).Value = password: Loop
ieDoc.getElementById("login-passwd").Value = password
Do Until ieDoc.getElementById("login-passwd").Value = password: Loop
ieDoc.forms("verifyPassword").submit
'ieDoc.getElementById("login-signin").Click

Err_Hnd: '(Fail gracefully)
objIE.Visible = True
On Error GoTo 0
Excel.Application.Cursor = xlDefault
End Sub

[mdmackillop]

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


http://www.sample-videos.com/xls/Sample-Spreadsheet-10-rows.xls


http://www.sample-videos.com/xls/Sample-Spreadsheet-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?....
20625

[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

[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]

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

[MRedfern]

Hi Kenneth,

I've played around with a few suggestions bit with little success i'm afraid.

I have tried to replicate the file i'm trying to use with some dummy links (see attached). The image shows what I see after the URL has been clicked and the report has loaded in Internet Explorer.

Any help appreciated!

[Kenneth Hobs]

Unfortunately, snips like that don't help me help you.

As I said, you have two solution paths.
1. IE object
2. SendKeys()

If you can view the source code's text when you click the link, can you see the value Reports File.xlsx and the path to it blocked out? If you can, code can get that and use the API or Workbooks.Open methods to get and save the file. My suspicion is that they used a security method to hide such details.

If it was just one file, a SendKeys() might have an off chance of working. For more than one file, SendKeys() will likely not be of much help. Of course a hybrid of the two methods might have a remote chance of working.

I did project many years ago where I had to get all items in a web listbox and then chose an item by code. As I explained, one has to get source code to know if that kind of thing is even possible.

If you can learn how I coded the #9 post's example, you might be able to discover a like solution for yourself. Try to work through doing that example with only the link known, mail.yahoo.com. Of course if you cheat and look at the solution example, I won't tell. In fact I recommend stealing, err, using the shown solution but in steps. Think, how would I enter my username, click Next button, enter my password, and click Ok button to login. As you will see in code, the Next button loads another website. As such, it has to wait until it loads before interacting with it further. Loops often work but I added an extra step where it waits for the new url to <> the first url. Sometimes, one just has to add and Application.Wait.




Feel free to PM me for some private conversation.

[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