Using VBA to login and pull file from website

[acejoca]

Hello everyone, i'm getting a little stuck and hoping someone can help redirect me.


Scenario:
I process commission statements and for one client we get 30 different login's for each one of our producers. I currently have all the logins and passwords in an excel document. I must login to each one, select several links, then find this months CSV file on the website, click and download it. I've tried working with client to consolidate logins but not help there. Obvisouly I'm not fan of this so i'm trying to find a better way.

What i've tried:
After some research I got close and landed on using the internet explorer object (my code is below) in excel to log into the website and actually log me in. The problem is one of the links I need to click on sends me to another website of theirs which I notice is a changing url. Looks to have an encryption on the url address that is telling the new website the username and password(which changes every time I log in). If this link was a static address I wouldn't have a problem but since it changes I'm not sure how to tell excel to click on that specific link.

Does someone know if I can search the website and pull the new url address on that specific link and/or just tell excel to click on the name of the link( in this case "Commission Statements")?

Or if someone can think of a better process in general...that would be awesome too.


Sub NRS()

UsrName = "ExampleUser"
Passwrd = "ExamplePass"

' Open IE and go to the desired web page
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "http://www.hullrhqbroker.com/login.asp"
.Top = 50
.Left = 130
.Height = 900
.Width = 900

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

Set ipf = ie.document.all.Item("text-1-userid")
ipf.Value = UsrName


Set ipf = ie.document.all.Item("text-1-password")
ipf.Value = Passwrd

ie.document.all.Item("frmlogin").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

ie.navigate "https://www.rhqquoting.com/Secure/AgencyServices.asp"
' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop


End With


End Sub

 

[acejoca]

ahh very cool sean, I will have to play around that with and see if i can get it to work for me. Thanks! Also big thanks for Norie and carpy!

 

[acejoca]

Hey guys so close to being done on this but I'm trying to take it one more step and actually download a file. I'm able to finally get the file name but not sure how to execute it. I cycled through "Input" and can find the link "ctl00$ContentPlaceHolder1$gviewCommStmt$ctl02$btnCSV" but I need to to essential click on it. I'm sure if there more steps here but not sure what i'm missing or need to look for. Any help?

<input type="image" name="ctl00$ContentPlaceHolder1$gviewCommStmt$ctl02$btnCSV" id="ctl00_ContentPlaceHolder1_gviewCommStmt_ctl02_btnCSV" src="Images/icon_xls.gif" style="border-width:0px;" />
                                                        </td>