[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Automate File Download from Internet Explorer using VBA

Posted on 2013-05-15
12
Medium Priority
?
2,043 Views
Last Modified: 2013-09-22
Hello - I am using existing VBA in Access 2007 to download a file from IE.  This code has been working perfectly for a few years.  Recently, the website changed their source and now the file is not always in the same location.  It is now in a table and I can't figure out how to get the tag for the proper file.

The file is no longer always associated with the same tag; some days it is (1) and some days it is (0) and some days it is (2) and so on.  I always need the csv file.

Below is the section of the code that had been working but needs changed.  I am not able to provide the login to the site as the information is confidential.  However, I have included attachments with what the webpage looks like and the source.  Hopefully, this provides enough information.  I have highlighted the file I need.

I am a beginner in VBA and didn't write this original code, so I need a lot of help writing the code.

Thanks!

    Set hdoc = IE.Document.frames(1).Document '.frames(1).Document '.Table(0).Document '.body.Document.frames(0).Document.Table(0).Document
    Set linkCol = hdoc.all.tags("a")
    tm = 0
    str = "RateSheetGrid_View_0"
TrySecondOption:
    For Each link In linkCol
        If InStr(link.ID, str) > 0 Then
            'debug.print link.href
            rng = Right(link.href, InStr(link.href, "javascript:PopUpFileViewer(") + 8)
            'debug.print rng
            rng = Left(rng, InStr(rng, ");") - 1)
            'debug.print rng
            urlXls = "https://www.stmpartners.com/Private/ResourceCenter/FileViewer.asp?FileID=" & rng
            GoTo DnldFile
        End If
    Next

Open in new window

IE-Automation-WebPage.PDF
IE-Automation-Source.pdf
0
Comment
Question by:rsburge
  • 7
  • 2
10 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 39173630
Okay, I'm not sure if this will work.  I'm just winging it.  But if it doesn't, maybe it will give you some ideas.
   
    Dim tableRows As Object
    Dim tRow As Object

    Set hdoc = IE.Document.frames(1).Document 
    Set tableRows = hdoc.all.tags("tr")
    For Each tRow in tableRows
        If tRow.InnerHtml Like "*Corr Rate Sheet - csv*" Then
            Set link = tRow.tags("a")
            'debug.print link.href
            rng = Right(link.href, InStr(link.href, "javascript:PopUpFileViewer(") + 8)
            'debug.print rng
            rng = Left(rng, InStr(rng, ");") - 1)
            'debug.print rng
            urlXls = "https://www.stmpartners.com/Private/ResourceCenter/FileViewer.asp?FileID=" & rng
            GoTo DnldFile
        End If
    Next

Open in new window

0
 

Author Comment

by:rsburge
ID: 39174414
Thank you!  I will see if I can make this work.  I will post back later today with the results of my testing.
0
 

Author Comment

by:rsburge
ID: 39188982
Hi.  I have tried all week to make some variation of this work, but no go.

I can get the exact text I need but still can't figure out how to either click the corresponding link or get the href from the corresponding link.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39189766
Can you post the results of the debug.print commands?
0
 

Author Comment

by:rsburge
ID: 39189797
It actually can't locate any href, so I changed that to innerhtml and it prints the source code.

The site isn't showing anything in the table this late at night, so I will have to get the data for you tomorrow once the page is updated.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39246051
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Comment

by:rsburge
ID: 39246052
I'm still trying to work this question.  I am very busy as I am the only person in the office right now who can work on this.  The problem is I can only get the requested information during certain times of the day and lately, the market has been changing so frequently, I haven't been able to get stable information.  I would really prefer you not close out this question.

Thank you!
0
 

Author Comment

by:rsburge
ID: 39493205
I apologize for the long delay.  I am still trying to make this work and am testing again this week.  We had a major project due and I was not able to work on this for the last weveral weeks.
0
 

Assisted Solution

by:rsburge
rsburge earned 0 total points
ID: 39501896
I was finally able to get this to work by changing this line...
From this -
       Set link = tRow.tags("a")

To this -
       Set link = tRow.all.tags("a")
0
 

Author Closing Comment

by:rsburge
ID: 39512750
I included my own comment as the code won't work properly without it.  

I do not want any points for the answer, just to include that it is necessary to change that line for the code to work.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question