Downloading file from a website (I've tried almost everything)

[veryamusing]

Hi there! I've been asked to automate the process of importing data from a website. I've been searching for a reliable method that can be deployed to other users within the organization, but have yet to find something that always works. Ideally, I'd like to host the file on our network, rather than requiring all users to repeatedly check and download the latest file. I suggested an Access DB, and built a prototype which would append new rows, but that approach was discouraged.

The website itself is very simple: login (if logged out), click button ("Databases") >> reveals link, click link >> initiates download. At this point, IE would prompt to Open / Save / Close, which I could never figure out how to click, even passing the shortcut. One attempt used the shell to loop through windows, which was unreliable in my implementation. I feel like I've tried everything and have yet to identify a simple, robust solution. All of the other code (mainly a "query" UserForm) relies on the most recent file being saved as "ABCD_YYMMDD" in the user's downloads folder.

On my machine, the below code works to return the actual href for the file, which changes every so often (sometimes daily, at least a couple times a week), and once obtained is passed to CPearson's download file procedure (Downloading A File). It works: downloads the file to my downloads folder, but doesn't work for a co-worker with whom I test these methods. Also, the procedure on CPearson's website is 32-bit, and I have attempted to update it for 64-bit, assuming that could be an issue.

A BIG thank you to everyone who reads this, and anyone who can help me work this out. If you're ever in Honolulu, I'll buy you a refreshing beverage!

Sub dlFile()
    
    Dim url As String
    Dim directory As String
    
    url = returnWebsite
    directory = "C:\Users\" & Environ("UserName") & "\Downloads\ABCD_" & Format(Now(), "YYMMDD") & ".XLSX"
    
    DownloadFile url, directory, OverwriteRecycle, ""
    
End Sub

Public Function returnWebsite() As String
    Dim ieApp As InternetExplorer
    Set ieApp = New InternetExplorer
    ieApp.Visible = False
    ieApp.navigate "[REMOVED]"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
    
    Dim ieDoc As Object
    Set ieDoc = ieApp.Document
    Dim htmlElem As IHTMLElement
    For Each htmlElem In ieDoc.getElementsByTagName("a")
        With htmlElem
            If .ID = "signin" Then
                If .innerText <> "Sign Out" Then
                    With ieDoc.Forms("loginform")
                        .UserName.Value = "[REMOVED]"
                        .password.Value = "[REMOVED]"
                        .submit
                        Do While ieApp.Busy: DoEvents: Loop
                        Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
                    End With
                End If
            End If
            If .innerText = "Databases" Then
                .Click
                Do While ieApp.Busy: DoEvents: Loop
                Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
                Set ieDoc = ieApp.Document
                Dim htmlElem2
                For Each htmlElem2 In ieDoc.getElementsByTagName("a")
                    With htmlElem2
                        If .innerText = "download" Then
                            returnWebsite = "[Removed]" & .getAttribute("href")
                            Set ieDoc = Nothing
                            ieApp.Quit
                            Set ieApp = Nothing
                            Exit Function
                        End If
                    End With
                Next
            End If
        End With
    Next
    
    ieApp.Quit
    Set ieApp = Nothing


End Function

 

[veryamusing]

My apologies. I should say that the "it doesn't work" part refers to a run-time error: "Automation error...The object invoked has disconnected from its clients." I assumed this meant it wasn't waiting long enough for the website to reach READYSTATE_COMPLETE after submitting the user credentials.

Ultimately, I'm looking for the most robust solution to download the file that this link points to. Thanks again!

 

[John_w]

See Alternative to sendkeys (in VBA) for a website dealing with angularjs for 3 methods to download a file.

A 4th method is possible: if you have a static URL to the file try the Windows API function UrlDownloadToFile.

 

[veryamusing]

An update: my implementation of CPearson's Download File procedure (using UrlDownloadToFile) was working on my machine at work; however, I tried it on my machine at home, and my partner's machine, and the file it downloads is only 1 KB instead of over 6 MB. I can download the file manually and it will be the larger size. When I opened the 1 KB file, there was a note that read, "The file you are requesting is currently not available. An email has been sent to the system administrator. We are sorry for the inconvenience." Any suggestions? Thank you.