veryamusing
New Member
- Joined
- Jul 28, 2017
- Messages
- 28
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!
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!
Code:
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