Using VBA to login and pull file from website

acejoca

New Member
Joined
Jun 4, 2006
Messages
21
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Few things to check

Is the link always in the same part of the page?

If you are comfortable looking at the "view source" data (use chrome or Firefox) then this method usually works for me (although it does depend on the site!!)


objElement = IE.document.getElementsByTagName("input")If you use chrome navigate to the page and right click on the link and "inspect element" and it will bring up some crazy stuff if you aren't familiar but basically you are looking for what the "id" is for that partClicking it involves using the objElement.click
</pre>although again websites differ massively (sadly)
 
Upvote 0
thanks, my html knowledge isn't too great so i'm not having much luck looking at the source code. The link is always in the same location, thinking of just doing sendkeys? I've been looking at the source code a lot but not getting too much out of it. I'm not entirely sure how to use the getelementsbytagname part. Is a tag name something I would find within the source code, next to the link or something?

ALso the website is built as an active server page (asp extension all the pages) if that changes anything.
 
Upvote 0
When looking at the code of the forum login page you will see this kind of thing...

[FONT=Helvetica, -webkit-pictograph]input type="text" class="textbox default-value" name="vb_login_username" id="navbar_username" size="10" accesskey="u" tabindex="101" value="User Name" />
input type="password" class="textbox" tabindex="102" name="vb_login_password" id="navbar_password" size="10" />
[/FONT]
I have put in bold the bits that you are looking for.

Not sure about asp - got an example page?
 
Upvote 0
Don't use SendKeys.

The first thing to do is find out what type of HTML element the link actually is.

Try searching the HTML source code for Commission Statements.

If you are using IE then you might have Developer Tools available, can't remember if it's there by default.

Anyway, if you have it you can open Developer Tools by pressing F12.

You'll see the HTML source code in a tree-type structure and various other things you can ignore by now.

What you want is the little white arrow just below the HTML tab at the top.

Press that, go back to the web page, click on the link you are interested in and then go back to Developer Tools.

Hopefully the source code for the link should be highlighted.

If it is look for an id, name or classname - they can be used to access the link from your code.

Post what you find, if you are going to post the HTML then enclose it in
HTML:
tags.
 
Upvote 0
I used the arrow and was able to go straight to the source but couldn't find a "classname" or "ID" still and wasn't able to copy from that particular screen but here is the code from the source code. I need the first link "...Commissions.aspx..." about 10 lines into the code. Did I copy enough code?

HTML:
<tr> 
    <td background="images/bg_side.jpg"> </td>
    <td height="15" colspan="4" valign="top">
      <blockquote> 
        <table width="80%"  border="1" cellpadding="0" cellspacing="0" bordercolor="#EEF1F4" class="labelgrey">
          <tr class="ltbluelabel">
            <td>User Services:</td>
          </tr>
        </table>
                <p><a href="UserProfile.asp?dasrc=am">User Profile</a><br>
          Update your personal information.
        </p>
        <p><a href="so_users.asp">Agency Security Options</a><br>
          Manage users for your agency.
        </p>

        <table width="80%"  border="1" cellpadding="0" cellspacing="0" bordercolor="#EEF1F4" class="labelgrey">
          <tr class="ltbluelabel">
            <td>Agency Reports:</td>
          </tr>
        </table>
        <p>The following links will open in a separate window.</p>
        
        <p><a href="https://www.mygeosource.com/Commissions.aspx?PID=EB86C7EBBC8A9B26B59F44DE3BADBC9DF00FAB9DD5EB8F34B59F44DE3BADBC7B184CBDD69423978E2AEFE06254E61219692EDCB1EC43E38B21FEF8774DE" target="_blank">Commission Statements</a></p>
        
        <p><a href="https://www.mygeosource.com/Management.aspx?PID=EB86C7EBBC8A9B26B59F44DE3BADBC9DF00FAB9DD5EB8F34B59F44DE3BADBC7B184CBDD69423978E2AEFE06254E61219692EDCB1EC43E38B21FEF8774DE" target="_blank">Management Reports</a></p>
        
        <table>
          <tr>
            <td>
              <img src="images/icon_new2.jpg" width="48" height="56" alt="New!">
            </td>
            <td>
              <p>
              <a href="https://www.mygeosource.com/Management.aspx?PID=EB8366C7EBBC8A9B26B59F44DE3BADBC9DF00FAB9DD5EB8F34B59F44DE3BADBC7B184CBDD69423978E2AEFE06254E61219692EDCB1EC43E38B21FEF8774DE&tab=monthly" target="_blank">Producer Overview Report</a><br />
              This comprehensive report provides a snapshot of your production, <br /> 
              profitability, quality, quote activity and growth over time with GeoVera <br /> 
              Specialty Insurance Company.</p>
            </td>
          </tr>
        </table>
        
        <table width="80%"  border="1" cellpadding="0" cellspacing="0" bordercolor="#EEF1F4" class="labelgrey">
          <tr class="ltbluelabel">
            <td>Information Center:</td>
          </tr>
        </table>
        <p><a href="Materials.asp">Agency Materials</a><br>
          Homeowners Guidelines, Sample Policy Forms, FAQ's, Binder Terms & Conditions and more...</p>
        <p> </p>
        <table width="80%" border="0" cellpadding="3" cellspacing="3">
          <tr>
            <td width="29%" valign="top"><p class="body"><a href="https://www.mygeosource.com/" target="_blank"><img src="images/billpay1.jpg" alt="Bill Pay" name="Image31" width="185" height="223" border="0" align="top" id="Image31" ***********="MM_swapImage('Image31','','images/billpay2.jpg',1)" **********="MM_swapImgRestore()"></a></p>
              <a href="https://www.mygeosource.com/" target="_blank"></a></td>
            <td width="71%" valign="top"><p class="body"><br>
                <br>
                    <span class="greybold10">NEW!<br>
              Policy holders can make payments online: </span></p>
              <ul>
                  <li class="body10"><span class="greybold10">Fast </span>- Just a few clicks</li>
                <li class="body10"><span class="greybold10">Easy</span> - No enrollment required</li>
                <li class="body10"><span class="greybold10">Free</span> - No transaction fees</li>
                <li class="body10"><span class="greybold10">Secure</span> - Safe online transactions </li>
              </ul>
              <p>Print the <a href="pdf/billpay_flyer.pdf" target="_blank">flyer</a> in your office, and share with your GeoVera Specialty clients! </p></td>
          </tr>
        </table>		
      </blockquote>      
    </td>
  </tr>
 
Upvote 0
Now we know it's an anchor (A) not an input element.

Try this, though it really is rough code and is bound to have mistakes.
Code:
Set colLinks = ie.Document.GetElementsByTagName("A")

For Each lnk in colLinks

   If Instr(lnk.innerHTML, "Commissions.aspx")>0 Then
            ie.Navigate lnk.href
            Exit For
   End If
Next lnk
 
Upvote 0
Sweet, it worked. All i had to do was remove the ".innerhtml" part and worked perfectly. Thanks guys for all your help!
 
Upvote 0
Glad you got it working, kew it wouldn't work as is.:)
 
Upvote 0
If you wish to make this a little easier on you should you need to do something similar in the future, I strongly recommend downloading iMacros. This will loop through and extract whatever info you need from a website, and has a pretty simple click mode for building your loop.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top