VBA to Download documents using URLs & Save As to folder

[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/documen...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