Solved

Automate File Download from Internet Explorer using VBA

Posted on 2013-05-15
12
1,808 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
12 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 46

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

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